Re: Optimizer

Tom,

The optimizer Does Not force the view to be run before before the index. To the CBO (Cost Based Optimeizer - your friend) the view is resolved to it's actual SQL with the user supplied criteria before it attempts to apply optimization and perform the execution of the query. If your queries are doing FTS (Full Table Scans) then the optimizer either does not know of a better path (have the tables been analyzed with the indexes?) or it thinks a large percentage of the rows of the indexed table will be returned so a parallel ful scan will be faster than a serial indexed lookup. These concepts are discussed in the Server Docs and in most DBA/Tuning books. CBO will not be used on indexes unless the tables have been analyzed after the index creation because the CBO is ignorant of them until this is done.

HTH DBA Tom

"SHRIKE" <tdeseamus_at_impacthire.com> wrote in message news:t3ihe71h9vk00c_at_corp.supernews.com...
> I have some PL/SQL code that retrieves records based on user supplied
> criteria that runs against a view. The optimizer forces the view to be
 run
> before the index and a full table scan to be run against the view. This
 has
> caused the operation to slow down considerably. Does anyone know of a way
> to force the index to be run before the view? We have tested replacing
 the
> view with imbedded sql statements, and this works, however this course
 would
> involve a great deal of code re-write.
>
> Thanks,
> Tom
>
>
>
Received on Sun Dec 17 2000 - 08:16:24 CST