溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
-- 范圍分區示例
drop table range_part_tab purge;
--注意，此分區為范圍分區
--例子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 p_201301 values less than (TO_DATE('2013-02-01', 'YYYY-MM-DD')),
partition p_201302 values less than (TO_DATE('2013-03-01', 'YYYY-MM-DD')),
partition p_201303 values less than (TO_DATE('2013-04-01', 'YYYY-MM-DD')),
partition p_201304 values less than (TO_DATE('2013-05-01', 'YYYY-MM-DD')),
partition p_201305 values less than (TO_DATE('2013-06-01', 'YYYY-MM-DD')),
partition p_201306 values less than (TO_DATE('2013-07-01', 'YYYY-MM-DD')),
partition p_201307 values less than (TO_DATE('2013-08-01', 'YYYY-MM-DD')),
partition p_201308 values less than (TO_DATE('2013-09-01', 'YYYY-MM-DD')),
partition p_201309 values less than (TO_DATE('2013-10-01', 'YYYY-MM-DD')),
partition p_201310 values less than (TO_DATE('2013-11-01', 'YYYY-MM-DD')),
partition p_201311 values less than (TO_DATE('2013-12-01', 'YYYY-MM-DD')),
partition p_201312 values less than (TO_DATE('2014-01-01', 'YYYY-MM-DD')),
partition p_201401 values less than (TO_DATE('2014-02-01', 'YYYY-MM-DD')),
partition p_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;
--以下是插入2013年一整年日期隨機數和表示福建地區號含義（591到599）的隨機數記錄，共有10萬條，如下：
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(0,365)),'J'),
ceil(dbms_random.value(591,599)),
ceil(dbms_random.value(18900000001,18999999999)),
rpad('*',400,'*')
from dual
connect by rownum <= 100000;
commit;
--以下是插入2014年一整年日期隨機數和表示福建地區號含義（591到599）的隨機數記錄，共有10萬條，如下：
insert into range_part_tab (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 range_part_tab (id) ;
create index idx_part_nbr on range_part_tab (nbr) local;
--統計信息系統一般會自動收集，這只是首次建成表后需要操作一下，以方便測試
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'RANGE_PART_TAB',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE) ;
set autotrace on
set linesize 1000
select max(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 | 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 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:00:00', 'YYYY-MM-DD hh34:mi:ss')
and deal_date <= TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD hh34: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 | RANGE_PART_TAB | 497 | 4473 | 340 (1)| 00:00:05 | 5 | 6 |
------------------------------------------------------------------------------------------------------------
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1136 consistent gets
select count(*)
from range_part_tab
where deal_date >= TO_DATE('2013-05-01 00:00:00', 'YYYY-MM-DD hh34:mi:ss')
and deal_date < TO_DATE('2013-06-01 00:00:00', 'YYYY-MM-DD hh34: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 | RANGE_PART_TAB | 22 | 198 | 170 (0)| 00:00:03 | 5 | 5 |
----------------------------------------------------------------------------------------------------------
統計信息
----------------------------------------------------------
0 recursive calls
0 db block gets
568 consistent gets
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
