Optimizer Stats Hint specifies the statistics collection method for the optimizer.
Common Optimizer Stats Hints include GATHER_OPTIMIZER_STATISTICS Hint and NO_GATHER_OPTIMIZER_STATISTICS Hint, which specify the statistics collection method for the optimizer.
| Hint Name | Description |
|---|---|
GATHER_OPTIMIZER_STATISTICS |
Enables statistics collection during data insertion. Its reverse operation is NO_GATHER_OPTIMIZER_STATISTICS. |
NO_GATHER_OPTIMIZER_STATISTICS |
Disables statistics collection during data insertion. Its reverse operation is GATHER_OPTIMIZER_STATISTICS. |
GATHER_OPTIMIZER_STATISTICS Hint
The GATHER_OPTIMIZER_STATISTICS Hint specifies that statistics are collected 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 */
Examples
-- Indicates the database optimizer to collect statistics required by the optimizer after the data in the t2 table is inserted 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 that statistics are not collected online during the execution of INSERT INTO...SELECT. It is the reverse 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
-- Indicates the database optimizer not to collect statistics required by the optimizer after the data in the t2 table is inserted into the t1 table.
INSERT /*+ NO_GATHER_OPTIMIZER_STATISTICS */ INTO t1 SELECT * FROM t2;