\/*+ hint *\/<\/p>\n
\/*+ hint(argument) *\/<\/p>\n
\/*+ hint(argument-1 argument-2) *\/<\/p>\n
All hints except \/*+ rule *\/ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully<\/em> hinted.<\/p>\n There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:<\/p>\n select \/*+ index(scott.emp ix_emp) *\/ from scott.emp emp_alias<\/strong><\/p>\n better:<\/p>\n select \/*+ index(emp_alias<\/strong> ix_emp) *\/ … from scott.emp emp_alias<\/strong><\/p>\n Why using hints<\/strong><\/p>\n Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help.<\/p>\n It must also be noted, that Oracle allows to lock<\/em> the statistics when they look ideal which should make the hints meaningless again.<\/p>\n Hint categories<\/strong><\/p>\n Hints can be categorized as follows:<\/p>\n Documented Hints<\/strong><\/p>\n Hints for Optimization Approaches and Goals<\/strong><\/p>\n See also the following initialization parameters: optimizer_mode<\/span>, optimizer_max_permutations<\/span>, optimizer_index_cost_adj<\/span>, optimizer_index_caching<\/span> and<\/p>\n Hints for Access Paths<\/strong><\/p>\n Hints for Query Transformations<\/strong><\/p>\n Hints for Join Operations<\/strong><\/p>\n
\nIt is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer<\/span> that promises to optimize a query’s execution plan<\/span>. When this optimizer is really doing a good job, no hints should be required at all.<\/p>\n\n
\n
\nOne of the hints that ‘invokes’ the Cost based optimizer<\/span>
\nALL_ROWS is usually used for batch processing<\/em> or data warehousing<\/em> systems.<\/li>\n
\nOne of the hints that ‘invokes’ the Cost based optimizer<\/span>
\nFIRST_ROWS is usually used for OLTP<\/em> systems.<\/li>\n
\nOne of the hints that ‘invokes’ the Cost based optimizer<\/span>
\nThis hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered<\/span>.<\/li>\n
\nThe RULE hint should be considered deprecated as it is dropped from Oracle9i2.<\/li>\n<\/ul>\n\n
\nPerforms a nested loop<\/span> by the cluster index of one of the tables.<\/li>\n
\nPerforms full table scan<\/span>.<\/li>\n
\nHashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.<\/li>\n
\nRetrieves the row by rowid<\/span><\/li>\n
\nSpecifying that index index_name should be used on table tab_name: \/*+ index (tab_name index_name) *\/
\nSpecifying that the index should be used the the CBO<\/span> thinks is most suitable. (Not always a good choice).
\nStarting with Oracle 10g, the index hint can be described: \/*+ index(my_tab my_tab(col_1, col_2)) *\/. Using the index on my_tab that starts with the columns col_1 and col_2.<\/li>\n
\nThe AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes<\/li>\n<\/ul>\n\n
\nThe FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.<\/li>\n