Thursday, October 04, 2012

OOW-2012: Oracle 12c New Features

Blogging right from Tom's session "database new features". Here are the new features as they are discussed:

1. Identity Columns

 Remember I blogged on this topic earlier.

2. Default on null

A default value is inserted into the column When it's null.

3. Improved defaults - metadata only defaults

Now add new default columns to a table on the fly. 12c does that for you.

4. Increased size limit for Varchar2 and NVarchar2

 The column types can be upto 32k.

5. Row Pattern Matching

Efficient SQL functionality for reporting row patterns like identifying V and W patterns for your stock

6. Partitioning Improvements

Online partition movement without using DBMS_REDEFINITION. Efficient global index management during DROP and TRUNCATE partition operation.


7. Hybrid Histograms

Jonathan Lewis spoke on this topic. I think more information on this is available on his blog.

8. Session private statistics for GTTs

Table and index statistics are held private for each session.

9. Temporary UNDO
Undo for temporary tables can now be managed in TEMP. Reduces the amount of undo generated in undo tablespace and redo generation.

10. Pluggable databases

 Read Roels blog for this one. He has a good blog post on this

 http://roelhartman.blogspot.com/
at

3 comments:

  1. Iggy Fernandez2:40 PM, October 07, 2012

    Hi, Asif,

    RE: "Default on null: A default value is inserted into the column When it's null."

    How is this different from the "DEFAULT expr NOT NULL" clause of CREATE TABLE and ALTER TABLE?

    RE: "Improved defaults - metadata only defaults: Now add new default columns to a table on the fly. 12c does that for you."

    How is this different from the ADD clause of ALTER TABLE?

    Iggy

    ReplyDelete
  2. Asif Momen12:45 PM, October 08, 2012

    Hi Iggy,

    Here are the answers:

    A-1:

    I am trying to recollect my thoughts on this. Give me sometime to dig more details for this.

    A-2:
    Typically when you add a new column with a default value to an existing table, Oracle visits each and every record and adds the new column with the default value. This operation is time & resource consuming when performed on a huge table. Starting 12c, this operation will be a "metadata only defaults" operation. Meaning, the alter statement will be non-resource intensive and completes very fast. Only the metadata of the column is updated to the default-value but not each and every row.

    Asif

    ReplyDelete
  3. Joaquin Gonzalez12:38 PM, October 09, 2012

    Hi Asif,

    Adding Columns with a Default Value can be a "metadata only defualts" operation in 11g yet.

    http://www.oracle.com/technetwork/articles/sql/11g-schemamanagement-089869.html

    So, maybe they improved, in 12c, when this can be done.

    Joaquin

    ReplyDelete