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

From: Volker Apelt <gq437x_at_yahoo.de>
Date: Mon, 21 Aug 2006 16:27:11 +0200
Message-ID: <lgejvai2ps.fsf@chi.biosolveit.loc>

"Gints Plivna" <gints.plivna_at_gmail.com> writes:

> 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.

Partly. Two plans are better than only one. I re-did the test case with an PK index as you suggest below.

> 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.

My test case missed the PK index, while the real data has a primary key index.

> SQL> set autotrace traceonly explain
> 1 select /*+ first_rows(1) */* from TEST_SMALL_RESULTSET T
..
> 1 select /*+ all_rows */* from TEST_SMALL_RESULTSET T
..

These are still queries after T.Kyte's example. Our real query is like

select *
from
 (select * from TEST_SMALL_RESULTSET ) T,  (select ID_TABLE_A FROM TABLE

    (cast(TEST_MODUL.test_data(CURSOR(

          SELECT * FROMTEST_SMALL_RESULTSET),5) 
          as test_data_ids 
          ) 

    ) SG1
  ) SG
where T.ID_TABLE_A = SG.ID_TABLE_A
;

It shows the same swithc from HASH JOIN to NESTED LOOPS for first_rows(1) to all_rows, too.

select /*+ all_rows */*
from (select * from TEST_SMALL_RESULTSET ) T, ( select /*+ cardinality(SG1 10) */ ID_TABLE_A   FROM TABLE (cast(TEST_MODUL.test_data( CURSOR(SELECT * FROM TEST_SMALL_RESULTSET), 5) as test_data_ids)) SG1   where rownum >= 0
) SG
where T.ID_TABLE_A = SG.ID_TABLE_A
;
Execution Plan

---

   0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=12 Card=16360 Bytes=850720)    1 0 HASH JOIN (Cost=12 Card=16360 Bytes=850720)

   2    1     VIEW (Cost=3 Card=10 Bytes=390)
   3    2       TABLE ACCESS (FULL) OF 'TEST_SMALL_RESULTSET' (Cost=3 Card=10 Bytes=440)
   4    1     VIEW (Cost=9 Card=16360 Bytes=212680)
   5    4       COUNT
   6    5         FILTER
   7    6           VIEW (Cost=9 Card=16360 Bytes=818000)
   8    7             COLLECTION ITERATOR (PICKLER FETCH) OF 'TEST_DATA'

select /*+ first_rows(1) */*
from (select * from TEST_SMALL_RESULTSET ) T, ( SELECT /*+ cardinality(SG1 10) */ ID_TABLE_A   FROM TABLE (cast(TEST_MODUL.test_data( CURSOR(SELECT * FROM TEST_SMALL_RESULTSET), 5) as test_data_ids)) SG1   where rownum >= 0
) SG
where T.ID_TABLE_A = SG.ID_TABLE_A
;
Execution Plan

---

   0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=3 Card=1 Bytes=41)    1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=41)

   2    1     VIEW (Cost=1 Card=10 Bytes=280)
   3    2       TABLE ACCESS (FULL) OF 'TEST_SMALL_RESULTSET' (Cost=3 Card=10 Bytes=440)
   4    1     VIEW (Cost=2 Card=1 Bytes=13)
   5    4       COUNT
   6    5         FILTER
   7    6           VIEW (Cost=9 Card=16360 Bytes=818000)
   8    7             COLLECTION ITERATOR (PICKLER FETCH) OF 'TEST_DATA'

I'll try those HINTS with the production query and see if I can remove the PIPELINED Attribute from the procedure.

Thank you,

-- 
Volker Apelt                   

Received on Mon Aug 21 2006 - 09:27:11 CDT