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: Deep meaning of error 3232

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

Original text of this message

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