The Optimizer Stats Hint under the global hint category instructs the optimizer to use specific statistics or methods during query processing.
Common optimizer stats hints include the GATHER_OPTIMIZER_STATISTICS Hint and the NO_GATHER_OPTIMIZER_STATISTICS Hint, which affect how the optimizer collects statistics.
Hint name |
Explanation |
|---|---|
GATHER_OPTIMIZER_STATISTICS |
Enables statistics collection while inserting data. Its inverse operation is NO_GATHER_OPTIMIZER_STATISTICS. |
NO_GATHER_OPTIMIZER_STATISTICS |
Collects statistics while prohibiting data insertion. Its inverse operation is GATHER_OPTIMIZER_STATISTICS. |
GATHER_OPTIMIZER_STATISTICS Hint
The GATHER_OPTIMIZER_STATISTICS hint specifies to collect statistics online when executing an INSERT INTO...SELECT statement. For more information, see Online statistics collection. Its counterpart is the NO_GATHER_OPTIMIZER_STATISTICS hint.
Syntax
/*+ GATHER_OPTIMIZER_STATISTICS */
Examples
--Used to instruct the database optimizer to collect the statistics required 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 prohibit online statistics collection. It has the opposite effect 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 */
Examples
-- This hint instructs 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;