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.
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…
LikeLike
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.
LikeLike
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.
LikeLike
Hi Nitin
OK, I’ll cook up an example just for you 🙂
LikeLike
> 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.
LikeLike
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!
LikeLike
Hi Flado
Great comments, thanks 🙂
LikeLike
Marcus, at least on the linux platform the issue with exp was fixed in 11.2.0.2 afaik.
Best regards
LikeLike
Hi Maxim
Thanks for the info, much appreciated 🙂
LikeLike
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 🙂
LikeLike
This change i noticed in 11 R2 while testing the ROWID Index Row Entry Columns Impact Index Block Splits Demo.
LikeLike