Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: realistic cardinality for PL/SQL procedures in SELECT statements

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

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


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

> Volker Apelt wrote:
>> -- with FUNCTION
>> SELECT /*+ cardinality(SG, 200) */ * FROM (
>
> I'm also doing tests on the same version.

Thank you.

> In your particular case alias SG is in parenthesis and hint is outside
> them.

OK, I rewrote that to (.. see below) but the problems remain.

SELECT * FROM (
SELECT /*+ cardinality(SG, 200) */* from TABLE (  TEST_MODUL.test_data(

      CURSOR(SELECT * FROM TEST_SMALL_RESULTSET )
      , 5

)) SG);

> SQL> select * from table(dbms_xplan.display);
[ .. ]
> here rows are 100 as hinted.

Same result for tables + cardinality hint here, too.

>> 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 :)

Ouch, of course DYNAMIC_SAMPLING is CBO.

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

Do you remember where you have read/heard that?

>> 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 :(

:-) Known problem ..
Please, see my comments on your later reply, too.

-- 
Volker Apelt                   
Received on Mon Aug 21 2006 - 09:27:03 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US