Niall's Oracle Pages – Oracle Opinion since 2004

New Features, New Defaults, New Side-Effects

with 2 comments

Starting with 11.2 which is now available for all the major platforms the behaviour of the CREATE TABLE statement has changed. Specifically, as introduced here, when creating a conventional table in a database created with the default options then the initial segment is not created until the first row is inserted into the table.

The good part of this is well described in the link above, specifically

The advantages of this space allocation method are the following:

•A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.

•Application installation time is reduced

In other words this is a very nice enhancement for applications like, for example, the Oracle E-Business Suite which typically take multiple hours to install and install a large number of objects that will never be used by any given customer.

I am however already aware of 2 potential downsides to this. The first downside will also apply to exactly the ERP type of application that the designers apparently had in mind for this feature. This is well described in Metalink Note 1050193.1 as follows:

The sequences created as default ( start with 1 increment by 1) are not staring with ‘1’ when used in insert query script, Instead they are starting with 2 or 4.
This behaviour of sequences is seen in version Oracle11gR2, not seen in earlier versions

In other words if you specify a sequence and a start with value to generate a range of IDs to be used to create a primary key, the sequence will effectively not start with the “START WITH” value. This is likely a minor inconvenience but one to be aware of if you subscribe to the artificial key design paradigm.

The second is described in this forum thread and is a result of the new default behaviour only applying to the Enterprise Edition of Oracle. Should you create an 11.2 database using the default options and later wish to export the data and import into a standard edition database (We have a number of clients for whom this is not a hypothetical case, they’ve done exactly this in the past) then there isn’t currently a satisfactory method to do this for schemas of any size. The issue is compounded currently by the fact that the Enterprise only status of this feature is not correctly documented.

All in all I think I’d rather that the default value for the initialisation parameter DEFERRED_SEGMENT_CREATION was set to false with the option for people who regularly create schemas with large numbers of never to be used tables and for whom application software installation time is a significant concern to set it to TRUE. It just seems like one of those cases where adopting a new option as a default in the very first release in which it is available is a less than optimal idea.

Possibly Related Posts:

Written by Niall Litchfield

April 25th, 2010 at 2:34 pm

Posted in Uncategorized

Tagged with , , ,

2 Responses to 'New Features, New Defaults, New Side-Effects'

Subscribe to comments with RSS or TrackBack to 'New Features, New Defaults, New Side-Effects'.

  1. […] that deferred segment creation is an Enterprise Edition only feature, and explain the potential problems that restriction might cause. (pages […]

Leave a Reply

%d bloggers like this: