Hi,
What is the purpose of ignoring derive indexes? Shouldn't SQL try to use
all possible access path to gain performance?
Sure, but DDS described logical files with select/omit clauses can only be
used by the CQE (Classic Query Engine).
In this way all queries executed against physical files that have DDS
described logical files with select/omit clauses, are rerouted to the old
CQE.
The only way to avoid the rerouting is to change the the option
INGNORE_DERIVED_INDEXES in the QAQQINI-File from *NO to *YES.
Executing Queries with the SQE will in most cases be much more performant
than executing the same query with the CQE.
If an access path is missed, the existing logical file can be deleted, an
index with the appropriate key fields created and the logical file
recreated. In this way, the access path stored in the SQL index can be used
by the SQE and the DDS described logical file will share the access path
with the SQL index. That means there is only a single access path that must
be actualized. BTW a DDS described logical file can share access path with a
SQL index, but a SQL index cannot share access paths with DDS a described
logical file, because of the larger page size of the SQL index (64K versus
8K for DDS files)
With 6.1. the SQE is terminated and with the new derived indexes most
logical files with select/omit clauses can be replaced.
In this way it should be possible to execute ALL queries with the SQE.
That's why the default value for IGNORE_DERIVED_INDEXES was changed to *YES.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and loosing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Lim Hock-Chai
Gesendet: Friday, 05. September 2008 17:37
An: rpg400-l@xxxxxxxxxxxx
Betreff: Re: Sql Table / View / index issue
What is the purpose of ignoring derive indexes? Shouldn't SQL try to
use all possible access path to gain performance?
"Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx> wrote in message
news:<mailman.8549.1220550768.2545.rpg400-l@xxxxxxxxxxxx>...
Hi,
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.
midrange.com
