Global hints: Optimizer stats hint

Optimizer stats hints under the global hint type are used to instruct the optimizer to use specific statistics or methods during query processing.

Common optimizer stats hints include GATHER_OPTIMIZER_STATISTICS and NO_GATHER_OPTIMIZER_STATISTICS, which affect how the optimizer collects statistics.

GATHER_OPTIMIZER_STATISTICS hint

The GATHER_OPTIMIZER_STATISTICS hint specifies to collect statistics online during an INSERT INTO...SELECT statement. For more information, see Online statistics collection. The inverse operation of this hint is NO_GATHER_OPTIMIZER_STATISTICS.

Syntax

/*+ GATHER_OPTIMIZER_STATISTICS */

Example

-- Instructs the database optimizer to collect statistics required by the optimizer after data from table t2 is inserted into table t1.
INSERT /*+ GATHER_OPTIMIZER_STATISTICS */ INTO t1 SELECT * FROM t2;

NO_GATHER_OPTIMIZER_STATISTICS hint

The NO_GATHER_OPTIMIZER_STATISTICS hint specifies to disable online statistics collection. This hint is the inverse operation of GATHER_OPTIMIZER_STATISTICS. For more information, see Online statistics collection. The inverse operation of this hint is GATHER_OPTIMIZER_STATISTICS.

Syntax

/*+ NO_GATHER_OPTIMIZER_STATISTICS */

Example

-- Instructs the database optimizer to not collect statistics required by the optimizer after data from table t2 is inserted into table t1.
INSERT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ INTO t1 SELECT * FROM t2;