Secondary Index Problem

[Former Member]

Hi All,

I created Secondary index and it works perfectly in my development system. But the same index is not working in my Testing system.

I tried with %_HINTS ORACLE 'INDEX("table" "table~index_name")' also,but it is not working .

Kindly suggest me to get solution for this.

[Former Member]

Hi All,

I created Secondary index and it works perfectly in my development system. But the same index is not working in my Testing system.

I tried with %_HINTS ORACLE 'INDEX("table" "table~index_name")' also,but it is not working .

Kindly suggest me to get solution for this.

[Former Member]

hi,

Check out whether the secondray Index is properly transporting or not ??

Regards,

Santosh

[Former Member]

The database system sometimes does not use a suitable index for a selection, even if there is one. The index used depends on the optimizer used for the database system. You should therefore check if the index you created is also used for the selection

Please use below procedure to check if an Index that u have created is Used

Procedure

Open a second session and choose System > Utilities > Performance trace.

The Trace Requests screen appears.

Select Trace on.

The SQL trace is activated for your user, that is all the database operations under your user are recorded.

In the first window, perform the action in which the index should be used.

If your database system uses a cost-based optimizer, you should perform this action with as representative data as possible. A cost-based optimizer tries to determine the best index based on the statistics.

In the second session, choose Trace off and then Trace list.

The format of the generated output depends on the database system used. You can determine the index that the database used for your action with the EXPLAIN function for the critical statements (PREPARE, OPEN, REPOPEN).

I hope it helps.

Thanks,

Vibha

Please mark all the useful answers

[Former Member]

First make sure that the index has been transported correctly and is active in your test box.

Then I would run a SQL trace in DEV and TST and look at the SQL statement that accesses your table with the index with explain SQL to see why the optimized decided to go for a different index in TEST.

Make sure that the all the fields of the index are selected with values (or at least the fields at the beginning).

E.g. if the first field of your index has no selection value and this index would be used, it would be a full table scan anyway because the first column didn't had any values and in that case the optimizer will look for a different index to use.

Hope that helps,

Michael

[Former Member]

Hi

My index was transported correctly and it is active in Testing system. I tested with SQL trace also.From that only i found that my Secondary index is not working in my Testing system.

I used only index field in the where condition and it is value is not initial.

[Former Member]

Hi Joe

Check for following :

1. Transport is done properly

2. Make sure Test system should not have any similar type of secondary index( check for all the field combination)

Hope this helps you .

Regards

Hemant