index advisor

[Former Member]

hi,

can anyone please tell me how a secondary index can be deemed as effective?

i did trace for a user and the EXPLAIN shows:

Now after running index advisor and doing another EXPLAIN including the recommended secondary index, the new EXPLAIN shows:

Since the "Estimated Costs" (high-lighted in yellow) did not actually improve, this means that the recommended secondary index is not effective. Is my assumption correct? If not, how do I know if a secondary index is effective?

Thank you.

[Sriram2009]

Hi Caldoza

You can enable the transaction code ST12 trace on any transaction or Z reports, check the Sql trace seconder index used or not.

BR

SS

[Former Member]

Thanks SS for your reply.

Yes, I actually used ST12 to trace the user's transaction.

The first screenshot I posted is the result of that trace using EXPLAIN for the SQL statement with the longest duration.

The second screenshot is the EXPLAIN after the recommended secondary index is included in the analysis.

Now I would like to know, based on these screenshots, if the recommended secondary index is actually effective or not.

Do I simply base it on the "Estimated Costs" value?

[Sriram2009]

Hi

On your Development system enable the trace before creating the seconder index and then create the seconder index enable the ST12 trace, analysis the both traces and take the action.

BR

SS