Re: How to tune this sucker ??

He was not that wise. at most two joins!!

<berj_kacherian_not_at_hotmail.com> wrote in message news:8fsod0$f77$1_at_nnrp1.deja.com...
> Break it up into smaller select statements. Make use of cursors.
>
> As a wise man once told me, you are allowed one join per select
> statement or two joins if you have been doing this for a few years and
> understand what you are doing. Nobody should be joining anything more
> than that!
>
> Berj
>
>
> 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
> >
> >
>
> --
> Out the Ethernet, past the firewall, through the router, down the
> T1...nothing but Net
>
> In order to email me remove the '_not' from my email address.
>
> --
> Out the Ethernet, past the firewall, through the router, down the
> T1...nothing but Net
>
> In order to email me remove the '_not' from my email address.
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed May 17 2000 - 00:00:00 CDT