Re: Optimizing Oracle Queries.

Vel wrote:

> See my response...Let me know info is not sufficient.
>
> What version of Oracle?
> Oracle9i

Then likely, or hopefully, you are using the Cost Based Optimizer (CBO).
>
> Are statistics current and created with DBMS_STATS?
> No idea. How to do that?

Ask your DBA as this is critical information. The CBO requires accurate statistics preferably generated by running the DBMS_STATS package.

> What is the expain plan?
> No idea. How to do that?

More info. than I can supply in a usenet post so see if you can follow the explain plan examples at:

http://www.psoug.org
click on Morgan's Library
click on Explain Plan

I'll help you if you get stuck.

Also look up explain plan at http://tahiti.oracle.com

Be sure to buid the plan table by running utlxplan.sql as part of the setup.

> 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,
> CHECKSUM_VALUE NUMBER(38) DEFAULT 0 NULL
> );
> ALTER TABLE REMARK_SUMMARY
> ADD CONSTRAINT SYS_C0017801 PRIMARY KEY (IMAGE_RECNBR);
>
> 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);
I would turn the CHARs into VARCHAR2. In my opinion there is no valid reason for CHAR anywhere and in your case definitely not. For example you are storing 72 bytes of remarks even if the remark is just one byte.

The DOCUMENT_NBR is listed as a CHAR(12). Why? Isn't it always an integer? Same goes for other columns though I can't see the data. The SEQ_NBR is numeric (NUMBER(4)) so you should not be using a string in your WHERE clause.

Explain Plan is a very basic technique I teach in the first 15 hours of a "basic" Oracle class. As you are not familiar with it I would strongly urge you to find a local university or college and get some training under your belt.

HTH

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