Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: realistic cardinality for PL/SQL procedures in SELECT statements
"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
> 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 ApeltReceived on Mon Aug 21 2006 - 09:27:03 CDT