Index optimization series 13-- aggregation optimization of all kinds of partitioned tables
-- range Partition example
Drop table range_part_tab purge
Note that this partition is a range partition
-- example 1
Create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2 (4000))
Partition by range (deal_date)
(
Partition packs 201301 values less than (TO_DATE ('2013-02-01,' YYYY-MM-DD'))
Partition packs 201302 values less than (TO_DATE ('2013-03-01,' YYYY-MM-DD'))
Partition packs 201303 values less than (TO_DATE ('2013-04-01,' YYYY-MM-DD'))
Partition packs 201304 values less than (TO_DATE ('2013-05-01,' YYYY-MM-DD'))
Partition packs 201305 values less than (TO_DATE ('2013-06-01,' YYYY-MM-DD'))
Partition packs 201306 values less than (TO_DATE ('2013-07-01,' YYYY-MM-DD'))
Partition packs 201307 values less than (TO_DATE ('2013-08-01,' YYYY-MM-DD'))
Partition packs 201308 values less than (TO_DATE ('2013-09-01,' YYYY-MM-DD'))
Partition packs 201309 values less than (TO_DATE ('2013-10-01,' YYYY-MM-DD'))
Partition packs 201310 values less than (TO_DATE ('2013-11-01,' YYYY-MM-DD'))
Partition packs 201311 values less than (TO_DATE ('2013-12-01,' YYYY-MM-DD'))
Partition packs 201312 values less than (TO_DATE ('2014-01-01-01,' YYYY-MM-DD'))
Partition packs 201401 values less than (TO_DATE ('2014-02-01,' YYYY-MM-DD'))
Partition packs 201402 values less than (TO_DATE ('2014-03-01,' YYYY-MM-DD'))
Partition p_max values less than (maxvalue)
)
Alter table RANGE_PART_TAB modify nbr not null
-- the following is a record of inserting random numbers for the whole year of 2013 and representing the meaning of Fujian area code (591 to 599). A total of 100000 entries are as follows:
Insert into range_part_tab (id,deal_date,area_code,nbr,contents)
Select rownum
To_date (to_char (sysdate-365,'J') + TRUNC (DBMS_RANDOM.VALUE (0365)),'J')
Ceil (dbms_random.value (591599))
Ceil (dbms_random.value (18900000001))
Rpad ('*', 400 minutes later)
From dual
Connect by rownum 'RANGE_PART_TAB',estimate_percent = > 10 methodological opt = >' for all indexed columns',cascade= > TRUE)
Set autotrace on
Set linesize 1000
Select max (nbr) max_nbr from range_part_tab partition (pendant 201305)
Carry out the plan
-
| | 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 |
-
Statistical information
0 recursive calls
0 db block gets
2 consistent gets
Select max (nbr) max_nbr
From range_part_tab
Where deal_date > = TO_DATE ('2013-05-01,' YYYY-MM-DD')
And deal_date
< TO_DATE('2013-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 | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 | ---------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets select count(*) max_nbr from range_part_tab partition(p_201305); 执行计划 ------------------------------------------------------------------------------------------------ | 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 range_part_tab where deal_date >= TO_DATE ('2013-05-01,' YYYY-MM-DD')
And deal_date
< TO_DATE('2013-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 | RANGE_PART_TAB | 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 range_part_tab partition(p_201305); 执行计划 -------------------------------------------------------------------------------------------------------- | 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 range_part_tab where deal_date >= TO_DATE ('2013-05-01,' YYYY-MM-DD')
And deal_date
< TO_DATE('2013-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 | RANGE_PART_TAB | 22 | 374 | 170 (0)| 00:00:03 | 5 | 5 | ---------------------------------------------------------------------------------------------------------- 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 568 consistent gets select distinct(nbr) from range_part_tab partition(p_201305); 执行计划 -------------------------------------------------------------------------------------------------------- | 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 range_part_tab where deal_date >= TO_DATE ('2013-05-01,' YYYY-MM-DD')
And deal_date
< TO_DATE('2013-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 | RANGE_PART_TAB | 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 range_part_tab where deal_date >= TO_DATE ('2013-05-01 00 TO_DATE,' YYYY-MM-DD hh34:mi:ss')
And deal_date = TO_DATE ('2013-05-01 00 virtual,' YYYY-MM-DD hh34:mi:ss')
And deal_date < TO_DATE ('2013-06-01 00 YYYY-MM-DD hh34:mi:ss')
COUNT (*)
-
8635
Carry out the plan
-
| | 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 | RANGE_PART_TAB | 22 | 198,170 (0) | 00:00:03 | 5 | 5 |
-
Statistical information
0 recursive calls
0 db block gets
568 consistent gets
