| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Optimizing Oracle Queries.
See my response...Let me know info is not sufficient.
What version of Oracle?
Oracle9i
Are statistics current and created with DBMS_STATS? No idea. How to do that?
What is the expain plan?
No idea. How to do that?
What indexes exist and are they being used? See the table structure and constraints.
CREATE TABLE REMARK_SUMMARY
(
IMAGE_RECNBR NUMBER(10) NOT NULL, REMARK_CAT CHAR(26) NOT NULL, REMARK_WHO CHAR(24) NOT NULL, DOCUMENT_NBR CHAR(12) NOT NULL, OP_NBR CHAR(4) NOT NULL, REMARK CHAR(72) NOT NULL, REMARK_SEC CHAR(2) NOT NULL, RMKTYPE CHAR(2) NOT NULL, TRANSCODE CHAR(2) NOT NULL, TYPE_X CHAR(2) NOT NULL, YMDEFF NUMBER(8) NOT NULL, YMDEND NUMBER(8) NOT NULL, YMDENTERED NUMBER(8) NOT NULL, YMDTRANS NUMBER(8) NOT NULL, SEQ_NBR NUMBER(4) NOT NULL,
CREATE INDEX REMARK_SUMMARY_IX1 ON REMARK_SUMMARY (REMARK_CAT); CREATE INDEX REMARK_SUMMARY_IX2 ON REMARK_SUMMARY (REMARK_WHO); CREATE INDEX REMARK_SUMMARY_IX3 ON REMARK_SUMMARY (DOCUMENT_NBR);
Why is DOCUMENT_NBR being trimmed?
Since we were checking first 9 should be non-space & number. We are
checking only non-space now..
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?
First Query returns count and the second query returns a particular
record based on the parameter. In both case where condition will be
same.
Why a nested SELECT(SELECT(SELECT? Can't this be done more
efficiently?
To achive above thing.
DA Morgan wrote:
> 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)
Received on Fri Mar 04 2005 - 11:55:25 CST
 |
 |