求助，关于子查询使用索引的问题

显示全部楼层 · 2008-9-15 01:28:12

一张表t 有建索引a 字段 djxh
在使用语句查询的时候如：select * from t where djxh=123 时会正常使用到索引
可是如果使用语句 select * from t where djxh in (select djxh from XXX) 或者 select * from t where exists (select djxh from XXX where djxh=t.djxh) 都不会用到索引a
不知道为何故，请教大家，谢谢啊！！！

千问 · 2008-9-15 01:28:12

子查询是不是返回很多行？CBO认为用索引代价更大。

千问 · 2008-9-15 01:28:12

子查询返回很多行数据的

千问 · 2008-9-15 01:28:12

多行超过一定比例不会用索引的，这个比例是不是5%？

千问 · 2008-9-15 01:28:12

那有什么办法能用到索引阿，比例可能大于5%

千问 · 2008-9-15 01:28:12

snyzz 发表于 2012-1-10 08:45
多行超过一定比例不会用索引的，这个比例是不是5%？
不一定，索引访问方式很多，就算1%，有的时候也不一定用索引，比如CF很大

千问 · 2008-9-15 01:28:12

没有好办法吗

千问 · 2008-9-15 01:28:12

LZ，你把如下语句的执行计划贴出来！
select * from t a where exists (select 1 from XXX b where b.djxh=a.djxh)

千问 · 2008-9-15 01:28:12

上面的语句是个例子实际上是个很复杂的语句
SELECT --BRXH,BAH,RYRQ,CYRQ,
(SELECT COUNT(*) FROM BY_CKD A WHERE DJXH IN
(
SELECT PATIENT_NO FROM ZY_PATIENT_INFORMATION WHERE BAH=TRIM(TO_CHAR(BA_BRJBXX.BAH,'00000000'))
AND ADMISS_DATE>=TRUNC(RYRQ) AND ADMISS_DATE[TR]9)
)
AND YPXH IN (SELECT YPXH FROM GY_YPCDJG WHERE DDDZ IS NOT NULL AND EXISTS(SELECT * FROM GY_YPMCGGZD WHERE YPXH=GY_YPCDJG.YPXH AND BZBM LIKE 'I%'))
)
PATIENT
FROM BA_BRJBXX
WHERE BAH='00528654' AND
CYRQ BETWEEN TO_DATE('2008-01-01','YYYY-MM-DD') AND TO_DATE('2011-12-01','YYYY-MM-DD')-0.00001

千问 · 2008-9-15 01:28:12

SELECT STATEMENT, GOAL = CHOOSE

SORT AGGREGATE

VIEW
SYS

SORT UNIQUE

FILTER

NESTED LOOPS

NESTED LOOPS

TABLE ACCESS FULL
HISBAK
BY_CKD

TABLE ACCESS BY INDEX ROWID
ZJHIS
GY_YPCDJG

INDEX RANGE SCAN
ZJHIS
GY_YPCDJG_YPXH

TABLE ACCESS BY INDEX ROWID
ZJHIS
GY_YPMCGGZD

INDEX UNIQUE SCAN
ZJHIS
PK_GY_YPMCGGZD

NESTED LOOPS

TABLE ACCESS BY INDEX ROWID
ZJHIS2
ZY_PATIENT_INFORMATION

INDEX UNIQUE SCAN
ZJHIS2
PK_ZY_PATIENT_INFORMATION

TABLE ACCESS BY INDEX ROWID
ZJHIS2
ZY_TOTAL_FEE

INDEX RANGE SCAN
ZJHIS2
INDEX_ZY_TOTAL_FEE_1

MAT_VIEW ACCESS BY INDEX ROWID
ZJHIS
BA_BRJBXX

INDEX RANGE SCAN
ZJHIS
IDX_BA_BRJBXX_BAH