Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Hints
user72_at_go2.pl (Marek Babski) wrote i
> I am preparing myself to talk for a new job. I have a question about SQL hints.
Hints are mostly bad. They force a specific execution plan. They result in rigid untunable SQL code. SQL code that cannot be scaled as the platform and data volumes changes.
If the Oracle CBO does not give you the correct execution plan, there are reasons for that, and ways to solve that problem, without resorting to hints.
The critical thing - ensuring that the CBO has accurate and updated stats to make an intelligent decision.
Next. Considering execution plan stability when specific execution plans are required for whatever reason. Often a consideration when as an ISV doing development and not knowing if the above "critical thing" is done at all in a customer's production environment.
That is the type of answer I would expect from an interview.
> 1) Can anyone explain me in example what use_hash (emp, dept) is
A hash join.
> 2) what is the difference in use_hash (emp, dept) and use_hash (dept, emp)
> - please explain in example
> 3) use_nl (nested loop - what it is in example)
A nested loop join.
Both these are explained in the Oracle documentation. I also do not think myself that this is an important thing to know for an interview.
What is critical IMO is understanding concepts. Like what is bind variables? Why use bind variables? What is multi version consistency? When does a writer block (e.g. UPDATE transaction) block a reader (SELECT)? Exact technical details can easily be obtained from manuals (I still use the Oracle Reference and SQL and other manuals daily). Concepts are something that much more fundemental. Without that, the technical details are meaningless as there is no framework in which you can apply the technical stuff correctly.
Basically - being able to quote verbatim the syntax of an UPDATE SQL statement does not by any stretch of the imagination mean that the person know *HOW* to use the UPDATE statement correctly and efficiently when developing production software. So I tend not give a rat's ass if a developer has to look up UPDATE syntax in the SQL manual - but if he use that UPDATE and COMMIT inside a cursor loop, doing fetches across commits, I will kick his ass. Hard. :-)
-- BillyReceived on Thu Mar 25 2004 - 02:31:30 CST