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

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

: Maybe you can help me on this problem with the CBO.

: If I use a simple query like

: select * from TABLE_A a, TABLE_B b
: where a.NAME = b.NAME and a.KEY_IN_A = 'X';

: the CBO will look at the table and index statistics for both tables
: to guess which query plan has the lowest costs.
: In addition to the table statistics it will look at the system
: statistics to adjust the costs for IO and CPU to the available hardware
: and some configuration parameters like optimizer_index_cost_adj
: and alike.

: The statistics for these tables can be produced with ANALYZE
: or DBMS_STATS.

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

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.

$0.10 Received on Wed Aug 16 2006 - 16:13:54 CDT