Re: Optimizing Oracle Queries.

Vel wrote:

> 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.

What version of Oracle?
Are statistics current and created with DBMS_STATS? What is the expain plan?
What indexes exist and are they being used? Why is DOCUMENT_NBR being trimmed?
Why is SEQ_NBR a string and an integer? and potentially a string of spaces? What is the meaing of two queries? The link only executes one I presume? Why a nested SELECT(SELECT(SELECT? Can't this be done more efficiently? What is the point of the pseudocolumns ROWID and ROWNUM in your query?

Overall is strikes me that you have used CHAR rather than VARCHAR2 and are storing numeric values as strings and that you are using a convoluted method of doing something simple but lacking the business logic I can't go beyond that.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)