Re: NESTED LOOP with FULL TABLE scan

You forgot to mention that you had two
partitioned tables, so we don't know what the partitioning key is (presumably the date).

If you look at the costing on your queries, you will note that Oracle has a cardinality of 1 on its scan of TEST1 0 i.e. it thinks it gets one row from the scan. Consequently it thinks that a sort/merge to TEST3 is a waste of a sort - a single scan is all that is needed.

Similarly, the result of the nested loop also returns has a cardinality of 1, so again the minimum extra work is a simple scan.

Something about your data distribution has fooled the optimizer into massively underestimating the rows likely to be returned at each stage.

Check if the TABLE-level stats on the partitioned tables are null.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison Wesley Longman
Book bound date now 1st Dec 2000

yywong_at_my-deja.com wrote in message <8tlbdp$o2l$1_at_nnrp1.deja.com>...


>Can anyone tell me what motivation does a NESTED LOOP join on 3 tables

>with FULL TABLE scans?

>We have a query joining 3 tables together and want to perform an merge

>join on them.  However, we cannot do this without hardcoding the hints

>of (use_merge).  It sounds strange as our testing tables are all very

>large in size. 2 of them even already hinted to use (full).  An group

>by and order by operations are already inserted.

>This problem seems not to occur in 8.0.4.4.0 (now is 8.0.6.1.1) and I

>found that the optimizer seems to be difference in MANY MANY places on

>8.0.6.1.1.  Is this normal or any initial parameters can solve the

>problem?  As in my previous post, the optimizer really retrieving a

>wrong index when using the hints (INDEX) without specifying an index

>name because it wrongly classify the index has lower (Cost?) even its

>join doesn't refer on the index.

>

>

>                -- Testing Query & Hints Generated --

>

>/* Remark: test1 and test3 are views with hints to use (FULL) */

>/*         When using (USE_MERGE) hints, the query returns in */

>/*         5 minutes.  If no hints, it will never return.     */

>/*         However, we cannot tell the users to use hints or  */

>/*         build MANY MANY views for the users to meet target */

>/*         with only a mirror change.                         */

>

>select t1.t1_date,

>       t2.t2_date,

>       decode(t1.t1_type, 'N', 'NO', 'YES'),

>       count(t1.t1_key)

>from   test1 t1,

>       test2 t2,

>       test3 t3

>where  t1.t1_date = t3.t3_ref_date

>and    t3.def_date = t2.t2_ref_date

>and    t1.ref_date between '01-jan-2000' and '31-jan-2000'

>group by t1.t1_date,

>         t2.t2_date,

>         decode(t1.t1_type, 'N', 'NO', 'YES')

>order by t1.t1_date,

>         t2.t2_date,

>         decode(t1.t1_type, 'N', 'NO', 'YES')

>

>

>

> Ci  Pi Statement  (Cost Cardinality Bytes)

>--- --- ---------------------------------------------------------------

>  0     SELECT STATEMENT optimizer=CHOOSE (2769 1 213)

>  1   0   SORT* (GROUP BY) (2769 1 213)

>  2   1     NESTED LOOPS* (2746 1 213)

>  3   2       NESTED LOOPS* (2505 1 128)

>  4   3         PARTITION* (CONCATENATED)

>  5   4           TABLE ACCESS* (FULL) of TEST1 (2445 1 76)

>  6   3         TABLE ACCESS* (FULL) of TEST3 (82 356585 18542420)

>  7   2       PARTITION* (CONCATENATED)

>  8   7         TABLE ACCESS* (FULL) of TEST2 (328 234594 19940490)

>

>9 rows selected.

>

>

>           Total Cost     Total Cardinality           Total Bytes

>--------------------- --------------------- ---------------------

>                13644                591184              38483753

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.