请教高手如何把以下sql语句调整成查询速度最快? 一下sql语句返回结果有70万条记录,where条件字段都是索引字段。
SELECT "T_APPEAL"."C_SERNO",
"T_APPEAL"."C_OPTTYPE",
"T_APPEAL"."C_OPTNO",
"T_APPEAL"."C_PROCDATE_A",
"T_APPEAL"."C_CARDNO",
"T_APPEAL"."C_TEL",
"T_APPEAL"."C_STATUS",
"T_AGENT"."C_NAME",
"T_APPEAL"."C_PROCDATE_B",
"T_APPEAL"."C_TITLE",
"T_APPEAL"."C_DEPTNO_A",
"T_APPEAL"."C_DEPTNO_B"
FROM "T_APPEAL",
"T_AGENT"
WHERE ( "T_APPEAL"."C_OPTNO" = "T_AGENT"."C_NUMBER" ) and
(( "T_APPEAL"."C_STATUS" > '1' ) AND
(to_char("T_APPEAL"."C_PROCDATE_A",'yyyy-mm-dd') between '2006-12-25' and '2008-12-25' OR
to_char("T_APPEAL"."C_PROCDATE_B",'yyyy-mm-dd') between '2006-12-25' and '2008-12-25') AND
"T_APPEAL"."C_CARDNO" like '%%' )
ORDER BY "T_APPEAL"."C_SERNO" DESC
SELECT "T_APPEAL"."C_SERNO",
"T_APPEAL"."C_OPTTYPE",
"T_APPEAL"."C_OPTNO",
"T_APPEAL"."C_PROCDATE_A",
"T_APPEAL"."C_CARDNO",
"T_APPEAL"."C_TEL",
"T_APPEAL"."C_STATUS",
"T_AGENT"."C_NAME",
"T_APPEAL"."C_PROCDATE_B",
"T_APPEAL"."C_TITLE",
"T_APPEAL"."C_DEPTNO_A",
"T_APPEAL"."C_DEPTNO_B"
FROM "T_APPEAL",
"T_AGENT"
WHERE ( "T_APPEAL"."C_OPTNO" = "T_AGENT"."C_NUMBER" ) and
(( "T_APPEAL"."C_STATUS" > '1' ) AND
(to_char("T_APPEAL"."C_PROCDATE_A",'yyyy-mm-dd') between '2006-12-25' and '2008-12-25' OR
to_char("T_APPEAL"."C_PROCDATE_B",'yyyy-mm-dd') between '2006-12-25' and '2008-12-25') AND
"T_APPEAL"."C_CARDNO" like '%%' )
ORDER BY "T_APPEAL"."C_SERNO" DESC
类似的都转换成
"T_APPEAL"."C_PROCDATE_A" between to_date('2006-12-25', 'yyyy-mm-dd')
and to_date('2008-12-26', 'yyyy-mm-dd')
to_char后永不到索引了。
这句貌似没有存在的必要吧?
你的"T_APPEAL"."C_PROCDATE_B"是什么类型的,看你转化成字符型不知道什么意思,
如果是date的可以直接比较大小的,如果不是可以这样写
SELECT "T_APPEAL"."C_SERNO",
"T_APPEAL"."C_OPTTYPE",
"T_APPEAL"."C_OPTNO",
"T_APPEAL"."C_PROCDATE_A",
"T_APPEAL"."C_CARDNO",
"T_APPEAL"."C_TEL",
"T_APPEAL"."C_STATUS",
"T_AGENT"."C_NAME",
"T_APPEAL"."C_PROCDATE_B",
"T_APPEAL"."C_TITLE",
"T_APPEAL"."C_DEPTNO_A",
"T_APPEAL"."C_DEPTNO_B"
FROM "T_APPEAL",
"T_AGENT"
WHERE ( "T_APPEAL"."C_OPTNO" = "T_AGENT"."C_NUMBER" ) and
(( "T_APPEAL"."C_STATUS" > '1' ) AND
(to_date("T_APPEAL"."C_PROCDATE_A",'yyyymmdd') > '20061225' and to_date("T_APPEAL"."C_PROCDATE_A",'yyyymmdd') '20081225' OR
to_date("T_APPEAL"."C_PROCDATE_B",'yyyymmdd') > '20061225' and to_date("T_APPEAL"."C_PROCDATE_B",'yyyymmdd')'20081225') AND
"T_APPEAL"."C_CARDNO" like '%%' )
ORDER BY "T_APPEAL"."C_SERNO" DESC
I have no time!
把过滤最多的大的检索条件放到下边,sql是从下到上执行的