Re: How to tune this sucker ??

Are they all tables or are one or two of them views? I'm puzzled why the explain plan is doing a sort on bk_format.

The outer joins are not helping either but I guess you can't avoid that.

M

In article <3921C898.787AE7E4_at_deja.com>, ttrivedi <ttrivedi_at_deja.com> 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
>
>
>
>
>
>
>
>
>
>

• Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free!