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.
Hint Name |
Description |
|---|---|
GATHER_OPTIMIZER_STATISTICS |
Enables the collection of statistics while inserting data. Its opposite is NO_GATHER_OPTIMIZER_STATISTICS. |
NO_GATHER_OPTIMIZER_STATISTICS |
Disables the collection of statistics while inserting data. Its opposite is GATHER_OPTIMIZER_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;