一张表,主键 YSFYB_ID ,三个索引 DYXSXXBID ,SFXMDMB_ID,DYXSXXB
SQL> desc ysfyb
Name Type
---------- -------------
YSFYB_ID NUMBER
NDMB_ID NUMBER
XQDMB_ID NUMBER
DYXSXXBID NUMBER
SFXMDMB_ID NUMBER
DYXSXXB VARCHAR2(20) sql语句1:
select * from ysfyb where sfxmdmb_id in (1,2,3) and dyxsxxb in ('aa','bb') and dyxsxxbid in (1,2,3,4,5,6....) 使用了索引扫描sql语句2:
select * from ysfyb where sfxmdmb_id in (1,2,3) and dyxsxxb in ('aa','bb') and dyxsxxbid in (select xsid from aa where zydmb_id=1 and xbdmb_id=1)
索引失效,没有用索引查询请问如何修改 sql语句2 在可以通过子查询的情况下使用索引查询
SQL> desc ysfyb
Name Type
---------- -------------
YSFYB_ID NUMBER
NDMB_ID NUMBER
XQDMB_ID NUMBER
DYXSXXBID NUMBER
SFXMDMB_ID NUMBER
DYXSXXB VARCHAR2(20) sql语句1:
select * from ysfyb where sfxmdmb_id in (1,2,3) and dyxsxxb in ('aa','bb') and dyxsxxbid in (1,2,3,4,5,6....) 使用了索引扫描sql语句2:
select * from ysfyb where sfxmdmb_id in (1,2,3) and dyxsxxb in ('aa','bb') and dyxsxxbid in (select xsid from aa where zydmb_id=1 and xbdmb_id=1)
索引失效,没有用索引查询请问如何修改 sql语句2 在可以通过子查询的情况下使用索引查询
from ysfyb
where sfxmdmb_id in (1, 2, 3)
and dyxsxxb in ('aa', 'bb')
and exists (select 1
from aa
where zydmb_id = 1
and xbdmb_id = 1
and ysfyb.dyxsxxbid = xsid)