Starting with Oracle 12c, the default behavior of database is to gathers statistics during bulk loads like CTAS or INSERT SELECT.  However for some reason , if you want to go back to pre-12c behavior , you can do so with a new hint NO_GATHER_OPTIMIZER_STATISTICS hint.

See illustration of examples comparing Oracle11g and Oracle12c

Oracle 11g

SQL> SELECT * FROM V$VERSION;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
PL/SQL Release 11.2.0.2.0 – Production
CORE    11.2.0.2.0      Production
TNS for Linux Version 11.2.0.2.0 – Production
NLSRTL Version 11.2.0.2.0 – Production

SQL> CREATE TABLE MY_OBJECTS_NOSTATS AS SELECT * FROM MY_OBJECTS;

Table created.

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Database is 11g, Hint is ignored

Table created.

SQL>  CREATE TABLE MY_OBJECTS_NOSTATS_HINT  AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Database is 11g, Hint is ignored

Table created.

SQL> SELECT TABLE_NAME, LAST_ANALYZED , NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME LIKE ‘MY_OBJECTS%’; ==> Database is 11g, Stats are not gathered for all tables

TABLE_NAME                     LAST_ANAL   NUM_ROWS     BLOCKS
—————————— ——— ———- ———-
MY_OBJECTS
MY_OBJECTS_STATS
MY_OBJECTS_NOSTATS
MY_OBJECTS_NOSTATS_HINT

Oracle12c

SQL> SELECT BANNER FROM V$VERSION;

BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
PL/SQL Release 12.1.0.1.0 – Production
CORE    12.1.0.1.0      Production
TNS for Linux: Version 12.1.0.1.0 – Production
NLSRTL Version 12.1.0.1.0 – Production

SQL> CREATE TABLE MY_OBJECTS_NOSTATS AS SELECT * FROM MY_OBJECTS;

Table created.

SQL> CREATE TABLE MY_OBJECTS_STATS AS SELECT /*+ GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Default behavior ;hint is of no help here.

Table created.

SQL> CREATE TABLE MY_OBJECTS_NOSTATS_HINT  AS SELECT /*+ NO_GATHER_OPTIMIZER_STATISTICS */  * FROM MY_OBJECTS; ==> Hint instructing Oracle not to collect Stats

Table created.

SQL> SET LINESIZE 200
SQL> COLUMN TABLE_NAME FOR A40
SQL> SELECT TABLE_NAME, LAST_ANALYZED , NUM_ROWS, BLOCKS FROM DBA_TABLES WHERE TABLE_NAME LIKE ‘MY_OBJECTS%’; ==> Stats are gathered for all tables except table MY_OBJECTS_NOSTATS_HINT which was created with NO_GATHER_OPTIMIZER_STATISTICS hint

TABLE_NAME                               LAST_ANAL   NUM_ROWS     BLOCKS
—————————————————————————————————————
MY_OBJECTS                                26-AUG-13      90908       1561
MY_OBJECTS_NOSTATS                26-AUG-13      90908       1561
MY_OBJECTS_STATS                    26-AUG-13      90908       1561
MY_OBJECTS_NOSTATS_HINT

4 rows selected.

SQL>

Tagged: gather stats during bulk load, GATHER_OPTIMIZER_STATISTICS, NO_GATHER_OPTIMIZER_STATISTICS