看看我这个问题吧,Oracle 9.2.0.6,默认的索引选择是如何计算的?
在一个表上有多个索引,而在查询的时候,使用了各个索引的主索引字段,那么这个时候Oracle是如何计算到底用哪个索引呢?
下面是我测试的过程,有了解的同志请谈谈!
[php]
SQL> create table toms_test as select * from dba_objects;
表已创建。
SQL> create index INX_TOMS_OBJ_NAME on toms_test (OBJECT_ID, OBJECT_NAME);
索引已创建。
SQL> create index INX_TOMS_OWNER on toms_test (OWNER);
索引已创建。
SQL>
SQL> set autotrace on exp
SQL> select created from toms_test where owner='SYS' and object_id=7559;
CREATED
----------
12-5月 -02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TOMS_TEST'
2 1 INDEX (RANGE SCAN) OF 'INX_TOMS_OWNER' (NON-UNIQUE)
SQL>
SQL> select created from toms_test where object_id=7559 and owner='SYS';
CREATED
----------
12-5月 -02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TOMS_TEST'
2 1 INDEX (RANGE SCAN) OF 'INX_TOMS_OWNER' (NON-UNIQUE)
SQL>
分析表/索引
SQL> select created from toms_test where object_id=7559 and owner='SYS';
CREATED
----------
12-5月 -02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TOMS_TEST' (Cost=3 Card=1 Bytes=18)
2 1 INDEX (RANGE SCAN) OF 'INX_TOMS_OBJ_NAME' (NON-UNIQUE) (Cost=2 Card=1)
SQL> select created from toms_test where owner='SYS' and object_id=7559;
CREATED
----------
12-5月 -02
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TOMS_TEST' (Cost=3 Card=1 Bytes=18)
2 1 INDEX (RANGE SCAN) OF 'INX_TOMS_OBJ_NAME' (NON-UNIQUE) (Cost=2 Card=1)
SQL>
[/php]