Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!
I am not a coder but I have come across the phenomenon that some versions of some databases will do a tablescan when you are not expecting it, so you are earning your money by knowing when that will happen.
I would vote for the subquery option because it is the one that I would write, but I can accept that in the real world that could trigger a tablescan when it shouldn't. The foreign key should be indexed in both tables otherwise the DBA should be shot.
IMO
EDIT - even if all the fields are indexed that is not always enough to stop a tablescan.
I am not a coder but I have come across the phenomenon that some versions of some databases will do a tablescan when you are not expecting it, so you are earning your money by knowing when that will happen.
I would vote for the subquery option because it is the one that I would write, but I can accept that in the real world that could trigger a tablescan when it shouldn't. The foreign key should be indexed in both tables otherwise the DBA should be shot.
IMO
EDIT - even if all the fields are indexed that is not always enough to stop a tablescan.
What ARE you on about? Full Table Scans, Fast Full Scans, etc. are not associated, in particular, with either Joins or Subqueries. The CBO decides HOW it will gather the data. The only way to be sure is to run it and see. Then tweaks-tests-tweaks-tests, etc. Then you know. No need to go through it all again, unless the stats become significantly out-of-step with reality, or an index is added, a table is partitioned, etc., etc.
Incidentally, a Full Table Scan can be the right thing to do.
There are books on all this, as well as bizillions of lovely web sites.
That old copy of "Oracle 7: Ye Goode Guyde To The Magicks and Mysteryes" is a bit redundant.
Comment