HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
New features of Oracle 11g comforting me
ORACLE STUFFS
06-Sep-2008
There are several new features in 11g. Various people would consider different features more important. So all the article has been written from my point of view.


Add - Drop - Unusable column (ALTER TABLE)
Oracle has changed way of the physical realization of the Alter table add .... default ... not null. Formerly it was updating all the existing records. It required workarounds for huge tables.
Now it is very quick operation allowing change tables even with compressed partitions and partitions in read-only tablespaces.

See: Ageing of data - avoid flat consideration of data in time (a


Compression (TABLE)
When table has been compressed in 9i or 10g version, then data should be either moved or inserted direct path (Insert /*+append*/) otherwise compression toke no effect.
Now (11g) compression has effect even using other DML commands such as standard insert or update. I have not noticed any performance difference while inserting into compressed or uncompressed table. It could make difference into ageing processes - segments could be initially created as compressed instead of being moved after load.


Partitioning
New types of partitionings such as INTERVAL or Referential and new possibilities of composite partitioning will change current approaches to partitioning strategies. We will describe it later in a separate article. It take some time of performance tests before any revisions of current approaches but changes are really huge.

See: Partitioning strategy for fact tables - 10g vs. 11g


Pivot, Unpivot
Unpivot is one of frequent transformation methods - ETL patterns. I will soon bring comparison of such pattern for Oracle 10g (a bit workaround) and the native SQL solution in Oracle 11g.

select * from tt6 unpivot (amount for metaindicator in(M0,M1,M2,M3));

B ME AMOUNT ---------- -- ---------- 1 M1 10 22 M2 220 25 M1 250 30 M2 300 34 M2 340 42 M2 420 43 M3 430 51 M3 510 54 M2 540 57 M1 570 78 M2 780 83 M3 830 87 M3 870 91 M3 910 100 M0 1000 6 M2 60 11 M3 110

See: Unpivot ETL transformation pattern - 10g vs. 11g

To be continued

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky