>      The notes that I hold from the Ingres Advanced Performance 
>      course indicate that there are 2 routes through the optimizer :-
>      
>      1) Non-enumeration - provides a fast path for single table 
>      queries when using primary key or scan; does not use statistics.
>      
>      2) Enumeration - uses optimizedb statistics.
>      
>      We want to cut down the amount of time allocated to optimizing 
>      tables (currently using -zk option), and I have highlighted some 
>      large tables which lie only in single table queries but not all 
>      of the primary keys are used.  The question is which of the two 
>      paths is followed with regard to these queries, or does anybody 
>      know of a trace point that I could use to check this out ?
>      
>      Thanks
>      
>      Paul Stevens
>      
>      Perot Systems Europe 
>      Email : paul.stevens&ccmail@psemail.ps.net
> 
The major caveat here is that a single table query may be presented to
the Ingres enumeration phase as a multi-table query. This is how Ingres
incorporates secondary indexes into query plans - if the where clause of
a query references an indexed (by secondary index) column, Ingres will 
introduce the index as a second "table" (under the covers) and will
cost a TID join between the index and the base table as one possible
strategy for solving the query. If your "single table" queries fall into
this category, not optimizing the secondary index columns could lead 
to poorly costed secondary index strategies, resulting in poor query
plans. Only tables with secondary indexes will have this problem. 

Trace point op156 dumps a bunch of stuff related to the range table
of a query, as well as where factors and other information. The range table
is the list of tables referenced in the query, and will include any
secondary indexes introduced to improve optimization potential. If the
queries in question have single entry range tables in the op156 output,
they will take the non-enumeration path described in your question. 
Please don't call tech support for help with the op156 - they won't be 
happy with me for telling you about it!

Doug.

Ingres Q & A

To William's Home Page

© William Yuan 2000

Email William