Optimizer Stats Hint under the Global Hint Type

Optimizer Stats Hint under the Global Hint Type is used to tell the optimizer to use 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 the statistics required by the optimizer 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;