Pickleball spielen

Less famous 12c New Feature: If you do Create Table As Select (CTAS) or a direct load into an empty table, we will automatically collect optimizer statistics:

SQL> select banner from v$version;

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

SQL> create table nu as select * from sales where rownum<=100000; 
Table created.
 
SQL> select num_rows,blocks from user_tables where table_name='NU';

  NUM_ROWS     BLOCKS
---------- ----------
    100000        695

SQL> truncate table nu;
Table truncated.

SQL> insert /*+ append */ into nu  select * from sales where rownum<=200000; 
200000 rows created. 

SQL> commit;
Commit complete.

SQL> select num_rows,blocks from user_tables where table_name='NU';

  NUM_ROWS     BLOCKS
---------- ----------
    200000       1379

If the table is not empty, the statistics are not updated:

SQL> insert /*+ append */ into nu  select * from sales where rownum<=100000; 
100000 rows created. 

SQL> commit;
Commit complete.

SQL> select num_rows,blocks from user_tables where table_name='NU';

  NUM_ROWS     BLOCKS
---------- ----------
    200000       1379

You see that the previous statistics have not been changed here.
If it is a conventional insert:

SQL> truncate table nu; 
Table truncated. 
SQL> insert into nu select * from sales where rownum<=100000; 
100000 rows created. 
SQL> commit; 
Commit complete. 
SQL> select num_rows,blocks from user_tables where table_name='NU'; 
NUM_ROWS BLOCKS 
---------- ---------- 
    200000       1379

Again, the statistics have not been maintained automatically either. Can be done manually still, of course:

SQL> exec dbms_stats.gather_table_stats('ADAM','NU')

PL/SQL procedure successfully completed.

SQL> select num_rows,blocks from user_tables where table_name='NU';

  NUM_ROWS     BLOCKS
---------- ----------
    100000        748

So knowing that, you may be able to skip some manual statistics collections that you may have done in the past after CTAS or bulk loads 🙂