Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to tune this sucker ??

Re: How to tune this sucker ??

From: <berj_kacherian_not_at_hotmail.com>
Date: 2000/05/17
Message-ID: <8fsod0$f77$1@nnrp1.deja.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US