index usage on view union, with subselect

From: bung ho <bung_ho_at_hotmail.com>
Date: 17 Dec 2002 20:31:33 -0800
Message-ID: <567a1b1.0212172031.c487c95@posting.google.com>

using 8.1.7.0 on linux.

i run the following script:

---

drop table t1;
drop table t2;

create table t1 (a int, b char(20));
create table t2 (a int, b char(20));

create index t1_pk on t1(a);
create index t2_pk on t2(a);

exec dbms_stats.set_table_stats( user, 'T1', numRows => 10000000, numBlks => 5000000 );
exec dbms_stats.set_table_stats( user, 'T2', numRows => 10000000, numBlks => 5000000 );

select a, b from
(
select a, b from t1
union all
select a, b from t2
)
where a in (select 1 from dual union select 2 from dual);

---

the query plan for that last query is:

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1399 Card=16400000 B
          ytes=459200000)

   1    0   NESTED LOOPS (Cost=1399 Card=16400000 Bytes=459200000)
   2    1     VIEW OF 'VW_NSO_1' (Cost=6 Card=82 Bytes=246)
   3    2       SORT (UNIQUE) (Cost=6 Card=82)
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)
   6    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)
   7    1     VIEW
   8    7       UNION-ALL (PARTITION)
   9    8         TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=2 Card=1
          00000 Bytes=3500000)

  10    9           INDEX (RANGE SCAN) OF 'T1_PK' (NON-UNIQUE) (Cost=1
           Card=100000)

  11    8         TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1
          00000 Bytes=3500000)

  12   11           INDEX (RANGE SCAN) OF 'T2_PK' (NON-UNIQUE) (Cost=1
           Card=100000)

nothing surprising there.

however, if i change the definition of table T1 to

create table t1 (a int, b char(20), c char(20)); -- one more column

and re-run the script, i suddenly get two FTS's:

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1527847 Card=1640000
          0 Bytes=459200000)

   1    0   HASH JOIN (Cost=1527847 Card=16400000 Bytes=459200000)
   2    1     VIEW OF 'VW_NSO_1' (Cost=6 Card=82 Bytes=246)
   3    2       SORT (UNIQUE) (Cost=6 Card=82)
   4    3         UNION-ALL
   5    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)
   6    4           TABLE ACCESS (FULL) OF 'DUAL' (Cost=1 Card=41)
   7    1     VIEW (Cost=1517738 Card=20000000 Bytes=500000000)
   8    7       UNION-ALL
   9    8         TABLE ACCESS (FULL) OF 'T1' (Cost=758869 Card=100000
          00 Bytes=250000000)

  10    8         TABLE ACCESS (FULL) OF 'T2' (Cost=758869 Card=100000
          00 Bytes=350000000)

am i not understanding something really obvious here? am i using dbms_stats.set_table_stats incorrectly? if not, why the big difference in plans? thanks in advance. Received on Tue Dec 17 2002 - 22:31:33 CST