創建與管理Oracle分區表和本地索引的實例解析
作者：newhappy2008
本文我們主要介紹了創建與管理Oracle分區表和本地索引的相關知識，并用一些實例對這一過程加以說明與解析，希望能夠對您有所幫助。
創建與管理Oracle分區表和本地索引的相關知識是本文我們主要要介紹的內容，我們知道，Oracle的分區技術在某些條件下可以極大的提高查詢的性能，所以被廣泛采用。從產品上說，分區技術是Oracle企業版中獨立收費的一個組件。以下是對于分區及本地索引的一個示例。
首先根據字典表創建一個測試分區表：
- SQL> connect eygle/eygle
- Connected.
- SQL> CREATE TABLE dbobjs
- 2 (OBJECT_ID NUMBER NOT NULL,
- 3 OBJECT_NAME varchar2(128),
- 4 CREATED DATE NOT NULL
- 5 )
- 6 PARTITION BY RANGE (CREATED)
- 7 (PARTITION dbobjs_06 VALUES LESS THAN (TO_DATE('01/01/2007', 'DD/MM/YYYY')),
- 8 PARTITION dbobjs_07 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')));
- Table created.
- SQL> COL segment_name for a20
- SQL> COL PARTITION_NAME for a20
- SQL> SELECT segment_name, partition_name, tablespace_name
- 2 FROM dba_segments
- 3 WHERE segment_name = 'DBOBJS';
- SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
- -------------------- -------------------- ------------------------------
- DBOBJS DBOBJS_06 EYGLE
- DBOBJS DBOBJS_07 EYGLE
創建一個Local索引，注意這里可以將不同分區的索引指定創建到不同的表空間：
- SQL> CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL
- 2 (PARTITION dbobjs_06 TABLESPACE users,
- 3 PARTITION dbobjs_07 TABLESPACE users
- 4 );
- Index created.
這個子句可以進一步調整為類似：
- CREATE INDEX dbobjs_idx ON dbobjs (created) LOCAL
- (PARTITION dbobjs_06 TABLESPACE users,
- PARTITION dbobjs_07 TABLESPACE users
- ) TABLESPACE users;
通過統一的tablespace子句為索引指定表空間。
- SQL> COL segment_name for a20
- SQL> COL PARTITION_NAME for a20
- SQL> SELECT segment_name, partition_name, tablespace_name
- 2 FROM dba_segments
- 3 WHERE segment_name = 'DBOBJS_IDX';
- SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
- -------------------- -------------------- ------------------------------
- DBOBJS_IDX DBOBJS_06 USERS
- DBOBJS_IDX DBOBJS_07 USERS
- SQL> insert into dbobjs
- 2 select object_id,object_name,created
- 3 from dba_objects where created
- 6227 rows created.
- SQL> commit;
- Commit complete.
- SQL> select count(*) from dbobjs partition (DBOBJS_06);
- COUNT(*)
- ----------
- 6154
- SQL> select count(*) from dbobjs partition (dbobjs_07);
- COUNT(*)
- ----------
- 73
我們可以通過查詢來對比一下分區表和非分區表的查詢性能差異：
- SQL> set autotrace on
- SQL> select count(*) from dbobjs where created < to_date('01/01/2008','dd/mm/yyyy');
- COUNT(*)
- ----------
- 6227
- Execution Plan
- ----------------------------------------------------------
- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
- 1 0 SORT (AGGREGATE)
- 2 1 PARTITION RANGE (ALL)
- 3 2 INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=8 Bytes=72)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 25 consistent gets
- 0 physical reads
- 0 redo size
- 380 bytes sent via SQL*Net to client
- 503 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- SQL> select count(*) from dbobjs where created < to_date('01/01/2007','dd/mm/yyyy');
- COUNT(*)
- ----------
- 6154
- Execution Plan
- ----------------------------------------------------------
- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=9)
- 1 0 SORT (AGGREGATE)
- 2 1 INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=4 Bytes=36)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 24 consistent gets
- 0 physical reads
- 0 redo size
- 380 bytes sent via SQL*Net to client
- 503 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 0 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
- SQL> select count(distinct(object_name)) from dbobjs where created < to_date('01/01/2007','dd/mm/yyyy');
- COUNT(DISTINCT(OBJECT_NAME))
- ----------------------------
- 4753
- Execution Plan
- ----------------------------------------------------------
- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1 Card=1 Bytes=75)
- 1 0 SORT (GROUP BY)
- 2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'DBOBJS' (Cost=1 Card=4 Bytes=300)
- 3 2 INDEX (RANGE SCAN) OF 'DBOBJS_IDX' (NON-UNIQUE) (Cost=2 Card=1)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 101 consistent gets
- 0 physical reads
- 0 redo size
- 400 bytes sent via SQL*Net to client
- 503 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
對于非分區表的測試：
- SQL> CREATE TABLE dbobjs2
- 2 (object_id NUMBER NOT NULL,
- 3 object_name VARCHAR2(128),
- 4 created DATE NOT NULL
- 5 );
- Table created.
- SQL> CREATE INDEX dbobjs_idx2 ON dbobjs2 (created);
- Index created.
- SQL> insert into dbobjs2
- 2 select object_id,object_name,created
- 3 from dba_objects where created
- 6227 rows created.
- SQL> commit;
- Commit complete.
- SQL> select count(distinct(object_name)) from dbobjs2 where created < to_date('01/01/2007','dd/mm/yyyy');
- COUNT(DISTINCT(OBJECT_NAME))
- ----------------------------
- 4753
- Execution Plan
- ----------------------------------------------------------
- 0 SELECT STATEMENT ptimizer=CHOOSE
- 1 0 SORT (GROUP BY)
- 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DBOBJS2'
- 3 2 INDEX (RANGE SCAN) OF 'DBOBJS_IDX2' (NON-UNIQUE)
- Statistics
- ----------------------------------------------------------
- 0 recursive calls
- 0 db block gets
- 2670 consistent gets
- 0 physical reads
- 1332 redo size
- 400 bytes sent via SQL*Net to client
- 503 bytes received via SQL*Net from client
- 2 SQL*Net roundtrips to/from client
- 1 sorts (memory)
- 0 sorts (disk)
- 1 rows processed
當增加表分區時，LOCAL索引被自動維護：
- SQL> ALTER TABLE dbobjs
- 2 ADD PARTITION dbobjs_08 VALUES LESS THAN (TO_DATE('01/01/2009', 'DD/MM/YYYY'));
- Table altered.
- SQL> set autotrace off
- SQL> COL segment_name for a20
- SQL> COL PARTITION_NAME for a20
- SQL> SELECT segment_name, partition_name, tablespace_name
- 2 FROM dba_segments
- 3 WHERE segment_name = 'DBOBJS_IDX';
- SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
- -------------------- -------------------- ------------------------------
- DBOBJS_IDX DBOBJS_06 USERS
- DBOBJS_IDX DBOBJS_07 USERS
- DBOBJS_IDX DBOBJS_08 EYGLE
- SQL> SELECT segment_name, partition_name, tablespace_name
- 2 FROM dba_segments
- 3 WHERE segment_name = 'DBOBJS';
- SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
- -------------------- -------------------- ------------------------------
- DBOBJS DBOBJS_06 EYGLE
- DBOBJS DBOBJS_07 EYGLE
- DBOBJS DBOBJS_08 EYGLE
關于創建與管理Oracle分區表和本地索引的相關知識及實例就介紹到這里了，如果您想了解更多關于Oracle數據庫的知識，可以看一下這里的文章：http://database.51cto.com/oracle/，希望本次的介紹能夠對您有所收獲！
【編輯推薦】
責任編輯：趙鵬
來源：
CSDN博客
