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

yf110_at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) writes:

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

> : How can I tell the CBO what number of records the procedure
> : returns?
>
> In a similar situation I "spooled" the results into a global temporary
> table and used that table in the main query.

> Simply including some kind of hint to the size in the main query might be
> nice, but on the other hand you could still have the situation where your
> hint was wrong, whereas the size of the temp table is definitive, and
> (though I haven't tried this) I wonder if adding certain indexes to the
> temp table would allow oracle to use other details about the table as well
> as its size.

Thank you Malcolm for your reply,

A temporary tables as an intermediate stage has some advantages as you pointed out. But the query is used in a context where exactly on select statement is expacted by the application.

I'll first investigate the use of hints as described by others in this thread.

But a temporary table is an option.

Please see my other comments, too.

-- 
Volker Apelt