Oracle之分区表和索引优化，分区表字段回表
来源：互联网 发布：北京知豆电动汽车4s店 编辑：程序博客网 时间：2024/06/05 04:35
-- 范围分区示例
drop table part_table purge;
--注意，此分区为范围分区
--例子1
create table part_table (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (deal_date)
(
partition p_201701 values less than (TO_DATE('2017-02-01', 'YYYY-MM-DD')),
partition p_201702 values less than (TO_DATE('2017-03-01', 'YYYY-MM-DD')),
partition p_201703 values less than (TO_DATE('2017-04-01', 'YYYY-MM-DD')),
partition p_201704 values less than (TO_DATE('2017-05-01', 'YYYY-MM-DD')),
partition p_201705 values less than (TO_DATE('2017-06-01', 'YYYY-MM-DD')),
partition p_201706 values less than (TO_DATE('2017-07-01', 'YYYY-MM-DD')),
partition p_201707 values less than (TO_DATE('2017-08-01', 'YYYY-MM-DD')),
partition p_201708 values less than (TO_DATE('2017-09-01', 'YYYY-MM-DD')),
partition p_201709 values less than (TO_DATE('2017-10-01', 'YYYY-MM-DD')),
partition p_201710 values less than (TO_DATE('2017-11-01', 'YYYY-MM-DD')),
partition p_201711 values less than (TO_DATE('2017-12-01', 'YYYY-MM-DD')),
partition p_201712 values less than (TO_DATE('2018-01-01', 'YYYY-MM-DD')),
partition p_201801 values less than (TO_DATE('2018-02-01', 'YYYY-MM-DD')),
partition p_201802 values less than (TO_DATE('2018-03-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
alter table part_table modify nbr not null;
--以下是插入2017年一整年日期随机数和表示山东地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into part_table (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--以下是插入2018年一整年日期随机数和表示山东地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into part_table (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
create index idx_part_id on part_table (id) ;
create index idx_part_nbr on part_table (nbr) local;
--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'part_table',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
set autotrace on
set linesize 1000
select max(nbr) max_nbr from part_table partition(p_201705);
执行计划
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FULL SCAN (MIN/MAX)| IDX_PART_NBR | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |
------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
select max(nbr) max_nbr
from part_table
where deal_date >= TO_DATE('2017-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2017-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select count(*) max_nbr from part_table partition(p_201705);
执行计划
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 8 (0)| 00:00:01 | 5 | 5 |
------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
select count(*) max_nbr
from part_table
where deal_date >= TO_DATE('2017-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2017-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select sum(nbr) max_nbr from part_table partition(p_201705);
执行计划
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 8 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
select sum(nbr) max_nbr
from part_table
where deal_date >= TO_DATE('2017-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2017-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select distinct(nbr) from part_table partition(p_201705);
执行计划
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8660 | 69280 | 9 (12)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 8660 | 69280 | 9 (12)| 00:00:01 | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
152890 bytes sent via SQL*Net to client
6741 bytes received via SQL*Net from client
577 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8635 rows processed
select distinct(nbr)
from part_table
where deal_date >= TO_DATE('2017-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2017-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 374 | 171 (1)| 00:00:03 | | |
| 1 | HASH UNIQUE | | 22 | 374 | 171 (1)| 00:00:03 | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
0 physical reads
0 redo size
152886 bytes sent via SQL*Net to client
6741 bytes received via SQL*Net from client
577 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8635 rows processed
select count(*)
from part_table
where deal_date >= TO_DATE('2017-05-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
and deal_date <= TO_DATE('2017-06-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss');
COUNT(*)
----------
8635
执行计划
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 340 (1)| 00:00:05 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |
|* 3 | TABLE ACCESS FULL | part_table | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |
------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1136 consistent gets
select count(*)
from part_table
where deal_date >= TO_DATE('2017-05-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
and deal_date < TO_DATE('2017-06-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss');
COUNT(*)
----------
8635
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
结论：当查询时候，用到分区表字段但是索引没有分区表字段，oracle需要回表，造成性能损耗
drop table part_table purge;
--注意，此分区为范围分区
--例子1
create table part_table (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (deal_date)
(
partition p_201701 values less than (TO_DATE('2017-02-01', 'YYYY-MM-DD')),
partition p_201702 values less than (TO_DATE('2017-03-01', 'YYYY-MM-DD')),
partition p_201703 values less than (TO_DATE('2017-04-01', 'YYYY-MM-DD')),
partition p_201704 values less than (TO_DATE('2017-05-01', 'YYYY-MM-DD')),
partition p_201705 values less than (TO_DATE('2017-06-01', 'YYYY-MM-DD')),
partition p_201706 values less than (TO_DATE('2017-07-01', 'YYYY-MM-DD')),
partition p_201707 values less than (TO_DATE('2017-08-01', 'YYYY-MM-DD')),
partition p_201708 values less than (TO_DATE('2017-09-01', 'YYYY-MM-DD')),
partition p_201709 values less than (TO_DATE('2017-10-01', 'YYYY-MM-DD')),
partition p_201710 values less than (TO_DATE('2017-11-01', 'YYYY-MM-DD')),
partition p_201711 values less than (TO_DATE('2017-12-01', 'YYYY-MM-DD')),
partition p_201712 values less than (TO_DATE('2018-01-01', 'YYYY-MM-DD')),
partition p_201801 values less than (TO_DATE('2018-02-01', 'YYYY-MM-DD')),
partition p_201802 values less than (TO_DATE('2018-03-01', 'YYYY-MM-DD')),
partition p_max values less than (maxvalue)
)
;
alter table part_table modify nbr not null;
--以下是插入2017年一整年日期随机数和表示山东地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into part_table (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--以下是插入2018年一整年日期随机数和表示山东地区号含义（591到599）的随机数记录，共有10万条，如下：
insert into part_table (id,deal_date,area_code,nbr,contents)
select rownum,
to_date( to_char(sysdate,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
create index idx_part_id on part_table (id) ;
create index idx_part_nbr on part_table (nbr) local;
--统计信息系统一般会自动收集，这只是首次建成表后需要操作一下，以方便测试
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'part_table',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
set autotrace on
set linesize 1000
select max(nbr) max_nbr from part_table partition(p_201705);
执行计划
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 2 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE | | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FULL SCAN (MIN/MAX)| IDX_PART_NBR | 1 | 8 | 2 (0)| 00:00:01 | 5 | 5 |
------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
select max(nbr) max_nbr
from part_table
where deal_date >= TO_DATE('2017-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2017-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select count(*) max_nbr from part_table partition(p_201705);
执行计划
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 8 (0)| 00:00:01 | 5 | 5 |
------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
select count(*) max_nbr
from part_table
where deal_date >= TO_DATE('2017-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2017-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select sum(nbr) max_nbr from part_table partition(p_201705);
执行计划
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 8 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
select sum(nbr) max_nbr
from part_table
where deal_date >= TO_DATE('2017-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2017-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 17 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
select distinct(nbr) from part_table partition(p_201705);
执行计划
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8660 | 69280 | 9 (12)| 00:00:01 | | |
| 1 | HASH UNIQUE | | 8660 | 69280 | 9 (12)| 00:00:01 | | |
| 2 | PARTITION RANGE SINGLE| | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
| 3 | INDEX FAST FULL SCAN | IDX_PART_NBR | 8716 | 69728 | 8 (0)| 00:00:01 | 5 | 5 |
--------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
152890 bytes sent via SQL*Net to client
6741 bytes received via SQL*Net from client
577 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8635 rows processed
select distinct(nbr)
from part_table
where deal_date >= TO_DATE('2017-05-01', 'YYYY-MM-DD')
and deal_date < TO_DATE('2017-06-01', 'YYYY-MM-DD');
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 22 | 374 | 171 (1)| 00:00:03 | | |
| 1 | HASH UNIQUE | | 22 | 374 | 171 (1)| 00:00:03 | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
0 physical reads
0 redo size
152886 bytes sent via SQL*Net to client
6741 bytes received via SQL*Net from client
577 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8635 rows processed
select count(*)
from part_table
where deal_date >= TO_DATE('2017-05-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
and deal_date <= TO_DATE('2017-06-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss');
COUNT(*)
----------
8635
执行计划
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 340 (1)| 00:00:05 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE ITERATOR| | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |
|* 3 | TABLE ACCESS FULL | part_table | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |
------------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1136 consistent gets
select count(*)
from part_table
where deal_date >= TO_DATE('2017-05-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
and deal_date < TO_DATE('2017-06-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss');
COUNT(*)
----------
8635
执行计划
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 170 (0)| 00:00:03 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | | | | |
| 2 | PARTITION RANGE SINGLE| | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
| 3 | TABLE ACCESS FULL | part_table | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
结论：当查询时候，用到分区表字段但是索引没有分区表字段，oracle需要回表，造成性能损耗
阅读全文
0 0
- Oracle之分区表和索引优化，分区表字段回表
- oracle分区表和索引
- Oracle 分区表和索引操作
- Oracle之分区表与全局索引和局部索引
- oracle,查询分区表和非分区表，索引对应的表空间sql
- oracle分区表&索引(例子)
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- oracle 分区表，分区索引
- ORACLE分区表、分区索引
- Oracle 分区表索引
- ORACLE分区表、分区索引
- ORACLE分区表、分区索引
- 【转】 Oracle学习:分区表和索引
- 分区表和分区索引
- 分区表和分区索引
- [Oralce] 分区表和索引
- 分区表和分区索引
- 使用更小卷积核的作用
- 2017-10-21离线赛总结
- [noip2015]斗地主 题解
- C++风格_友元
- 10.21离线赛
- Oracle之分区表和索引优化，分区表字段回表
- C++风格_异常
- Python practice
- java 中 问题及其他
- 随机产生两组整数，每组整数中元素互不相同，这两组数按值递增有序。设计程序，将这两组数合并成按值递减有序的一组数，要求合并的新的一组数中，相同的元素只有一个。
- tp3的单字母函数在thinkphp5.0改为助手函数简介
- Oracle学习笔记 字符集概述
- 51nod 1163 最高的奖励
- 基于TensorFlow的机器学习(1) -- 基础介绍
