I have a fairly large query involving over 25 joins. It runs fine in 9i, but in 10g it takes a long time (often over 200 seconds). I am running the query on a database with no data in it (zero rows returned). Are there massive difference in the optimizer in 9 and 10 that might be causing this issue? It seems like when I reach a certain number of joins, the 10 instance starts taking a very long time to create the execution plan.
Does anyone have any hints or tips when reading the execution plan that might pinpoint what's wrong? I have books but I'm still kind of new to oracle query tuning.
Platform: Windows 2003 server
Oracle version: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Any help is much appreciated!