Re: V6 Optimizer?: Query based on view
Date: Fri, 15 Oct 93 21:50:35 GMT
Message-ID: <1993Oct15.215035.5857_at_sequent.com>
rchance_at_netcom.com (Ray Chance) writes:
FORMS will work by querying the view with simple conditions built on the base table fields that you have supplied values for. It leaves any optimizing attempts to the basic SQL optimizer in Oracle.
Thus, using EXPLAIN PLAN for a query that uses the same criteria against the same view should yield the same execution plan. Make sure that your EXPLAIN PLAN criteria are in the same datatype that the fields are defined as in the form. This can make a huge difference, since datatype conversions can prevent indexes from being selected. For instance, if you are storing number in a character field in the database, and you have the form field defined as a number, forms will pass the field as a number, like
FIELD1 = 1 rather than
FIELD1 = '1' Assuming that FIELD1 is a character field in the database, only the second example would yield the use of an index on FIELD1.
No view is ever 'stored' temporarily, but if the optimizer cannot easily construct a way to add your criteria to the view's criteria and go directly to the base tables, than the entire view will be resolved and your criteria will be used as a filter for the view results. This, of course, is to be avoided if at all possible.
-G.
>I'm trying to analyze the (poor) performance of a Forms detail block query
>based on a view. When I evaluate the view alone using EXPLAIN PLAN (join of
>four tables) I've got a full table scan on one table, indexes for others. If
>I add the criteria that comes from the master block in the Form, then I use
>indexes on all four tables.
>??: How do I know which is the situation that occurs when the Form is actually
>being used? Is the result of the view stored temporarily in which case there
>would be no index on the master-detail block column applied, or is the screen
>criteria "tacked on" to the end of the SQL statement when the view is parsed,
>thereby (maybe?) using all four indexes?
>rchance_at_netcom.com
Received on Fri Oct 15 1993 - 22:50:35 CET