jump to navigation

Oracle11g Creation On Demand Indexes (Invisible Touch) February 17, 2011

Posted by Richard Foote in 11g, 11g New features, Oracle Indexes.
trackback

Prior to Oracle11g Release 2, the default and minimum size of a segment is one extent. So in the below example, where we create a table and five associated indexes: 

 
SQL> create table empty (a number, b number, c number, d number, e number);
 
Table created.
 
SQL> create index empty_a_i on empty(a);
 
Index created.
 
SQL> create index empty_b_i on empty(b);
 
Index created.
 
SQL> create index empty_c_i on empty(c);
 
Index created.
 
SQL> create index empty_d_i on empty(d);
 
Index created.
 
SQL> create index empty_e_i on empty(e);
 
Index created.
 

SQL> select segment_name, blocks, bytes, extents from dba_segments where segment_name like 'EMPTY%';
 
SEGMENT_NAME     BLOCKS      BYTES    EXTENTS
------------ ---------- ---------- ----------
EMPTY               128    1048576          1
EMPTY_A_I           128    1048576          1
EMPTY_B_I           128    1048576          1
EMPTY_C_I           128    1048576          1
EMPTY_D_I           128    1048576          1
EMPTY_E_I           128    1048576          1
 
6 rows selected.

  

Each of the segments has been allocated an extent, including each of the indexes.
 
However, since Oracle11g Release 2, this default behaviour has changed. Running exactly the same demo:
 

 
SQL> create table empty (a number, b number, c number, d number, e number);
 
Table created.
 
SQL> create index empty_a_i on empty(a);
 
Index created.
 
SQL> create index empty_b_i on empty(b);
 
Index created.
 
SQL> create index empty_c_i on empty(c);
 
Index created.
 
SQL> create index empty_d_i on empty(d);
 
Index created.
 
SQL> create index empty_e_i on empty(e);
 
Index created.
 
SQL> select segment_name, blocks, bytes, extents from dba_segments where segment_name like 'EMPTY%';
 
no rows selected

 

We can see that no actual segments have been allocated. The default number of extents when creating an object is now effectively zero. Oracle now defers the creation of the segment and the actual allocation of extents and storage until the point in time when the first row gets inserted.
 
This means for those packaged applications where a large number of objects get created of which relatively few are actually ever used by the specific deployment of the application (eg. SAP) , a substantial amount of storage could potentially be saved. It also can save a significant amount of time deploying such applications as the overheads associated with actually creating the never to be used segments can be avoided.
 
There also some subtle performance implications when the application attempts to access some of these “empty” tables. I’m just going to create another table, but this one populated with a bunch of rows and run a query that joins this with the “empty” table:
 

 
SQL> create table bowie as select rownum id, 'BOWIE' name from dual connect by level <= 1000000;
 
Table created.
 
SQL> create index bowie_id_i on bowie(id);
 
Index created. 

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', cascade=>true, estimate_percent=>null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.
 
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'EMPTY', cascade=>true, estimate_percent=>null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.

SQL> select * from bowie, empty where bowie.id=empty.a and bowie.id = 42;
 
no rows selected
 

Execution Plan
----------------------------------------------------------
Plan hash value: 1098778158
 
--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |     1 |    76 |     2   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN         |            |     1 |    76 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID | EMPTY      |     1 |    65 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | EMPTY_A_I  |     1 |       |     1   (0)| 00:00:01 |
|   4 |   BUFFER SORT                 |            |     1 |    11 |     1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID| BOWIE      |     1 |    11 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | BOWIE_ID_I |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("EMPTY"."A"=42)
   6 - access("BOWIE"."ID"=42)
 

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        303  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

 

With a table which has yet to be populated, the CBO knows there can be no rows associated with this table (and hence no returns return from this query). You can’t get any less than 0 consistent gets. Note previously, the CBO would be forced to perform at the very least one read (if not more) as it can’t “know” there are indeed no rows, even with the table statistics set at zero (as they may no longer be accurate) until it actually physically accesses a segment.
 
The segment is not actually created and storage allocated to the object until the first row is inserted into the table. This means that this first insert will be a relatively expensive operation as the work associated with creating the segment and allocating the initial extent needs to be implicitly performed in the background at this time. Not only for the table itself, but for any dependant object as well (eg. indexes).

 
SQL> insert into empty (a, b) values (1,1);
 
1 row created.
 

Execution Plan
----------------------------------------------------------
 
--------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     1 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | EMPTY |       |            |          |
--------------------------------------------------------------------------
 

Statistics
----------------------------------------------------------
       3535  recursive calls
        178  db block gets
        830  consistent gets
         26  physical reads
      27272  redo size
        367  bytes sent via SQL*Net to client
        321  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         59  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
SQL> commit;
 
Commit complete.

   

Although it’s only a single row insert, note the high number of recursive calls and logical I/Os due in large part to the segment(s) being implicitly created in the background at this time. Subsequent inserts are nowhere near as expensive:
 

 
SQL> insert into empty (a, b) values (1,1);
 
1 row created.
 

Execution Plan
----------------------------------------------------------
 
--------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     1 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | EMPTY |       |            |          |
--------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
          0  recursive calls
          5  db block gets
          1  consistent gets
          0  physical reads
        692  redo size
        381  bytes sent via SQL*Net to client
        321  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

Although, the table was populated with values that only directly impacted 2 of the indexes, once the table segment is created, all dependent segments are created at the same time.

 
SQL> select segment_name, blocks, bytes, extents from dba_segments where segment_name like 'EMPTY%';
 
SEGMENT_NAME     BLOCKS      BYTES    EXTENTS
------------ ---------- ---------- ----------
EMPTY               128    1048576          1
EMPTY_A_I           128    1048576          1
EMPTY_B_I           128    1048576          1
EMPTY_C_I           128    1048576          1
EMPTY_D_I           128    1048576          1
EMPTY_E_I           128    1048576          1
 

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'EMPTY', cascade=>true, estimate_percent=>null, method_opt=> 'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.
 
SQL> select index_name, blevel, leaf_blocks, status from dba_indexes where index_name like 'EMPTY%';
 
INDEX_NAME     BLEVEL LEAF_BLOCKS STATUS
---------- ---------- ----------- --------
EMPTY_A_I           0           1 VALID
EMPTY_B_I           0           1 VALID
EMPTY_C_I           0           0 VALID
EMPTY_D_I           0           0 VALID
EMPTY_E_I           0           0 VALID

 

 
Note all the index segments have been created although only 2 of them actually contain data.

Finally, there are some implications with regard to how quotas are enforced that potentially may cause issues. Prior to Oracle11g Release 2, if a user tried to create a segment in a tablespace for which they don’t have sufficient privileges, the operation will fail during the creation of the object:

 
SQL> create user muse identified by muse default tablespace user_data temporary tablespace temp;
 
User created.
 
SQL> grant create session, create table to muse;
 
Grant succeeded.
 
SQL> connect muse/muse;
Connected.
SQL> create table fred (id number primary key using index (create index fred_pk on fred(id) tablespace user_data), name varchar2(20));
create table fred (id number primary key using index (create index fred_pk on fred(id) tablespace user_data), name varchar2(20))
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USER_DATA'

 

However with Oracle11g Release 2, as no segment is actually created and hence storage allocated when an object is created, such creation of objects will now initially succeed. No quotas can be violated if no storage is actually used during the creation of the object:
 

 
SQL> create user muse identified by muse default tablespace user_data temporary tablespace temp;
 
User created.
 
SQL> grant create session, create table to muse;
 
Grant succeeded.
 
SQL> connect muse/muse
Connected.
 
SQL> create table fred (id number primary key using index (create index fred_pk on fred(id) tablespace user_data), name varchar2(20));
 
Table created.

  

It’s only when the table is first populated and hence when the segment is actually created and storage allocated, will quota related issues generate errors:

 
SQL> insert into fred values (1, 'BOWIE');
insert into fred values (1, 'BOWIE')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USER_DATA'

 

An interesting change in default behaviour introduced in Oracle11g Release 2 that can potentially be quite beneficial but perhaps also a little dangerous for the unwary.

About these ads

Comments»

1. Tim Hall - February 17, 2011

Hi.

The last point has caught me out a few times. It would have been nice if Oracle had added a check for the quota to the CREATE TABLE processing, rather than relying on the extent creation to police it. They we would then have the same behavior with relation to table creation and quotas.

I know it’s a *feature*, but it doesn’t half feel like a bug when it happens. :)

Cheers

Tim…

Richard Foote - February 18, 2011

Hi Tim

Great to hear from you :)

It can rather hide a number of potiental problems “under the carpet” for a while for one to only trip up over it all at a later point in time.

The obvious issue of course is that fixing this means changing code unnecessarily as things do “work” as such. I have alway regarded having no quota as being somewhat different as having exceeded a nominated quota limit but the code doesn’t really make such a delineation.

Certainly something to be wary of.

2. Nitin - February 17, 2011

This is a good one. What about partitions and sub partitions? In one of our systems we are facing a lot of wasted space as the initial extent for the sub partitions is 10MB while the data is lesser. Maybe you can include the analysis for partitions and subpartitions in this post to make it more comprehensive.

Richard Foote - February 18, 2011

Hi Nitin

OK, I’ll cook up an example just for you :)

3. Marcus Mönnig - February 17, 2011

> Oracle now defers the creation of the segment and
> the actual allocation of extents and storage
> until the point in time when the first row gets inserted.

For what its worth, Oracle EE with the the parameter deferred_segment_creation set to the default TRUE. The feature is not available in SE.
As a side effect, the feature renders conventional export (finally) useless, since empty tables will not get exported.

Flado - February 18, 2011

Marcus,

> As a side effect, the feature renders conventional export (finally) useless, since empty tables will not get exported.

What do you mean by “conventional export”? exp (11.2.0.2) did merrily export a segmentless table with an index, and imp imported it for me, and it remained segmentless after importing…

The thing is, you don’t get the benefit automatically when upgrading, though. If you upgrade a database in place, the tables and indexes already have segments and the empty ones are of course not dropped. If you do a exp from the old version and imp into a new 11.2 database, you still get all segments created, because the DDLs contain an explicit INITIAL specification. The only way to get rid of the empty segments (that I could think of) was to either pre-create the objects, or explicitly do a TRUNCATE TABLE … DROP ALL STORAGE on the empty ones afterwards.
I’m not sure if there is a difference in behaviour between exp/imp and expdp/impdp in this matter.

Cheers!

Richard Foote - February 18, 2011

Hi Flado

Great comments, thanks :)

Maxim - February 18, 2011

Marcus, at least on the linux platform the issue with exp was fixed in 11.2.0.2 afaik.

Best regards

Richard Foote - February 18, 2011

Hi Maxim

Thanks for the info, much appreciated :)

Richard Foote - February 18, 2011

Hi Marcus

See Flado’s and Maxim’s comments below, this EXP issue has been addressed in latest Oracle versions.

That said, I do prefer Data Pump I must say :)

4. Vineeth - February 18, 2011

This change i noticed in 11 R2 while testing the ROWID Index Row Entry Columns Impact Index Block Splits Demo.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,820 other followers

%d bloggers like this: