Re: good guess for final record number in SELECT?

From: Ed Prochak <edprochak_at_adelphia.net>
Date: Thu, 27 Feb 2003 02:31:29 GMT
Message-ID: <3E5D7C21.1030102@adelphia.net>

Volker Apelt wrote:
> danielroy10_at_hotmail.com (Daniel Roy) writes:
>
>

>>A good answer to your question depends significantly on the select
>>statement being issued. If your tables and indexes have been analyzed
>>recently (i.e. you use CBO), have you thought of using the data in
>>user_tables (NUM_ROWS) and user_indexes (DISTINCT_KEYS)? This might
>>allow you to at least get a high-level idea on the number of rows
>>returned.

>
>
> Using user_tables and user_indexes is an interesting idea, but
> the result tables will be a small sub set of some huge tables
> which are restricted by some WHERE clauses.
>
> But, thank you it is somthing to think about.
>
> Volker
>

You can possibly devise an estimate function that uses the row counts from the Stats info Dan mentioned along with the known relationships (1-1, 1-many). Maybe two possible values, a high estimate (always assuming 1-many has a fixed "many" value greater than 1) and a lower estimate where all joins assume 1-1.

So the low value is the NUM_ROWS value of the joined table with the MOST number of rows.

The high value let's say uses a value of 3 (1-many is 1-3 on average), then the number of the max rows value numtiplied by 3 for each additional table joined in a 1-many relation.

If you are looking for ballbark quessimates, something like those might give a handle on it. (You can add your own fudge factor as you get more experience with it.)

Just some thoughts on it. HTH

-- 
Ed Prochak
running: http://www.faqs.org/faqs/running-faq/
family:  http://web.magicinterface.com/~collins
--
"Two roads diverged in a wood and I
I took the one less travelled by
and that has made all the difference."
robert frost

Received on Wed Feb 26 2003 - 20:31:29 CST