Re: Deep meaning of error 3232

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 3 Jul 2004 06:34:52 +0000 (UTC)
Message-ID: <cc5k2c$e6b$1@hercules.btinternet.com>

I won't say anything about the 3232, but if you have one hint in this query, you need a handful to make sure the one is not abused.

You said
> GLB_DETENTEUR is a temporary table (10-12 rows/tx max)

Oracle has got the cardinality for this as 8168 - which is why the join has gone wrong.
Options:
a) Get the tables in the right order then hint with

ordered use_nl(t1) index(t1 DETENTEUR_PK) or
b) Use dbms_stats.set_table_sets to tell Oracle just

        once that the GTT gets 10 rows (or less) if you
        prefer whenever it is used.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"SL" <no.spam_at_thank.u> wrote in message
news:cc3u2a$cf2_at_news.rd.francetelecom.fr...


>

> "SL" <no.spam_at_thank.u> a écrit dans le message news:

> cc3q2q$ae2_at_news.rd.francetelecom.fr...

> [snip]

> Before I get flamed, here is the exec plan of a query I managed to

> identify

> as part of the process described in my previous post

>

>   select /*+ INDEX(t1 DETENTEUR_PK) */

>   t1.*, t2.StatutTechnique

>     from DETENTEUR t1, GLB_DETENTEUR t2

>    where t1.REFTYPEELTAROUTER=t2.REFTYPEELTAROUTER

>      and t1.IDELTAROUTER=t2.IDELTAROUTER

>      and t1.CODEBASICATDETENTEUR=t2.CODEBASICAT

>      and t1.REFINSTANCEDETENTEUR=t2.REFINSTANCE

>

> SELECT STATEMENT, GOAL = CHOOSE    864   1    189

>  HASH JOIN         864   1    189

>   TABLE ACCESS BY INDEX ROWID      849   3466 308474

>        OF EFBADMIN.DETENTEUR

>    INDEX FULL SCAN                  23   3466

>     OF EFBADMIN.DETENTEUR_PK

>   TABLE ACCESS FULL                10    8168  816800

>    OF EFBTRAVAIL.GLB_DETENTEUR

>

> DETENTEUR is a heavy work table, GLB_DETENTEUR is a

> temporary table (10-12 rows/tx max)

>

> That query is the only one that features join of any type ;)

>

> Sorry for my mistake.

> --

> SL

>

Received on Sat Jul 03 2004 - 01:34:52 CDT