NESTED LOOP with FULL TABLE scan

From: <yywong_at_my-deja.com>
Date: Tue, 31 Oct 2000 02:39:53 GMT
Message-ID: <8tlbdp$o2l$1@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. Received on Mon Oct 30 2000 - 20:39:53 CST