Auto Create Statistics in Oracle 12c.

Exploring Oracle 12c .... 

Prior to Oracle 12c , we usually generate the table statistics  to give it to the Optimizer for generating cost effective plans.
 
In Oracle 12c  , this is done automatically.

Please see the example below .

Oracle 12c
===========

SQL> col product format a50
SQL> col version  format a15

SQL> Select * from product_component_version ;

PRODUCT                                            VERSION         STATUS
-------------------------------------------------- --------------- ---------------------------
NLSRTL                                             12.1.0.1.0      Production
Oracle Database 12c Enterprise Edition             12.1.0.1.0      64bit Production
PL/SQL                                             12.1.0.1.0      Production
TNS for 64-bit Windows:                            12.1.0.1.0      Production

SQL>

SQL> Create table t as Select * from all_objects;

Table created.


SQL> Create table t as Select * from all_objects;

Table created.

SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE   NUM_ROWS LAST_ANAL
----- ---------- ---------
T          73760 16-JUL-13


SQL> set pagesize 90
SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     ORACLE_MAINTAINED               2           1 NONE            16-JUL-13
T     EDITIONABLE                     2           1 NONE            16-JUL-13
T     SHARING                         3           1 NONE            16-JUL-13
T     EDITION_NAME                    0           0 NONE            16-JUL-13
T     NAMESPACE                       7           1 NONE            16-JUL-13
T     SECONDARY                       1           1 NONE            16-JUL-13
T     GENERATED                       2           1 NONE            16-JUL-13
T     TEMPORARY                       2           1 NONE            16-JUL-13
T     STATUS                          1           1 NONE            16-JUL-13
T     TIMESTAMP                    1133           1 NONE            16-JUL-13
T     LAST_DDL_TIME                1037           1 NONE            16-JUL-13
T     CREATED                      1061           1 NONE            16-JUL-13
T     OBJECT_TYPE                    23           1 NONE            16-JUL-13
T     DATA_OBJECT_ID                 86           1 NONE            16-JUL-13
T     OBJECT_ID                   73760           1 NONE            16-JUL-13
T     SUBOBJECT_NAME                  0           0 NONE            16-JUL-13
T     OBJECT_NAME                 40132           1 NONE            16-JUL-13
T     OWNER                          17           1 NONE            16-JUL-13

18 rows selected.




Oracle 11g
==========

SQL> set linesize 1000
SQL> set pagesize 90
SQL> col product format a50
SQL> col version  format a15
SQL> Select * from product_component_version ;

PRODUCT                                            VERSION         STATUS
-------------------------------------------------- --------------- ------------------
NLSRTL                                             11.2.0.3.0      Production
Oracle Database 11g Enterprise Edition             11.2.0.3.0      64bit Production
PL/SQL                                             11.2.0.3.0      Production
TNS for 64-bit Windows:                            11.2.0.3.0      Production

SQL> Create table t as Select * from all_objects;

Table created.


SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANAL
------------------------------ ---------- ---------
T

SQL> col table_name format a5
SQL> col column_name format a20
SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     OWNER                                         NONE
T     OBJECT_NAME                                   NONE
T     SUBOBJECT_NAME                                NONE
T     OBJECT_ID                                     NONE
T     DATA_OBJECT_ID                                NONE
T     OBJECT_TYPE                                   NONE
T     CREATED                                       NONE
T     LAST_DDL_TIME                                 NONE
T     TIMESTAMP                                     NONE
T     STATUS                                        NONE
T     TEMPORARY                                     NONE
T     GENERATED                                     NONE
T     SECONDARY                                     NONE
T     NAMESPACE                                     NONE
T     EDITION_NAME                                  NONE

15 rows selected.

SQL> exec dbms_stats.gather_table_stats ( user , 'T' );

PL/SQL procedure successfully completed.

SQL> Select table_name , num_rows , last_analyzed from user_tab_statistics where table_name ='T';

TABLE   NUM_ROWS LAST_ANAL
----- ---------- ---------
T          56358 16-JUL-13

SQL> Select table_name , column_name , num_distinct ,    num_buckets ,  histogram , last_analyzed
  2  from user_tab_columns where table_name  ='T';

TABLE COLUMN_NAME          NUM_DISTINCT NUM_BUCKETS HISTOGRAM       LAST_ANAL
----- -------------------- ------------ ----------- --------------- ---------
T     OWNER                          16           1 NONE            16-JUL-13
T     OBJECT_NAME                 31094           1 NONE            16-JUL-13
T     SUBOBJECT_NAME                 12           1 NONE            16-JUL-13
T     OBJECT_ID                   56358           1 NONE            16-JUL-13
T     DATA_OBJECT_ID                 92           1 NONE            16-JUL-13
T     OBJECT_TYPE                    29           1 NONE            16-JUL-13
T     CREATED                      1122           1 NONE            16-JUL-13
T     LAST_DDL_TIME                1459           1 NONE            16-JUL-13
T     TIMESTAMP                    1429           1 NONE            16-JUL-13
T     STATUS                          2           1 NONE            16-JUL-13
T     TEMPORARY                       2           1 NONE            16-JUL-13
T     GENERATED                       2           1 NONE            16-JUL-13
T     SECONDARY                       1           1 NONE            16-JUL-13
T     NAMESPACE                      10           1 NONE            16-JUL-13
T     EDITION_NAME                    0           0 NONE            16-JUL-13

15 rows selected.

Oracle 12c Installed ... New "fetch" Feature in Oracle 12c

Oracle released its latest version of its database last week .
Installed it this week and started playing with that.

DBCA
====

While creating database via dbca , it has two buttons "Alert Log" and "Activity Log" . By clicking on "Alert Log" , a Java popup window displays the contents of the (legacy ) alert log.

SQL Feature
===========

Started with the "tiny" new feature in SQL . I believe , it will be useful in pagination queries.


SQL> select empno , ename , hiredate from emp order by hiredate;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7566 JONES      02-APR-81
      7698 BLAKE      01-MAY-81
      7782 CLARK      09-JUN-81
      7844 TURNER     08-SEP-81
      7654 MARTIN     28-SEP-81
      7839 KING       17-NOV-81
      7900 JAMES      03-DEC-81
      7902 FORD       03-DEC-81
      7934 MILLER     23-JAN-82
      7788 SCOTT      19-APR-87
      7876 ADAMS      23-MAY-87

14 rows selected.


SQL> select empno , ename , hiredate from emp order by hiredate  fetch first 50 percent rows only;

     EMPNO ENAME      HIREDATE
---------- ---------- ---------
      7369 SMITH      17-DEC-80
      7499 ALLEN      20-FEB-81
      7521 WARD       22-FEB-81
      7566 JONES      02-APR-81
      7698 BLAKE      01-MAY-81
      7782 CLARK      09-JUN-81
      7844 TURNER     08-SEP-81

7 rows selected.

As it can be seen from the example , I can display only the part of the resultset based on %.

Will continue to explore and post my findings as I come across.

Comments Welcome.