Here’s a little puzzle about execution plans. How many (significantly) different strategies are there for the following SQL – and how many of them will the Cost Based Optimizer be unable to find without being explicitly hinted ?
select t1.v1 from t1, t3 where t1.n2 = 100 and t3.n1 = t1.n1 and t3.n2 = 100 and exists ( select t2.id from t2 where t2.n1 = 15 and t2.id = t1.id ) and exists ( select t4.id from t4 where t4.n1 = 15 and t4.id = t3.id ) ;
I’m not expecting answers, by the way, it’s just a little query for you to play with. I’ll be making further comments about it later.
Updated 25th Jan: I’ve added a simple predicate to both t1 and t3 to make give some of the options a little more visibility, as some of the potential execution strategies are a little unbelievable if you don’t have any filtering on these two tables.
For the next chapter in the treatment of the query, go to this page.