Query Efficiency

[nader]

Consider the following 2 querries:
_________________________________
select * from List_A A,List_B B
where A.ID = 100
and B.ID = A.ID
_________________________________

AND
_________________________________
select * from List_A A,List_B B
where A.ID = 100
and B.ID = 100
_________________________________

Both List_A and List_B tables have an index
on the ID column, and both tables are very large!
The environment is UDB.

Which query is more efficient? Please give valid reasons!

 

[Kenrae]

Normally I'd say the second one is more efficient, but nearly every dbms will transform the first one into this query: "where A.id=100 and B.id=A.id and B.id=100"

The second one is better because on table List_B you only have one access to the index (when doing B.id=100). After filtering both tables the dbms will have two temp file with the info, then it will do the join between the temp files.
If you don't filter table List_B, when doing the join the dbms will have one access to List_B index for every List_A row! (well, this isn't exact, but you'll have more than one access)

 

[nader]

makes sense, thanks