Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: HINTS Jump to:  
Category: >> Oracle PL/SQL >> HINTS Bookmark and Share

<< lastnext >>

Snippet Name: HINTS

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.

Also see:
» Oracle SQL Hints

Comment: (none)

Language:
Highlight Mode: HTML4STRICT
Last Modified: March 07th, 2009

SELECT /*+ FIRST_ROWS(10) */ table_name
FROM dba_tables
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.
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


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.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 107 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?