Re: realistic cardinality for PL/SQL procedures in SELECT statements

OK I've played a bit more with your problem and I've got 2 different execution plans one with nested lops, one with hash join even without cardinality hints. I assume that is what you wanted.

Yea and I've changed your table id to not null and added primary key constraint, that of course is cruical to get index scan instead of n full scans.

SQL> set autotrace traceonly explain
SQL> ed
Wrote file afiedt.buf

  1 select /*+ first_rows(1) */* from TEST_SMALL_RESULTSET T   2 where ID_TABLE_A in (
  3 SELECT ID_TABLE_A
  4 FROM TABLE(cast(TEST_MODUL.test_data(cursor(select * from TEST_SMALL_RESULTSET), 5)
  5 as test_data_ids)) SG
  6 where rownum >= 0
  7* )
SQL> / Execution Plan

   0      SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=1 B
          ytes=60)

   1    0   NESTED LOOPS (Cost=4 Card=1 Bytes=60)
   2    1     VIEW OF 'VW_NSO_1' (Cost=1 Card=1 Bytes=13)
   3    2       SORT (UNIQUE)
   4    3         COUNT
   5    4           FILTER
   6    5             VIEW (Cost=11 Card=8168 Bytes=359392)
   7    6               COLLECTION ITERATOR (PICKLER FETCH) OF 'TEST_D
          ATA'

   8    1     TABLE ACCESS (BY INDEX ROWID) OF 'TEST_SMALL_RESULTSET'
          (Cost=1 Card=1 Bytes=47)

   9    8       INDEX (UNIQUE SCAN) OF 'TSR_PK' (UNIQUE)

SQL> ed
Wrote file afiedt.buf

  1 select /*+ all_rows */* from TEST_SMALL_RESULTSET T   2 where ID_TABLE_A in (
  3 SELECT ID_TABLE_A
  4 FROM TABLE(cast(TEST_MODUL.test_data(cursor(select * from TEST_SMALL_RESULTSET), 5)
  5 as test_data_ids)) SG
  6 where rownum >= 0
  7* )
SQL> / Execution Plan

   0      SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=14 Card=1 By
          tes=60)

   1    0   HASH JOIN (SEMI) (Cost=14 Card=1 Bytes=60)
   2    1     TABLE ACCESS (FULL) OF 'TEST_SMALL_RESULTSET' (Cost=2 Ca
          rd=10 Bytes=470)

   3    1     VIEW OF 'VW_NSO_1' (Cost=11 Card=8168 Bytes=106184)
   4    3       COUNT
   5    4         FILTER
   6    5           VIEW (Cost=11 Card=8168 Bytes=359392)
   7    6             COLLECTION ITERATOR (PICKLER FETCH) OF 'TEST_DAT
          A'

Gints Plivna
http://www.gplivna.eu Received on Sat Aug 19 2006 - 16:17:48 CDT