Optimizing Oracle Queries.

From: Vel <velmurugan.p_at_gmail.com>
Date: 4 Mar 2005 09:24:08 -0800
Message-ID: <1109957048.078658.174980@o13g2000cwo.googlegroups.com>

Hi,

I have to select a particular record / set of records when clicking page link in web based applications. For that I have designed the oracle query in the following manner...It works fine.. but when we working with the tables which is has more than a Million records, the response is very slow....How to optimize the query / Is there any other approach to get a particular record from the DB quickly?

SELECT COUNT(*) FROM REMARK_SUMMARY WHERE ( ((DOCUMENT_NBR like
'_________ %' )

AND LENGTH(TRIM(DOCUMENT_NBR)) = 9 ) AND (( 4.0 = 2 AND REMARK_SUMMARY.SEQ_NBR = ' ' ) OR 4.0 <> 2 ) )

SELECT * FROM (SELECT ROWNUM ROW_NUM, T.* FROM ( SELECT ROWID ROW_ID, IMAGE_RECNBR,
REMARK_CAT, REMARK_WHO, DOCUMENT_NBR, OP_NBR, REMARK, REMARK_SEC, RMKTYPE, TRANSCODE, TYPE_X, YMDEFF,
YMDEND, YMDENTERED, YMDTRANS, SEQ_NBR,
CHECKSUM_VALUE FROM REMARK_SUMMARY WHERE ( ((DOCUMENT_NBR like
'_________ %' ) AND

LENGTH(TRIM(DOCUMENT_NBR)) = 9 ) AND (( 4.0 = 2 AND REMARK_SUMMARY.SEQ_NBR = ' ' ) OR 4.0 <> 2 ) ) ) T) WHERE ROW_NUM BETWEEN ? AND ? All your suggestion would be appreciated !.

Thanks and Regards,
Velmurugan. Received on Fri Mar 04 2005 - 11:24:08 CST