select distinct *
from (select /*+ leading(T_CARDINFO) INDEX(T_CARDINFO CI_XM_INDEX) INDEX(T_CARDTIME T_CHECKTIME_INDEX) */
T_CARDINFO.*, rownum rn
from T_CARDINFO, T_CARDTIME
where CI_SFZH = CT_SFZH
and exists (select 'x'
from T_CARDTIME
where CI_SFZH = CT_SFZH
and CT_YZSJ >= to_date('20010501', 'YYYYMMDD')
and CT_YZSJ <= to_date('20110921', 'YYYYMMDD'))
and CI_XM = '李明'
and CT_CHECK_USER like '800300020600%'
and rownum < 21) t
WHERE t.rn >= 1数据量在500万左右,查询速度太慢。能优化吗??谢谢了
看执行计划呗。
分析表sql:analyze table T_CARDINFO compute statistics;
DBMS_STATS.gather_table_stats('system', T_CARDINFO');
执行计划sql:
explain plan for select v from t11 where v in (select v from t22);
select * from table(dbms_xplan.display());
写成下面的不行吗?
SELECT /*+ leading(T_CARDINFO) INDEX(T_CARDINFO CI_XM_INDEX) INDEX(T_CARDTIME T_CHECKTIME_INDEX) */
DISTINCT T_CARDINFO.*
FROM T_CARDINFO
WHERE EXISTS (SELECT 'x'
FROM T_CARDTIME
WHERE CI_SFZH = CT_SFZH
AND CT_YZSJ >= TO_DATE('20010501', 'YYYYMMDD')
AND CT_YZSJ <= TO_DATE('20110921', 'YYYYMMDD')
AND CT_CHECK_USER LIKE '800300020600%')
AND CI_XM = '李明'
AND ROWNUM < 21 ;