What is ORACLE HINTS , A Brief Overview.

Get real time news update from your favorite websites.
Don't miss any news about your favorite topic.
Personalize your app

Check out NTyles.

Get it on....

Apart from lucenening and searching. Lets move on to some real time performance issues when you deal with large database with millions and trillions of rows with hundreds and thousands of tables. Oracle's sql processing executes query using the best plan generated by optimizer which has the low cost and needs very less resources executing that query. All the cost association for a query is calculated by Optimizer. In oracle there are two types of Optimizer :

• RBO(Rule Based Optimizer)
• CBO(Cost Based Optimizer)

Here I will not talk about RBO because Oracle is gradually deprecating the use of RBO in its future releases. Generally, all the costing calculation is done by CBO and oracle prefers CBO to do this and by default, its CBO. Oracle's CBO's main goal is the BEST THROUGHPUT, that means to execute all the rows using limited resources available. This is really good if you are querying in the small table. The response time will be good and even best. But think of the condition when an online user is trying to access the few information using simple query and at the back end of the website gives emphasis on BEST THROUGHPUT then surely this really is going to depress the user because he/she is not interested in the overall results and also user don't have patience to wait for 1-2 minutes online. So, the BEST THROUGHPUT really has a big disadvantage when the database is big and when multiple joins etc... are to be done in such database. For the online application, user's will really be happy if they see some results quickly as possible as soon as they hit the 'SHOW RESULTS' button. In order to achieve this we can tell the optimizer to change the execution plan for the best response time. Yes, BEST FAST RESPONSE TIME is another goal that a CBO can perform.But by default it's BEST THROUGHPUT. Now, how to make the optimizer choose the best plan to optimize itself for best fast response time? The answer is.......use oracle's HINTS.

For example if you have ten millions of rows in a table , instead of using this :

SELECT * FROM BIG_TABLE; 

and wait for 10-15 seconds simply use this:

SELECT /*+ FIRST_ROWS(20) */ * FROM BIG_TABLE;

to get few rows in your console as soon  as possible after hitting enter.

The bold section that looks like a comment is a hint embedded in the query, that makes the optimzer to change its execution plan for best fast response time.

Refer here for full syntax of Oracle Hints

.
Hints in Oracle are really very useful in improving performance in large database and should be used sparingly.
If you are not satisfied then don't hesitate to play my presentation:
ORACLE HINTS