Query Efficiency

nader · Mar 6, 2001

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 · Mar 6, 2001

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 · Mar 7, 2001

makes sense, thanks

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Query Efficiency

nader

Programmer

Kenrae

Programmer

nader

Programmer

Similar threads

Part and Inventory Search

Sponsor