I have the following SQL in my program. When I do the FETCH, the index is not being used (indexes are SAP_ORDER_NBR and TIMESTAMP).
EXEC SQL DECLARE ORMSGSV1_CURSOR CURSOR FOR
SELECT SAP_ORDER_NBR
,TIMESTAMP
,EVENT_ID
,TRANS_TYPE
,REF_NBR
,MESSAGE_AREA
,STATUS_CD
,PGM_NAME
,SAP_ITEM_NBR
FROM RFUMNUD0.ORMSGSV1
WHERE SAP_ORDER_NBR = :DCLOHSHMTV1.SAP-ORDER-NBR
AND SAP_ORDER_NBR > ' '
AND SAP_ORDER_NBR BETWEEN '0000000000' AND
'9999999999'
AND TIMESTAMP > '0001-01-01-00.00.00.000001'
OPTIMIZE FOR 10 ROWS
FOR READ ONLY
END-EXEC.
Can you give me any help on why the FETCH is doing a tablespace scan and not using the indexes?
QUESTION POSED ON: 19 JUL 2005
QUESTION ANSWERED BY: Rudy Limeback
It appears that you are feeding the query a specific parameter value in :DCLOHSHMTV1.SAP-ORDER-NBR. So why do you also have to check to make sure that this value is greater than a string of blanks and, in addition, between "0000000000" and "9999999999"? Are some of your order numbers actually blank or not numeric? Similarly with the timestamp, are there really rows in your table with a timestamp that is less than or equal to "0001-01-01-00.00.00.000001"? These types of checks should be done in the application, if they are needed at all (they should not be, since you should feed in only valid parameter values).
I'm guessing that if you took these conditions out of the SQL, maybe the index might be used. But I'm not sure, and I really don't want to give you false hope. I would have to come over to your office to see your actual data to understand why the query got written that way in the first place. Even then, I couldn't promise that removing these conditions would solve the performance problem. Performance usually depends more on non-SQL factors than the actual SQL, although this question does make a strong case for being careful that the SQL doesn't actually hinder performance.
|
|
|
