Optimizer Stats Hint

Optimizer Stats Hint specifies that the optimizer uses specific statistics or methods during query processing.

Common Optimizer Stats Hints include GATHER_OPTIMIZER_STATISTICS Hint and NO_GATHER_OPTIMIZER_STATISTICS Hint, which affect how the optimizer collects statistics.

GATHER_OPTIMIZER_STATISTICS Hint

The GATHER_OPTIMIZER_STATISTICS Hint specifies to collect statistics online during the execution of INSERT INTO...SELECT. For more information, see Online statistics collection. Its opposite is NO_GATHER_OPTIMIZER_STATISTICS.

Syntax

/*+ GATHER_OPTIMIZER_STATISTICS */

Example

-- Indicates to the database optimizer to collect optimizer statistics after inserting data from table t2 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. It is the opposite of the GATHER_OPTIMIZER_STATISTICS Hint. For more information, see Online statistics collection. Its opposite is GATHER_OPTIMIZER_STATISTICS.

Syntax

/*+ NO_GATHER_OPTIMIZER_STATISTICS */

Example

-- Indicates to the database optimizer not to collect optimizer statistics after inserting data from table t2 into table t1.
INSERT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ INTO t1 SELECT * FROM t2;