Just a "wouldn't it be nice if" sort of feature request. I'm not sure how practical it is. Someone in our organization wrote a data fix query, which has sort of odd logic, but it does what they need. The problem is that it ran for 14 hours in a test against a copy of the data. I looked at it and figured it could do better with an extra index. The index took five minutes to build, and the run time for the query dropped to five minutes. The index is not needed for production, so it was then dropped. It struck me that it would be outstanding if the planner could recognize this sort of situation, and build a temporary index based on the snapshot of the data visible to the transaction. It seems to me that the obvious downside of this would be the explosion in the number of permutations the planner would need to examine -- based not just on what indexes ARE there, but which ones it could build. At a minimum, there would need to be a cost threshold below which it would not even consider the option. (In this case, as long as the optimizer spent less than 13 hours and 50 minutes considering its options, we would have come out ahead.) I'm not sure the details of this particular incident are that relevant, but I've attached the query and the two plans. -Kevin
Attachment:
datafix-Action.txt
Description: Binary data
Attachment:
datafix-plan1.txt
Description: Binary data
Attachment:
datafix-plan2.txt
Description: Binary data
Attachment:
datafix.qry
Description: Binary data
