Better explain plan but much worse performance
I have a problem query that touches a large EBS table (fnd_attached_documents). One strategy I tried was to rebuild the N1 index which already has 3 of the 5 columns referenced in the query, to have all 5. the first indexed column is entity_name.
If I leave entity_name off the query - the optimizer cost id 59, and shows what I would expect - a skip scan on the N1 index. The query returns about 2000 rows of data for the given parameters pretty fast - under a second.
If I then add to the where clause - and entity_name = ''MTL_SYSTEM_ITEMS' - the explain plan cost drops to 8 and shows what I would expect - a unique scan - but the query now sucks and takes over 10 seconds to return the same data.