This is a Tom Kyte trick, that I found very useful.
I have a query that runs slow , and after looking at the explain plan, I see a full table scan, and what the cost is.
I was wondering what the cost would be if I put a composite index on 2 columns to stop a full table scan.
This being an inuse performance database, I can't do this without affecting tests, or can I ???
Oracle has something called "virtual indexes" . The tuning packs using this funtionality to try different solutions, and you can use them too. They don't physically exist, and users don't see them so they have no affect on the database. They get created instantaneusly, so testing is quick. Here is what I did.
http://www.dbasupport.com/oracle/ora9i/ ... exes.shtml
create index scott.employees_idx1 on scott.employee(first_name,last_name) nosegment; --- create the index no segment
exec dbms_stats.gather_index_stats('SCOTT','EMPLOYEE_IDX1'); -- make sure statistics are current
alter session set "_use_nosegment_indexes" = TRUE ; -- default is false
Run your explain plan in your session, and it will tell you cost using this index, or if it will use the index.
Drop the index, and decide whether to create it for real.