Oracle Hints

Extended Statistics


Any statistics you collect for expressions and column groups are called "extended statistics". Expression statistics on functions enable the optimizer to obtain a vastly more accurate selectivity value for predicates that involve expressions.

You can issue the following query to find details about expression statistics on a table's columns:

SQL> select extension_name, extension
     from user_stat_extensions
     where table_name=<Table_name>;

   
ORACLE HINTS


Examples of index hints are the INDEX_FFS for an index fast full scan, and the INDEX SS for an index skip scan. The INDEX_SS hint is appropriate if you have a table with composite, multi-column indexes. It is possible to have Oracle use the index, even if the query does not use the leading column of the index. At times, the INDEX_SS hint can be beneficial to retrieve data fast, even if the column noted in the WHERE clause isn't the leading column of an index.

Sort merge joins, like hash joins, are used to join a large volume of data. Unlike the hash join, the sort merge join is used when the join condition between the tables is not an equijoin.

A hash join, which can't begin returning rows until the join is done, but which might execute in less overall time than a nested loops join.


Hints to instruct the optimizer to choose a join method are sometimes necessary because of several factors:

*    Status of statistics on the table
*    Size of the PGA
*    If the data is sorted at join time
*    An unexplained choice of the optimizer

APPEND HINT:- The APPEND hint works within statements performing DML insert operations from another table, that is, using a subquery from within an INSERT SQL statement. This is appropriate for when you need to copy a large volume of rows between tables. By bypassing the Oracle database buffer cache blocks and appending the data directly to the segment above the high water mark, it saves significant overhead. This is a very popular method for inserting rows into a table very quickly.

The APPEND and APPEND_VALUES hints, when used, automatically convert a conventional insert operation into a direct-path insert operation. In addition, if you are using parallel operations during an insert, the default mode of operation is to use the direct-path mode. If you want to bypass performing direct-path operations, you can use the NOAPPEND hint.

If two append operations are inserting rows at the same time, performance will suffer as there is a rick of contention, as since the insert append operation appends the data above the high water mark for a segment, only one operation should be done at one time. However, if you have partitioned objects, you can still run several concurrent append operations, as long as each insert operates on separate partitions for a given table.

DRIVING SITE:- Make use of the Driving Site Hint in the case of a Query involving a Distributed Database.Specifying the remote site as the driver is most appropriate if the volume on the remote site is large, or if you are querying many tables on the remote site.

The biggest factors in determining which site should be the driving site are as follows:

*    How many tables are in the distributed query?
*    How many databases are involved in the distributed query?
*    Which database contains the most tables involved in the query?
*    Which database contains the greatest volume of data?

If there is no driving site hint specified in the Query involving a distributed transaction, the local site is the driving site.

PARALLEL INDEX:-Specify the parallel_index hint to control parallel access to indexes. You can generally access an index in parallel only when the index is a locally partitioned index. In that case, you can apply the parallel_index hint. If you omit the Degree of Parallelism from the hint, the optimizer determines the Degree of Parallelism based on the initialization parameter settings.If automatic Degree of Parallelism is enabled and configured properly (PARALLEL_DEGREE_POLICY=AUTO), THEN THE PARALLELISM THAT YOU SET ON OBJECTS IS IGNORED, and the optimizer chooses the degree of parallelism to be used.