Re: Explain Plan Question

On Mon, 12 Nov 2001 06:09:29 GMT, "Zoom Zoom Zoom" <nospam_at_nospam.com> wrote:

>Do the number of rows in tables change the output for Explain Plan (tkprof)
>on the same machine? The Optimizer used is Rule based.
>
>Let's say I run a query:
>
>SELECT /*+ RULE */ COUNT(*)
>FROM tableA a, tableB b
>WHERE a.id = b.dept_id (+);
>
>First time let's say there are 0 rows in each of these tables. Now I run
>tkprof and get the results .
>
>Second time let's say there are 1000+ rows in both of these tables. Will the
>results of explain plan will be same as in previous case ?
>
>Thanks
>
>- Zoom
>

Yes are you are using the rule based optimizer (which you shouldn't do, as it it obsolete and hasn't been improved since 1994)
Rule based optimizer works with heuristics, it disregards completely the number of rows in a table, it will use an index when an index is present, even if a full table scan would result in better performance.

Hth

Regards
Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Nov 12 2001 - 00:43:58 CST