Re: How to tune this sucker ??

Re-order the from clause. Learn to read the plan.

try: from bkcompany comp, bkformat, bkproducttitle pt, bkbookrelease br

Oracle drives the query based on the last table referenced. (MSSQL & DB2 drive the query based on the first table referenced I believe)

The where clause has some effect, but usually the optimizer can co-ordinate the where clause with the from clause.

With exact values for 2 keys, and indexes on the outer joined fields, this might take 15 seconds if that, even on a PII 233...

These tables aren't big enough to warrent a day's running. Your dba should have shot you.

ttrivedi wrote:

> This one has been running for a full day now.
> select br.prelrefnum, pt.title,br.isbn, bkformat.formateditionname,
> br.price, comp.companyname,
> br.gradefrom, br.gradeto, br.isadult, br.pages,
> to_char(br.publisherdate,'YYYY-MM-DD'),
> to_char(br.publisherdate,'YYYY'),
> pt.titlearticle,pt.subtitle
> from bkbookrelease br, bkproducttitle pt, bkformat, bkcompany comp
> where br.muzeid = pt.muzeid
> and bkformat.releaseformat = br.releaseformat
> and comp.companyid (+) = br.publishercompanyid
> and comp.compakanum (+) = br.publisherakanum
> and pt.titleakanum = 1
> and br.muzeid = 2417845;
>
> SYSTEM:STAGING/SQL>column object_type format a20
> SYSTEM:STAGING/SQL>l
> 1 SELECT OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE
> 2 FROM PLAN_TABLE
> 3* WHERE STATEMENT_ID = 'badboy'
> SYSTEM:STAGING/SQL>/
>
> OPERATION OPTIONS
> OBJECT_NAME OBJECT_TYPE
> ------------------------- ------------------------------
> ------------------------------ --------------------
> SELECT STATEMENT
> MERGE JOIN
> SORT JOIN
> NESTED LOOPS OUTER
> NESTED LOOPS
> TABLE ACCESS BY INDEX ROWID BKPRODUCTTITLE
> INDEX UNIQUE SCAN
> PK_BKPRODUCTTITLE UNIQUE
> TABLE ACCESS BY INDEX ROWID BKBOOKRELEASE
> INDEX RANGE SCAN
> PK_BKBOOKRELEASE UNIQUE
> TABLE ACCESS BY INDEX ROWID BKCOMPANY
> INDEX UNIQUE SCAN
> PK_BKCOMPANY UNIQUE
>
> OPERATION OPTIONS
> OBJECT_NAME OBJECT_TYPE
> ------------------------- ------------------------------
> ------------------------------ --------------------
> SORT JOIN
> TABLE ACCESS FULL BKFORMAT
>
> 13 rows selected.
>
> SYSTEM:STAGING/SQL>l
> 1* select table_name,column_name from user_ind_columns where
> table_name in ('BKBOOKRELEASE' , 'BKPRODUCTTITLE', 'BKCOMPANY',
> 'BKFORMAT')
> SYSTEM:STAGING/SQL>/
>
> TABLE_NAME COLUMN_NAME
> ------------------------------ ------------------------------
> BKBOOKRELEASE MUZEID
> BKBOOKRELEASE PRELREFNUM
> BKCOMPANY COMPANYID
> BKCOMPANY COMPAKANUM
> BKFORMAT RELEASEFORMAT
> BKPRODUCTTITLE MUZEID
> BKPRODUCTTITLE TITLEAKANUM
>
> 7 rows selected.
>
> select count(*) from BKBOOKRELEASE;
>
> COUNT(*)
> ----------
> 1731833 select count(*) from BKPRODUCTTITLE
> SYSTEM:STAGING/SQL>/
>
> COUNT(*)
> ----------
> 1744142 select count(*) from BKFORMAT
> SYSTEM:STAGING/SQL>/
>
> COUNT(*)
> ----------
> 839
> select count(*) from BKCOMPANY
> SYSTEM:STAGING/SQL>/
>
> COUNT(*)
> ----------
> 67720
>
> Any ideas ,hints,comments ,
> outbursts will be appreciated.
>
> Tapan
Received on Wed May 17 2000 - 00:00:00 CDT