Description: Hints are used to give specific information that we know about our data and application, to Oracle. This further improves the performance of our system. There can be instances where the default optimizer may not be efficient for a certain SQL statements. We can specify HINTS with the SQL statements, to improve the efficiency of those SQL statements.
Language: Highlight Mode: HTML4STRICT Last Modified: March 07th, 2009
SELECT /*+ FIRST_ROWS(10) */ table_name
WHERE owner = 'SYS'
AND table_name LIKE '%$'
ORDER BY 1;
/*+ RULE */
Disables the use of the optimizer. This hint is not supported and should not be used.
/*+ APPEND */
When you use the APPEND hint for INSERT, data is simply appended to a table above the High Water Mark.
No UNDO is created.
/*+ NOAPPEND */
Instructs the optimizer to use conventional INSERT by disabling parallel mode for the duration of the INSERT statement.
/*+ CURSOR_SHARING_EXACT */
Oracle can replace literals in SQL statements with bind variables, when it is safe to do so. This replacement is controlled with the CURSOR_SHARING initialization parameter. The CURSOR_SHARING_EXACT hint instructs the optimizer to switch this behavior off. In other words, Oracle executes the SQL statement without any attempt to replace literals with bind variables.
/*+ DRIVING_SITE([@queryblock] <tablespec>) */
Forces query execution to be done at a user selected site rather than at a site selected by the database. This hint is useful if you are using distributed query optimization.
/*+ MONITOR */
Forces real-time SQL monitoring for the query, even if the statement is not long running. This hint is valid only when the parameter CONTROL_MANAGEMENT_PACK_ACCESS is set to DIAGNOSTIC+TUNING.
/*+ NO_MONITOR */
Disables real-time SQL monitoring for the query, even if the query is long running.
/*+ PX_JOIN_FILTER(<tablespec>) */
Forces the optimizer to use parallel join bitmap filtering.
/*+ NO_PX_JOIN_FILTER(<tablespec>) */
Prevents the optimizer from using parallel join bitmap filtering.
/*+ RESULT_CACHE */
Instructs the database to cache the results of the current query.
/*+ NO_RESULT_CACHE */
The NO_RESULT_CACHE hint disables caching for the current query.
SQL University.net courses meet the most demanding needs of the business world for advanced education
in a cost-effective manner. SQL University.net courses are available immediately for IT professionals
and can be taken without disruption of your workplace schedule or processes.
Compared to traditional travel-based training, SQL University.net saves time and valuable corporate
resources, allowing companies to do more with less. That's our mission, and that's what we deliver.