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

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: 17 Aug 2006 06:58:46 -0700
Message-ID: <1155823126.029331.201740@75g2000cwc.googlegroups.com>

Volker Apelt wrote:
> -- with FUNCTION
> SELECT /*+ cardinality(SG, 200) */ * FROM (
> SELECT * from TABLE (
> TEST_MODUL.test_data(
> CURSOR(SELECT * FROM TEST_SMALL_RESULTSET )
> , 5
> )) SG);
> -- PLAN shows Card=8168
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE
> (Cost=11 Card=8168 Bytes=318552)
> 1 0 VIEW (Cost=11 Card=8168 Bytes=318552)
> 2 1 VIEW (Cost=11 Card=8168 Bytes=1078176)
> 3 2 COLLECTION ITERATOR (PICKLER FETCH) OF 'TEST_DATA'

> Is that the correct way to use the cardinality hint with functions?
> Is its dependent on oracle version? We have 9.2.0.7.

I'm also doing tests on the same version.

In your particular case alias SG is in parenthesis and hint is outside them. Although I've wrote it correctly and unfortunately got the wrong results anyway. It is strange because using only types we got the desired result:

Create Type TBL_IDs As Table Of Number(13) /

explain plan for
SELECT /*+ cardinality (pk, 100) */ column_value FROM TABLE(CAST(:v_rrpr_ids_old AS tbl_ids)) pk /

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

| Id  | Operation                         |  Name       | Rows  | Bytes

| Cost |

|   0 | SELECT STATEMENT                  |             |   100 |
|    11 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH|             |       |
|       |
---------------------------------------------------------------------------------

here rows are 100 as hinted.

Ok anyway probably you can try folowing using first_rows(1) hint in upper query. It doesn't work in subquery giving this magic cardinality 8168, but outside the subquery it works.

SQL> SELECT /*+ first_rows (1) */ * FROM (   2 SELECT
  3 * from TABLE(TEST_MODUL.test_data(CURSOR(SELECT * FROM TEST_SMALL_RESULTSET T), 5))) pk
  4 /
Elapsed: 00:00:00.00

Execution Plan

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

   1    0   VIEW (Cost=1 Card=1 Bytes=39)
   2    1     VIEW (Cost=11 Card=8168 Bytes=1078176)
   3    2       COLLECTION ITERATOR (PICKLER FETCH) OF 'TEST_DATA'

>

> My goggle research showed me some other approaches like

> /* FIRST_ROWS DYNAMIC_SAMPLING(TABLE_ALIAS 2) */
> to let the optimizer peek into the return values
> DYNAMIC_SAMPLING seems to switch from CBO to RBO, right?

No to RBO you can switch only using hint RULE. DYNAMIC_SAMPLING in its very nature is very CBOish :)

> It had no effect in my queries.

Yea, because if I remember correctly it had no effect on pipelined functions, it has effect only on tables.

> I have already read T.Kyte's examples on his web site
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3779680732446#15740265481549
> Subject "Oracle 9i generates statistics automatically?"
> comment in reply to Steve's questions from February 18, 2004
>
> and can reproduce his example with STR2TBL as function.

I'll try to read about it more later.

> (its in the middle of the topic.)
> But, if I try my own procedure in his kind of query
> CARDINALITY shows no effect.
> I tried many different versions and hints, but failed.
>
> You can find the complete source code below.

Thanks. It helped, to get at least the idea given above :)

> I hope to get away with two cases, one for few rows
> and one for nearly all. But first I have to find two
> different queries that work.

> If you have any comment to my example code below or
> have an working example please let me know.

Too much reading, too little time :(

Gints Plivna
http://www.gplivna.eu Received on Thu Aug 17 2006 - 08:58:46 CDT