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

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: 16 Aug 2006 13:50:49 -0700
Message-ID: <1155761448.972684.288250@74g2000cwt.googlegroups.com>

> How can I tell the CBO what number of records the procedure
> returns?

With cardinality hint.

> So, the query can change its character from a 'pick one record'
> to a 'filter many records' and the change is driven by the
> size of the result set of the procedure.

We had the similar problem and it was resolved using 2 query versions something like:
if predicted_count <= treshold then
  SELECT /*+ cardinality(table_name, 1) */ column_list   FROM blabla
else
  SELECT /*+ ALL_ROWS */ column_list
  FROM blabla
end if

For the second case you can as well as add some big cardinality number if it is necessary.
For the first case you can try probably as well first_rows_1 hint. Our main business tables mostly have several M records and threshold we used was something like 500. Effectively for the first case there was index access and nested loops and for the second case full scans and hash joins. If you need you can create more than two versions of queries differing only in cardinalities and of course tune threshold as necessary.

Gints Plivna
http://www.gplivna.eu Received on Wed Aug 16 2006 - 15:50:49 CDT