01-06-2014 8:47 AM
Hi
i created an index to a z table i've created. i am loading 2000000 records to this table and want the select to use the new index i created.
When i select from the Z table according to the index keys he doesnt use this index but use index 0 ( the deafult index ).
What can i do in order to make the select use my index , when i use the HINT command i get the message that the index isnt active which is incorect.
but after i do DB20 the select use this index?
Is DB20 is a must ? how can i make the select use my index other than by HINT ?
Thanks Ami
01-06-2014 9:07 AM
Hi Ami
is your index active from SE11 .. and you need to check the table in DB20 transaction to ensure that statistics for this table is updated.
Only when the statistics is updated , it will be taken into account by the CBO.
Thanks
Rishi
01-06-2014 8:53 AM
Please check status of index in SE11. It should be activate and saved as shown below
01-06-2014 9:07 AM
Hi Ami
is your index active from SE11 .. and you need to check the table in DB20 transaction to ensure that statistics for this table is updated.
Only when the statistics is updated , it will be taken into account by the CBO.
Thanks
Rishi
01-06-2014 10:38 AM
hi thanks to the reply
according to what u say each time i m adding 1,000,000 records to the z table i need to run DB20 in order to use the new index or only for the first time im INSERTING RECORDS for the table ?
Thanks Ami
01-06-2014 11:47 AM
This will be needed for the 1st time to create the statistics .
After that the update stats job ( which should be active in your system) to update any required statistics in tables / indexes should be fine.
Thanks
Rishi