Optimizer Stats Hint under Global Hints

Optimizer Stats Hints under Global Hints are 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 reverse operation is NO_GATHER_OPTIMIZER_STATISTICS.

Syntax

/*+ GATHER_OPTIMIZER_STATISTICS */

Example

-- Indicates the database optimizer to collect the optimizer statistics required after inserting data from the t2 table into the t1 table.
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 reverse operation is GATHER_OPTIMIZER_STATISTICS.

Syntax

/*+ NO_GATHER_OPTIMIZER_STATISTICS */

Example

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