Re: Sql Table / View / index issue

[CRPence <CRPbottle@xxxxxxxxx>]

  The IGNORE_DERIVED_INDEXES refers to ignoring DDS created derived 
indexes, which are often incompatible with SQE.  Incompatible due to 
their having originated with\for RLA versus SQL access.  Beyond the 
compatibility issue, it is not sufficiently easy to translate all of the 
attributes of the DDS definition into SQL in a generic sense.  The 
existing translations are consistent and compatible only with the CQE 
[in alignment with RLA concepts] rather than the SQE [in alignment with 
SQL concepts]; i.e. more compatible for OPNQRYF and QRY400.  For example 
the CQE may have some requirements to honor special attributes like 
FIFO, but the SQL has no similar concept and so its performance should 
not be impacted just to do so.  The SQL expects such decisions must be 
effected from the data itself; e.g. for FIFO, by using an ascending 
ORDER BY of a TIMESTAMP field that denotes when the row was inserted.

  The request to ignore is really nothing more than a request of the DB 
engine to "please [do] not use the CQE path" just because if\when a DDS 
derived index is found over the data.  Restated, it is a request to "Use 
the more efficient and faster SQL-aligned SQE to perform the query, if 
it is supported for this query, rather than deferring immediately to CQE 
to perform this query just because a DDS derived index exists."

Regards, Chuck

Lim Hock-Chai wrote:
> What is the purpose of ignoring derive indexes? Shouldn't SQL try to 
> use all possible access path to gain performance?
>
> "Birgitta Hauser" wrote:
> > in release 6.1. SQL will check the derived indexes and know about
> > the where clauses or new fields you created. But the SQE optimizer
> > will not use DDS described logical files with select/omit clauses. 
> > BTW the default value for IGNORE_DERIVED_INDEXES in the
> > QAQQINI-File get changed in release 6.1. from *NO to *YES. That
> > means by default logical files with select/omit clauses now are 
> > ignored while optimizing the query.