Extract(Read) > Transform(Test&Apply) > Load(Learn) Blog

September 28, 2008

11g Enhancement for ALTER TABLE .. ADD COLUMN Functionality

Filed under: Oracle 11g New Features,Oracle Performance — H.Tonguç Yılmaz @ 10:45 pm

Before Oracle 11g adding new columns with DEFAULT values and NOT NULL constraint required both an exclusive lock on the table and the default value to be stored in all existing records.

Now in Oracle 11g the database can optimize the resource usage and storage requirements for this operation, the default value is stored in the data dictionary instead of updating the table column as a result especially for large tables the execution time is reduced and space is saved.

In addition, the following ADD COLUMN operations can now run concurrently with DML operations:
* Add a NOT NULL column with a default value
* Add a nullable column without a default value
* Add a virtual column

release 1002000300 –


drop table tst_source purge ;
create table tst_source nologging as
select rownum id, text
  from dba_source;

set timing on

ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL);

Elapsed: 00:00:30.43

set timing off

exec dbms_stats.gather_table_stats(user, 'tst_source');

select count(*) from tst_source where name is NULL ;

  COUNT(*)
----------
         0

release 1101000600 –


drop table tst_source purge ;
create table tst_source nologging as
select rownum id, text
  from dba_source;

set timing on

ALTER TABLE tst_source ADD (name VARCHAR2(16) DEFAULT 'N' NOT NULL);

Elapsed: 00:00:00.10

set timing off

exec dbms_stats.gather_table_stats(user, 'tst_source');

select count(*) from tst_source where name is NULL ;

  COUNT(*)
----------
         0
Advertisements

On SAGE and Oracle’s new 11g SQL Tuning Workshop Education Content

Filed under: Oracle 11g New Features,Oracle Performance — H.Tonguç Yılmaz @ 5:48 pm

Most probably you think SAGE (this was its project name before Larry’s announcement, much more compact naming so I prefer it :) –Oracle Exadata Storage Server and the HP Oracle Database Machine– will not make a change in your daily life and you maybe unsatisfied with this announcement but I promise to give you something cool whether you are a massive OLAP or an XE developer with this post. :)

But first let me share with you one of my friend’s, who is a warehouse developer, first response to SAGE: “Oh I knew Oracle should work much more faster for years, the fault was not ours you see!” .:) So does these new features promise to reduce the importance of the warehouse development best practices? I really don’t think so..

So on my 22 hours way back home I had a chance to look at this new Oracle University education and I strongly think that this is the best content you may get from a tuning education. First, with this post, let me briefly explain the table of contents and then I think I will be doing some series of blog posts parallel to this content.

If you had 10g’s SQL Tuning Workshop here is not only new features but new chapters in the 11g’s content now, like a whole chapter dedicated to Star Transformation and a very long chapter dedicated to the optimizer operations like access path options of the Oracle database. I tried to motivate developers around me for not doing any SQL Tuning, just do tuning as they are developing and I am very happy to see this chapter as it is a part I always dreamed of. :)

Let me mention another interesting observation of mine, if you attended Jonathan Lewis’s optimizer seminars I think you will also think that the content and even some pictures are very similar in this education to that content, to my understanding this is a kind of a kind approval for people like Mr.Lewis who are trying to improve the communities understanding of the complex technologies like optimizer. No pain no gain right, so I do not think this education now will be easily consumed by starters as it should be.

By the way, as we own one of the largest warehouses in the world and there is a very important possibility that we may be testing and using Exadata and so I may be blogging something more than “reading billions of rows in seconds” at the end of the day ETL(I mean lots of writes), sort operations parallel scans and hash joins are in total what will make the difference since lots of reports(I mean reads) are already tuned to be fast enough by logical design and access path options like partitioning, bitmap indexes and materialized views in today’s optimized warehouses.

Create a free website or blog at WordPress.com.