Message-ID: <F001.00484A30.20020621145319@fatcity.com>
Date: Fri, 21 Jun 2002 14:53:19 -0800
To: Multiple recipients of list ORACLE-L <ORACLE-L@fatcity.com>
Sender: root@fatcity.com
Reply-To: ORACLE-L@fatcity.com
Errors-To: ML-ERRORS@fatcity.com
From: Stephane Faroult <sfaroult@oriole.com>
Subject: Re: Urgent - Query Optimization
Organization: Fat City Network Services, San Diego, California
Precedence: bulk
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Inder.Singh@gecits.ge.com wrote:
> 
> Hi,
> I have a report which was taking 48 minutes. So I added rule hint in that
> now it is taking 14-15 min but, still it's not acceptable.. Is there any way
> I can elimintae these nested loops or Can I replace them with hash joins. or
> is there any other way to optimize this query. I don't have always_semi_join
> to hash_joins in the init.ora file and I don't want to set that in init.ora.
> Is there any session level setting like any rule which enforces the hash
> join.
> 
> Regards,
> SELECT STATEMENT Optimizer=HINT: RULE
>   SORT (UNIQUE)
>     NESTED LOOPS
>       NESTED LOOPS
>         NESTED LOOPS
>           NESTED LOOPS
>             NESTED LOOPS
>               NESTED LOOPS
>                 NESTED LOOPS
>                   NESTED LOOPS
>                     NESTED LOOPS
>                       NESTED LOOPS
>                         NESTED LOOPS
>                           NESTED LOOPS
>                             NESTED LOOPS
>                               NESTED LOOPS
>                                 NESTED LOOPS
>                                   NESTED LOOPS
>                                     NESTED LOOPS
>                                       NESTED LOOPS
>                                         TABLE ACCESS (FULL) OF
> TABLE_EMP_CERT
>                                         TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_CERTIFICATION
>                                           INDEX (UNIQUE SCAN) OF
> CERTIFICATION_OBJINDEX (UNIQUE)
>                                       TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_PART_CLASS
>                                         INDEX (UNIQUE SCAN) OF
> PART_CLASS_NAME_INDEX (UNIQUE)
>                                     TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_EMPLOYEE
>                                       INDEX (UNIQUE SCAN) OF
> EMPLOYEE_OBJINDEX (UNIQUE)
>                                   TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_PART_NUM
>                                     INDEX (RANGE SCAN) OF
> IND_PART_NUM2PART_CLASS (NON-UNIQUE)
>                                 TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_MOD_LEVEL
>                                   INDEX (RANGE SCAN) OF
> IND_PART_INFO2PART_NUM (NON-UNIQUE)
>                               TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_SITE_PART
>                                 INDEX (RANGE SCAN) OF
> IND_SITE_PART2PART_INFO (NON-UNIQUE)
>                             TABLE ACCESS (BY INDEX ROWID) OF TABLE_CASE
>                               INDEX (RANGE SCAN) OF IND_CASE_PROD2SITE_PART
> (NON-UNIQUE)
>                           TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE
>                             INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE)
>                         TABLE ACCESS (BY INDEX ROWID) OF TABLE_ADDRESS
>                           INDEX (UNIQUE SCAN) OF ADDRESS_OBJINDEX (UNIQUE)
>                       TABLE ACCESS (BY INDEX ROWID) OF
> TABLE_X_CASE_INFORMATION
>                         INDEX (RANGE SCAN) OF CASE_OBJIDINDEX (NON-UNIQUE)
>                     TABLE ACCESS (BY INDEX ROWID) OF TABLE_DEMAND_HDR
>                       INDEX (RANGE SCAN) OF IND_DEMANDHDR_CASEINFO2CASE
> (NON-UNIQUE)
>                   TABLE ACCESS (BY INDEX ROWID) OF TABLE_DEMAND_DTL
>                     INDEX (RANGE SCAN) OF IND_DEMAND_DTL2DEMAND_HDR
> (NON-UNIQUE)
>                 TABLE ACCESS (BY INDEX ROWID) OF TABLE_CONDITION
>                   INDEX (UNIQUE SCAN) OF CONDITION_OBJINDEX (UNIQUE)
>               TABLE ACCESS (BY INDEX ROWID) OF TABLE_GBST_ELM
>                 INDEX (UNIQUE SCAN) OF GBST_ELM_OBJINDEX (UNIQUE)
>             TABLE ACCESS (BY INDEX ROWID) OF TABLE_MOD_LEVEL
>               INDEX (UNIQUE SCAN) OF MOD_LEVEL_OBJINDEX (UNIQUE)
>           TABLE ACCESS (BY INDEX ROWID) OF TABLE_PART_NUM
>             INDEX (UNIQUE SCAN) OF PART_NUM_OBJINDEX (UNIQUE)
>         TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE
>           INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE)
>       TABLE ACCESS (BY INDEX ROWID) OF TABLE_SITE
>         INDEX (UNIQUE SCAN) OF SITE_OBJINDEX (UNIQUE)
> 
> --

What you are posting here is hardly helpful (in terms of uselessness, it
comes second to 'hallo, here are the contents of V$WAITSTAT and events
plus my init.ora, please rewrite the query').
When you want to optimize a query, it's better to start with the query,
vague hints (not in the Oracle acceptance) about volumes, indexes,
number of rows expected and selectivity of criteria.
-- 
HTH,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult@oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).