那位DX能告诉小弟为什么这个查询不使用索引

lijietz

如果连接的表数目较少，而且统计信息准确，
oracle cbo选择的执行计划应该是最优的。
   不过对rbo,调整还是非常有必要的。

rwq_

估计全表扫描的表是数据量很小，ORACLE的优化计划就直接不用索引了。-----这是可能一个原因。

Yong Huang

最初由 lession 发布
[B]
select
  b.deptid, b.deptname
  from dept b,person1 a
where a.personid = b.personid
   and (a.firstname like 'test%'
   or a.lastname like 'test%'
   or a.chinesename like 'test%');

Execution Plan
------------------------------------------------------------   
SELECT STATEMENT, GOAL = RULE                                       
NESTED LOOPS                                       
  TABLE ACCESS FULL        Object owner=TEST Object name=DEPT                       
  TABLE ACCESS BY INDEX ROWID        Object owner=TEST        Object name=PERSON1                       
   INDEX UNIQUE SCAN        Object owner=TEST        Object name=PK_PERSON1 [/B]

I don't understand. Do you want an index scan for DEPT even though you already have an index scan on PK_PERSON1 for PERSON1? That doesn't make sense. A nested loop is supposed to full scan the driving table (preferably the smaller one) and for each row scanned, you find the corresponding row(s) in the other (driven) table, preferably by way of an index on the driven table. If you force Oracle to use PK_DEPT, Oracle may use it but it just adds unnecessary overhead.

Your question is not clear because you didn't say which index you think should be used and is not. If you mean the indexes on non-primary-key columns of PERSON1 should be used and are not, then the reason could be that Oracle thinks restricting by PERSON1's PK is more selective. If you think that's not the case (i.e. there're many people in each department but only 1 or 2 named 'test%'), I suggest you use CBO and gather table stats.

Yong Huang

CHENTAO

oracle 選用的執行計劃，你
的數據可能很少，與其用index還不如不用