通过统一的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 |
我们一直都在努力坚持原创.......请不要一声不吭,就悄悄拿走。
我原创,你原创,我们的内容世界才会更加精彩!
【所有原创内容版权均属TechTarget,欢迎大家转发分享。但未经授权,严禁任何媒体(平面媒体、网络媒体、自媒体等)以及微信公众号复制、转载、摘编或以其他方式进行使用。】
微信公众号
TechTarget
官方微博
TechTarget中国
相关推荐
-
SQL Server 2005合并联接算法(三)
合并联接本身的速度很快,但如果需要排序操作,选择合并联接就会非常费时。如果数据量很大且能够从现有B树索引中获得预排序的所需数据,合并联接通常是最快的联接算法。
-
创建与维护Oracle分区表和本地索引(三)
Oracle的分区技术在某些条件下可以极大的提高查询的性能,所以被广泛采用。从产品上说,分区技术是Oracle企业版中独立收费的一个组件。以下是对于……
-
创建与维护Oracle分区表和本地索引(一)
Oracle的分区技术在某些条件下可以极大的提高查询的性能,所以被广泛采用。从产品上说,分区技术是Oracle企业版中独立收费的一个组件。