大家好
我的Proc源码中会用到显示游标,可是用到显示游标的地方,对表索引字段的检索时间很长,几乎和没有创建索引的时间一样。而同样的sql语句直接在oracle中执行则很快完成。所以感觉用到Proc显示游标的时候,表索引几乎失去了作用?请问是什么原因?仔细测试后又发现了这样的情况,和创建索引时的顺序有关。
例如:
tb_1中有(a,b,c,d,e,f,g,h)八个字段
1、首先在a列上创建索引,通过显示游标根据a列进行查询,正常。
2、然后在b列上创建索引,通过显示游标根据b列进行查询,正常;而根据a列进行查询,则速度变慢,几乎和没创建索引一样
如果继续在c列创建索引,则c列正常,a、b列都变得很慢。但是在oracle中直接执行显示游标中定义的sql语句,无论是根据a、b、c列进行查询,速度都很快,这说明索引创建的没问题
奇怪的是为什么通过Proc的显示游标查询就会有区别?谢谢!
我的Proc源码中会用到显示游标,可是用到显示游标的地方,对表索引字段的检索时间很长,几乎和没有创建索引的时间一样。而同样的sql语句直接在oracle中执行则很快完成。所以感觉用到Proc显示游标的时候,表索引几乎失去了作用?请问是什么原因?仔细测试后又发现了这样的情况,和创建索引时的顺序有关。
例如:
tb_1中有(a,b,c,d,e,f,g,h)八个字段
1、首先在a列上创建索引,通过显示游标根据a列进行查询,正常。
2、然后在b列上创建索引,通过显示游标根据b列进行查询,正常;而根据a列进行查询,则速度变慢,几乎和没创建索引一样
如果继续在c列创建索引,则c列正常,a、b列都变得很慢。但是在oracle中直接执行显示游标中定义的sql语句,无论是根据a、b、c列进行查询,速度都很快,这说明索引创建的没问题
奇怪的是为什么通过Proc的显示游标查询就会有区别?谢谢!
我的where条件的确比较多,而且包含了建立索引的所有列。
如果要建立联合索引,需要每个列都需要有值才行吧。可实际情况是很多时候只有某一个索引列有值。
而且将语句直接放到oracle中执行都是正常的。所以应该不是索引建立的问题,是否会是显示游标中存在某些限制?
EXEC SQL DECLARE InqTransInfo CURSOR for
SELECT DEP_NO,TODAY_RATE,MERCHANT_ID,MERCHANT_NAME,MID_BOC,TERMINAL_ID,TRANS_DATE,TRANS_TIME,CARD_NO,TRANS_TYPE,AUTH_CODE,TRACE_NO,BATCH_NO,ENTRY_MODE,INVOICE_NO,
REFERENCE_NO,TRANS_AMT_LOC,TRANS_AMT_FOR,CURRENCY_CODE_FOR,MERCHANT_DISCOUNT,BOC_AGENT_RATE,TRANS_NET_AMT,TRANS_COMM_AMT,TRANS_BANK_COMM_AMT,TRANS_BANK_DCC_INCOME,
BANK_GRAND_TOTAL_AMT,SETTLE_DATE,OWNER_LEGWORK_ID,DCCFLAG,REPORT_DATE,MERCHANT_BACK_AMT,REFUND_AMT
FROM TB_REPORT_DATA_HIS
WHERE BATCH_NO like :InqTrans.batch_no
AND INVOICE_NO like :InqTrans.invoice_no
AND TRACE_NO like :InqTrans.trace_no
AND instr(','||:InqTrans.trans_type||',', ','||TO_CHAR(trans_type)||',')>0
AND TRANS_DATE >= :InqTrans.trans_date_s AND TRANS_DATE <= :InqTrans.trans_date_e
AND MERCHANT_ID like :InqTrans.mid
AND TERMINAL_ID like :InqTrans.tid
AND CARD_NO like :InqTrans.card_no
AND AUTH_CODE like :InqTrans.auth_code;
其中,将所有的宿主变量的最末尾都strcat了"%",用于模糊查询(由于我还没有完全掌握动态sql的语法,因此只能先用静态sql代替)。最后的四列(merchant_id,terminal_id,card_no,auth_code)定义了索引。在前段只输入card_no,其余的查询条件都为'%',只有card_no有值时,查询速度缓慢。将这段sql贴到oracle中,比如sqlplus或者toad中,如下
SELECT DEP_NO,TODAY_RATE,MERCHANT_ID,MERCHANT_NAME,MID_BOC,TERMINAL_ID,TRANS_DATE,TRANS_TIME,CARD_NO,TRANS_TYPE,AUTH_CODE,TRACE_NO,BATCH_NO,ENTRY_MODE,INVOICE_NO,
REFERENCE_NO,TRANS_AMT_LOC,TRANS_AMT_FOR,CURRENCY_CODE_FOR,MERCHANT_DISCOUNT,BOC_AGENT_RATE,TRANS_NET_AMT,TRANS_COMM_AMT,TRANS_BANK_COMM_AMT,TRANS_BANK_DCC_INCOME,
BANK_GRAND_TOTAL_AMT,SETTLE_DATE,OWNER_LEGWORK_ID,DCCFLAG,REPORT_DATE,MERCHANT_BACK_AMT,REFUND_AMT
FROM TB_REPORT_DATA_HIS
WHERE BATCH_NO like '%'
AND INVOICE_NO like '%'
AND TRACE_NO like '%'
AND instr(','||'OS,AS,S ,I ,R '||',', ','||TO_CHAR(trans_type)||',')>0
AND TRANS_DATE >= '20090501' AND TRANS_DATE <= '20090801'
AND MERCHANT_ID like '%'
AND TERMINAL_ID like '%'
AND CARD_NO like '4864010000292082%'
AND AUTH_CODE like '%'
其中只根据card_no进行查询,速度正常。查询结果只有一条记录
如果用户没有填写则按照默认的初始值(也就是当天的日期)进行查询。
假设比如:
TRANS_DATE和CARD_NO,注意顺序在cursor的query中,将这些列紧跟在where后面,顺序和索引中的顺序相同。Pro c中可以使用hint吗?
若可以,也可以在query中加上:
select /*+index(tablealis.indexname */ columns from tablename where indexcolumn_conditions and column_conditions;
因为用户可能只根据(MERCHANT_ID,TERMINAL_ID,CARD_NO,AUTH_CODE )这四列中的任何一列或者多列进行查询,因此联合索引是起不到作用了
CREATE OR REPLACE PROCEDURE my_test
AS
cnt number;
begin
select count(*) into cnt
FROM TB_REPORT_DATA_HIS A,TB_LEGWORK_ID B
WHERE A.OWNER_LEGWORK_ID = B.id
AND BATCH_NO like '%'
AND INVOICE_NO like '%'
AND TRACE_NO like '%'
AND instr(','||'OS,AS,S ,I ,R '||',', ','||TO_CHAR(trans_type)||',')>0
AND TRANS_DATE >= '20090718' AND TRANS_DATE <= '20090801'
AND MERCHANT_ID like '%'
AND TERMINAL_ID like '%'
AND CARD_NO like '4864010000292082%'
AND AUTH_CODE like '%';
dbms_output.put_line(cnt);
end;
/
execute my_test;后只提示:PL/SQL procedure successfully completed.
可是如何找到执行的时间呢?
还是需要在Proc程序中调用呢?谢谢
set serveroutput on不过这个时间包括了结果在屏幕显示时间,若要得到完全的执行时间可以在procedure里面调用
dbms_output.put_line (to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));