IGNORE_DERIVED_INDEX

[waytoajay@rediffmail.com]

Hi All

When shoudl we use IGNORE_DERIVED_INDEX?

[lostromich]

Hi Ajay.

If you have SQL which directed to CQE because of DDS-based LF (ex:LF with Select/Omit options) and this SQL perform not well then you could try to set IGNORE_DERIVED_INDEX to *YES and tune the SQL (create indexes based on Query Optimizer advice). The above action could significantly improve performance.

In future think about DB modernization and replace DDS-based PFs,LFs with SQL-based Tables, Indexes and Views and replacing native I/O with SQL I/O. After DB modernization you may no need IGNORE_DERIVED_INDEX at all.

[FriskyRex]

you can always create indexes just by analyzing your SQL statements as well.

Always put an index on:

• Local selections
• Join fields
• group by fields

[Elvis B.]

It'll be set to *YES by default in next release anyway so my vote is for setting IGNORE_DERIVED_INDEX to *YES in all circumstances.

Check out Centerfield's article "SQE vs CQE - Part II" on page 6 of October 2007 volume for one exception I ran into:

http://www.centerfieldtechnology.com.../SeptOct07.pdf