Shulou(Shulou.com)06/01 Report--

-- 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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.