分区表+本地索引+。。。2
来源:百度文库 编辑:神马文学网 时间:2024/07/03 12:38:55
SQL>COL segment_name for a20
SQL>COL PARTITION_NAME for a20
SQL>SELECT segment_name, partition_name, tablespace_name
2FROM dba_segments
3WHERE segment_name='DBOBJS_IDX';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
----------------------------------------------------------------------
DBOBJS_IDX DBOBJS_06 USERS
DBOBJS_IDX DBOBJS_07 USERS
SQL>insert into dbobjs
2select object_id, object_name, created
3from dba_objects where created
6227 rows created.
SQL>commit;
Commitcomplete.
SQL>select count(*) from dbobjs partition(DBOBJS_06);
COUNT(*)
----------
6154
SQL>select count(*) from dbobjs partition(dbobjs_07);
COUNT(*)
----------
73*************************************我们可以通过查询来对比一下分区表和非分区表的查询性能差异:
********************************************************
对于非分区表的测试:SQL>CREATE TABLE dbobjs2
2(object_id NUMBER NOT NULL,
3object_name VARCHAR2(128),
4created DATE NOT NULL
5);
Tablecreated.
SQL>CREATE INDEX dbobjs_idx2 ON dbobjs2(created);
Index created.
SQL>insert into dbobjs2
2select object_id, object_name, created
3from dba_objects where created
6227rowscreated.
SQL>commit;
Commitcomplete.
SQL>select count(distinct(object_name))
from dbobjs2
where created
COUNT(DISTINCT(OBJECT_NAME))
----------------------------
4753
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTptimizer=CHOOSE
10SORT(GROUPBY)
21TABLEACCESS(BYINDEXROWID)OF'DBOBJS2'
32INDEX(RANGESCAN)OF'DBOBJS_IDX2'(NON-UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
2670consistentgets
0physicalreads
1332redosize
400bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
1sorts(memory)
0sorts(disk)
1rowsprocessed
******************************当增加表分区时,LOCAL索引被自动维护:
SQL>COL PARTITION_NAME for a20
SQL>SELECT segment_name, partition_name, tablespace_name
2FROM dba_segments
3WHERE segment_name='DBOBJS_IDX';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
----------------------------------------------------------------------
DBOBJS_IDX DBOBJS_06 USERS
DBOBJS_IDX DBOBJS_07 USERS
SQL>insert into dbobjs
2select object_id, object_name, created
3from dba_objects where created
6227 rows created.
SQL>commit;
Commitcomplete.
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;
COUNT(*)
----------
6227
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTptimizer=CHOOSE(Cost=1Card=1Bytes=9)
10SORT(AGGREGATE)
21PARTITIONRANGE(ALL)
32INDEX(RANGESCAN)OF'DBOBJS_IDX'(NON-UNIQUE)(Cost=2Card=8Bytes=72)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
25consistentgets
0physicalreads
0redosize
380bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
SQL>select count(*) from dbobjs where created;
COUNT(*)
----------
6154
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTptimizer=CHOOSE(Cost=1Card=1Bytes=9)
10SORT(AGGREGATE)
21INDEX(RANGESCAN)OF'DBOBJS_IDX'(NON-UNIQUE)(Cost=2Card=4Bytes=36)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
24consistentgets
0physicalreads
0redosize
380bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
0sorts(memory)
0sorts(disk)
1rowsprocessed
SQL>selectc ount(distinct(object_name)) from dbobjs where created;
COUNT(DISTINCT(OBJECT_NAME))
----------------------------
4753
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTptimizer=CHOOSE(Cost=1Card=1Bytes=75)
10SORT(GROUPBY)
21TABLEACCESS(BYLOCALINDEXROWID)OF'DBOBJS'(Cost=1Card=4Bytes=300)
32INDEX(RANGESCAN)OF'DBOBJS_IDX'(NON-UNIQUE)(Cost=2Card=1)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
101consistentgets
0physicalreads
0redosize
400bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
1sorts(memory)
0sorts(disk)
1rowsprocessed
********************************************************
对于非分区表的测试:SQL>CREATE TABLE dbobjs2
2(object_id NUMBER NOT NULL,
3object_name VARCHAR2(128),
4created DATE NOT NULL
5);
Tablecreated.
SQL>CREATE INDEX dbobjs_idx2 ON dbobjs2(created);
Index created.
SQL>insert into dbobjs2
2select object_id, object_name, created
3from dba_objects where created
6227rowscreated.
SQL>commit;
Commitcomplete.
SQL>select count(distinct(object_name))
from dbobjs2
where created
COUNT(DISTINCT(OBJECT_NAME))
----------------------------
4753
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTptimizer=CHOOSE
10SORT(GROUPBY)
21TABLEACCESS(BYINDEXROWID)OF'DBOBJS2'
32INDEX(RANGESCAN)OF'DBOBJS_IDX2'(NON-UNIQUE)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
2670consistentgets
0physicalreads
1332redosize
400bytessentviaSQL*Nettoclient
503bytesreceivedviaSQL*Netfromclient
2SQL*Netroundtripsto/fromclient
1sorts(memory)
0sorts(disk)
1rowsprocessed
******************************当增加表分区时,LOCAL索引被自动维护:
SQL>ALTER TABLE dbobjs 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 FROM dba_segments 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 FROM dba_segments WHERE segment_name='DBOBJS';
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
----------------------------------------------------------------------
DBOBJS DBOBJS_06 EYGLE
DBOBJS DBOBJS_07 EYGLE
DBOBJS DBOBJS_08 EYGLE
*************************************************************************************--The End---