There was a warehouse job having performance problem since the 11g upgrade.
My Test Database was on outage , so I had to tune it directly on the Production Database.
I know if a Index is created the issue will be resolved but the since the introduction of index would cause other problem , I had to create it in Invisible state.
create index index_for_testing on table table_name(column_name1,column_name2) invisible;
In the above statement the keyword invisible will not make the index ready to use.
The optimizer has to be hinted on the usage and then the session will start to use the index.
SQL> alter session set optimized_use_invisible_indexes=true;
SQL> exec start_job;
The index is now valid only for that session and that run.
After proper testing I will make the index permanent by the below syntax
SQL> alter index index_for_testing visible;
I hope this tip will be helpful when it comes to creating indexes directly on the production environment.
