If Table B is driving table
For A in 1 to 10 LOOP Pick matching values from Table B End Loop --Total loop iterations 10
For B in 1 to 20 LOOP Pick matching values from Table B End Loop --Total loop iterations 20
user601798 wrote:Now that's a healthy attitude. (And I really do mean that.)
Ok, time to battle it out ... put your votes in everyone ... bigger or smaller table as driving table (given all other things being equal)...
Just as an addendum to this thread --consider marking this tread as ANSWERED & starting new thread with totally new & different topic
user601798 wrote:Your 'where' clause in the query should be the deciding factor.
OS: Red Hat Linux
I have gotten two conflicting answers while reading books by Don Burleson and Dan Hotka. It has to do with the CBO and nested-joins:
One says the CBO will choose the 'smaller' table as the driving table, the other states that the 'larger' table will be the driving table. And both stick by this philosophy as the preferred goal of any SQL Tuning -- that is, one states that the 'smaller' table should be the driving table. The other says the 'larger' table should be the driving table.
I had always thought that the 'smaller' table should be the driving table. That in a nested loop the driving will not likely use an index even. Who is correct? (I am not going to say who said what, btw). :-)
But I got to let one of them know they got a 'typo' ... :-)
user601798 wrote:It is not possible for +"*all* other things to be equal"+. (my emphasis).
It is an over-simplistic scenario but, as I mentioned, if all other things are 'equal' -- which would include 'access time/work', then I think the small table as the driving table has the advantage.