How to tune this sucker ??

From: ttrivedi <ttrivedi_at_deja.com>
Date: 2000/05/16
Message-ID: <3921C898.787AE7E4@deja.com>#1/1

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 Tue May 16 2000 - 00:00:00 CDT