Hint philosophy

Again from Expert Oracle SQL: Optimization, Deployment, and Statistics

You should be asking yourself these sorts of questions:
• Do I know the reason why I need to hint this code? Why has the CBO picked a different plan to
the one I think is best?
• Do I know that hinting the code will actually make a positive difference, or am I making a
potentially incorrect assumption?
• Are there alternative approaches? Are these alternative approaches better or worse than the
use of hints?
• Do I understand what the hints that I plan to use do?
• Are the hints an appropriate and complete set?
• Will my colleagues understand what I have done and why?

Analytic sorts

If you take a 10032 trace you will see that the ROW_NUMBER analytic function uses what is known as a version 1 sort, which is a memory-intensive, insertion-based sort, whereas the FIRST_VALUE function uses a version 2 sort, which is apparently a fancy combination of a radix sort and a quick sort1 that requires much less memory and fewer comparisons.

From Expert Oracle SQL: Optimization, Deployment, and Statistics