你看下这条语句的执行计划 ,是否使用上索引 SELECT rpad("T_CHARGE"."C_CALLER",11), rpad("T_CHARGE"."C_ASERVICENUM"||"T_CHARGE"."C_PROGRAMNO",11), COUNT(1), SUM("T_CHARGE"."C_TOTALTIME"), SUM(CEIL("T_CHARGE"."C_TOTALTIME"/"T_CHARGE"."C_UNITTIME")*"T_CHARGE"."C_RATIO") FROM "T_CHARGE" WHERE "T_CHARGE"."C_STATUS" = '1' AND "T_CHARGE"."C_UNITTIME"> 0 AND "T_CHARGE"."C_TOTALTIME" >:i_timeset AND TO_CHAR("T_CHARGE"."C_BEGINTIME",'YYYY/MM/DD') >= :AS_BEGIN_DATE AND TO_CHAR("T_CHARGE"."C_ENDTIME",'YYYY/MM/DD') <= :AS_END_DATE AND "T_CHARGE"."C_CALLER" like :AS_CALLER GROUP BY rpad("T_CHARGE"."C_CALLER",11), rpad("T_CHARGE"."C_ASERVICENUM"||"T_CHARGE"."C_PROGRAMNO",11)
以下情况不使用索引 select * from tablename where id+0=1 where user_name||''= 'smith' where to_number(字段)=4 where user_name like '%D' where user_name like 'D%' where user_name like '%D%' where user_name like 'D%D' where to_char(字段)='k' where nvl(字段,'0')='3' where to_date(字段) ...... --以上排除建了函数索引的情况,如果想用索引则建立函数索引.提高执行速度: 1.尽量少在条件中用to_number,to_date等的计算, 2.尽量少用复杂的表达式. 3.尽量少用where id=nvl(:id,id) 4.字段变长时用where user_name ='name',定长时用where user_name like 'name' .....
这个c_caller的索引一定得要, 但是在PB那个语句里面会明显降低查询速度,咋回事?
我知道是LIKE不能用索引啊。唉,我自己解决啦,
倒,like不能用索引,那oracle不用开公司了。 主要是这3段 TO_CHAR("T_CHARGE"."C_BEGINTIME",'YYYY/MM/DD') >= :AS_BEGIN_DATE AND TO_CHAR("T_CHARGE"."C_ENDTIME",'YYYY/MM/DD') <= :AS_END_DATE AND "T_CHARGE"."C_CALLER" like :AS_CALLER如果没建函数索引,to_char已经把查询变成full table scan了。还有你的AS_CALLER变量不要用%开头,否则索引失效。想用'%ABC%'形式的话,只能full table scan了。
,是否使用上索引
SELECT rpad("T_CHARGE"."C_CALLER",11),
rpad("T_CHARGE"."C_ASERVICENUM"||"T_CHARGE"."C_PROGRAMNO",11),
COUNT(1),
SUM("T_CHARGE"."C_TOTALTIME"),
SUM(CEIL("T_CHARGE"."C_TOTALTIME"/"T_CHARGE"."C_UNITTIME")*"T_CHARGE"."C_RATIO")
FROM "T_CHARGE"
WHERE "T_CHARGE"."C_STATUS" = '1' AND "T_CHARGE"."C_UNITTIME"> 0 AND "T_CHARGE"."C_TOTALTIME" >:i_timeset AND
TO_CHAR("T_CHARGE"."C_BEGINTIME",'YYYY/MM/DD') >= :AS_BEGIN_DATE AND
TO_CHAR("T_CHARGE"."C_ENDTIME",'YYYY/MM/DD') <= :AS_END_DATE AND
"T_CHARGE"."C_CALLER" like :AS_CALLER
GROUP BY rpad("T_CHARGE"."C_CALLER",11), rpad("T_CHARGE"."C_ASERVICENUM"||"T_CHARGE"."C_PROGRAMNO",11)
或许PB给你的SQL语句添加了一些东西
你在pb8里面的数据窗口中retrieve一下看看快不快
select * from tablename where id+0=1
where user_name||''= 'smith'
where to_number(字段)=4
where user_name like '%D'
where user_name like 'D%'
where user_name like '%D%'
where user_name like 'D%D'
where to_char(字段)='k'
where nvl(字段,'0')='3'
where to_date(字段)
......
--以上排除建了函数索引的情况,如果想用索引则建立函数索引.提高执行速度:
1.尽量少在条件中用to_number,to_date等的计算,
2.尽量少用复杂的表达式.
3.尽量少用where id=nvl(:id,id)
4.字段变长时用where user_name ='name',定长时用where user_name like 'name'
.....
但是在PB那个语句里面会明显降低查询速度,咋回事?
主要是这3段
TO_CHAR("T_CHARGE"."C_BEGINTIME",'YYYY/MM/DD') >= :AS_BEGIN_DATE AND
TO_CHAR("T_CHARGE"."C_ENDTIME",'YYYY/MM/DD') <= :AS_END_DATE AND
"T_CHARGE"."C_CALLER" like :AS_CALLER如果没建函数索引,to_char已经把查询变成full table scan了。还有你的AS_CALLER变量不要用%开头,否则索引失效。想用'%ABC%'形式的话,只能full table scan了。