Query Efficiency
Query Efficiency
(OP)
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!
_________________________________
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!
RE: Query Efficiency
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)
RE: Query Efficiency