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!
Talk To Other Members
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