- SQL code
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万左右,查询速度太慢。能优化吗??谢谢了
------解决方案--------------------
CT_CHECK_USER 这个字段有索引么?
看执行计划呗。
------解决方案--------------------
先分析每张表,再看执行计划,数据量500万是指哪张表?还是结果是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());
------解决方案--------------------
你这个SQL写的有些问题啊,为啥T_CARDTIME关联了两次,查询的结果两次?
写成下面的不行吗?
- SQL code
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 ;
------解决方案--------------------
语句套的层太多。完全减少层数。可以不用distinct,把表的别名带到字段中去。