空间管理您的位置: ITPUB个人空间 » star_guan2008的个人空间 » 日志

Choosing Composite Indexes

上一篇 / 下一篇 2008-04-28 11:20:07 / 个人分类：原创

A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list of columns in theCREATEINDEXstatement that created the index. Consider thisCREATEINDEXstatement:

CREATE INDEX comp_indON table1(x, y, z);

x,xy, andxyzcombinations of columns are leading portions of the index
yz,y, andzcombinations of columns arenotleading portions of the index

SQL> drop table test;

表已丢弃。

SQL> create table test as select * from user_tables;

表已创建。

SQL> commit;

提交完成。

SQL> select * from test a where a.table_name like 'T%' and a.TABLESPACE_NAME='TEST' and a.PCT_USED='40';

已选择12行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          7 consistent gets
          4 physical reads
          0 redo size
       3503 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)
         12 rows processed

SQL> create index test_inx on test(table_name,tablespace_name,pct_used);

索引已创建。

SQL> select * from test a where a.table_name like 'T%' and a.TABLESPACE_NAME='TEST' and a.PCT_USED='40';

已选择12行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'TEST_INX' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
       3503 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)
         12 rows processed

SQL> select * from test a where a.table_name like 'T%' and a.TABLESPACE_NAME='TEST';

已选择12行。

SQL> select * from test a where a.TABLESPACE_NAME='TEST' and a.PCT_USED='40';

已选择36行。

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          9 consistent gets
          0 physical reads
          0 redo size
       4420 bytes sent via SQL*Net to client
        525 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
         36 rows processed

SQL> select * from test a where a.table_name like 'T%' and a.PCT_USED='40';

已选择15行。

Statistics
----------------------------------------------------------
          0 recursive calls
          0 db block gets
          4 consistent gets
          0 physical reads
          0 redo size
       3642 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)
         15 rows processed

SQL> select * from test a where a.TABLESPACE_NAME='TEST' and a.table_name like 'T%' and a.PCT_USED='40';

已选择12行。