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

DA Morgan <damorgan_at_psoug.org> writes:

> Volker Apelt wrote:
>> Oracle 9.2.0.7 on SuSE Linux (AMD 64)
>> db_block_size=<<16 kb>>

[there are statistics for tables used by the CBO]
>> But, what if the TABLE is created from a PL/SQL procedure
>> on the fly?
>> How can I tell the CBO what number of records the procedure returns?
>
> Consider also rewriting the query as there are many different constructs
> that, with different plans, will yield the result set.
>
> Nine different versions can be found at:
> http://www.psoug.org/reference/explain_plan.html

Dear Daniel,

I do like the documentation and hints on the psoug.org pages and have already tried many different versions of the production query.

We have compared the different query plans and identified a couple of problematic steps from slow queries, like PARTITION read in PARTITION LIST ALL while the partition key is constraint to exactly one value in the query and other problems like that.

One problem is the missing statistics for functions. The production query takes half an hour with the function, but if we spool the function return value to a table and replace the function call, it takes a few seconds even if the table is not analyzed and has no indexes. (This is for the 'pick a few records' case from my initial posting.)

So, with a function call CBO produces a unfavourable plan and I try to influence it.

Do you have an idea how implement that?

In another discussion i have seen someone recommend a on-the-fly transfromation of the query result to a in-memory temp table, by appending a WHERE ROWNUM >=0 which should help the CBO to get some insights to the real cardinality of the return set.

What do you think about this concept?
How do I check this transformation took place or didn't?  

Thank you,

Volker

-- 
Volker Apelt