Oracle Scratchpad

November 6, 2009

Did you know …

Filed under: Indexing,Infrastructure — Jonathan Lewis @ 7:02 pm BST Nov 6,2009

… a few things about the use of index space to suprise your friends and amaze your colleagues:

  • If you use “bigfile” tablespaces for your tables this can result in some indexes becoming more space-efficient than they would be otherwise.
  • Creating tables in tablespaces built from multiple datafiles may cause some of their indexes to be less space-efficient than they would otherwise be.
  • Unless you store tables in different tablespaces from indexes, rebuilding indexes can allow the indexes that you haven’t rebuilt to become less space-efficient.
  • Dropping (or truncating) a table may allow unrelated indexes to become less space-efficient
  • Using the partition exchange mechanism for data loading may make some unrelated indexes become less space-efficient.
  • Some classes of index will be less space-efficient in a RAC system than they would be in a single-instance system.
  • Using the “shrink” option on a table may cause some of its indexes to get bigger.

On the plus side: although there are many activities that contribute to indexes  being bigger than they would be if they were newly created, there aren’t many cases where the benefit of recreating them (or restructuring your database) is worth the effort. Note that many “space anomalies” in indexes are transient anyway and are self-correcting over time – given enough time and sufficient use.

Footnote: I am not going to supply an explanation for any of the above phenomena because I don’t think they’re sufficiently important to warrant any action – in general. This note has been published only for the purpose of answering trivia questions and similar entertainments.

 

4 Comments »

  1. Hi jonathan,
    I could think of a test case where an index could get bigger when created in a tablespace with more than 1 datafile vs creating it in a bigfile tablespace.

    using 11.1.0.6.0 on windows xp x64

    create bigfile tablespace DATA_BIG datafile ‘C:\ORADATA\ORCL\DATA_BIG01.DBF’ size 512m;

    create bigfile tablespace DATA_SMALL datafile ‘C:\ORADATA\ORCL\DATA_SMALL01.DBF’ size 512m, ‘C:\ORADATA\ORCL\DATA_SMALL02.DBF’ size 512m;

    drop table x_small
    /
    drop table x_big
    /
    create table x_small(c1 char(100)) tablespace data_small
    /
    create index ix_small on x_small(c1) tablespace users
    /
    create table x_big(c1 char(100)) tablespace data_big
    /
    create index ix_big on x_big(c1) tablespace users
    /

    Now i allocate extents to X_SMALL across both the files

    alter table x_small allocate extent (datafile ‘C:\ORADATA\ORCL\TEST01.DBF’);
    alter table x_small allocate extent (datafile ‘C:\ORADATA\ORCL\TEST02.DBF’);
    alter table x_small allocate extent (datafile ‘C:\ORADATA\ORCL\TEST01.DBF’);
    alter table x_small allocate extent (datafile ‘C:\ORADATA\ORCL\TEST02.DBF’);

    Put around 1680 of these lines in a file test2.sql
    insert into x_small values(‘a’);
    commit;
    insert into x_small values(‘a’);
    commit;
    insert into x_small values(‘a’);
    commit;
    …………….
    …………….

    Put around 1680 of these lines in a file test3.sql
    insert into x_big values(‘a’);
    commit;
    insert into x_big values(‘a’);
    commit;
    insert into x_big values(‘a’);
    commit;
    …………….
    …………….

    run test2.sql & test3.sql

    sys@orcl> select count(*) from x_small;

    COUNT(*)
    ———-
    1680

    sys@orcl> select count(*) from x_big;

    COUNT(*)
    ———-
    1680

    select owner, segment_name, segment_type, partition_name, tablespace_name,
    extent_id, file_id, block_id, blocks
    from dba_extents
    where segment_name in (‘X_SMALL’,’X_BIG’)
    order by 1,2,4,5,6,7
    /
    Start
    Segment Partition Extent File Block
    Owner Segment Name Type Name Tablespace No No No Blocks
    ———- ——————– ——————– ——————- ——————– ——- —– ——- ———-
    SYS X_BIG TABLE DATA_BIG 0 6 19921 8
    SYS X_BIG TABLE DATA_BIG 1 6 19929 8
    SYS X_BIG TABLE DATA_BIG 2 6 19937 8
    SYS X_BIG TABLE DATA_BIG 3 6 19945 8

    SYS X_SMALL TABLE DATA_SMALL 0 8 49 8
    SYS X_SMALL TABLE DATA_SMALL 1 7 25 8
    SYS X_SMALL TABLE DATA_SMALL 2 8 57 8
    SYS X_SMALL TABLE DATA_SMALL 3 7 33 8

    select owner, segment_name, segment_type, partition_name, tablespace_name,
    extent_id, file_id, block_id, blocks
    from dba_extents
    where segment_name in (‘IX_SMALL’,’IX_BIG’)
    order by 1,2,4,5,6,7

    Start
    Segment Partition Extent File Block
    Owner Segment Name Type Name Tablespace No No No Blocks
    ———- ——————– ——————– ——————- ——————– ——- —– ——- ———-
    SYS IX_BIG INDEX USERS 0 4 57 8
    SYS IX_BIG INDEX USERS 1 4 105 8
    SYS IX_BIG INDEX USERS 2 4 113 8
    SYS IX_BIG INDEX USERS 3 4 121 8
    SYS IX_BIG INDEX USERS 4 4 129 8

    SYS IX_SMALL INDEX USERS 0 4 49 8
    SYS IX_SMALL INDEX USERS 1 4 65 8
    SYS IX_SMALL INDEX USERS 2 4 73 8
    SYS IX_SMALL INDEX USERS 3 4 81 8
    SYS IX_SMALL INDEX USERS 4 4 89 8
    SYS IX_SMALL INDEX USERS 5 4 97 8

    index_stats

    NAME HEIGHT Blocks LF_ROWS LF_BLKS LF_ROWS_LEN BR_ROWS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE
    ——– ———- ———- ———- ———- ———– ———- —————– ———– ———-
    IX_SMALL 2 48 1680 39 188160 38 1680 320032 192484
    IX_BIG 2 40 1680 33 188160 32 1680 272032 191804

    index tree dump

    *****************************
    Index tree dump for ix_small
    *****************************

    —– begin tree dump
    branch: 0×1000034 16777268 (0: nrow: 39, level: 1)
    leaf: 0×1000038 16777272 (-1: nrow: 41 rrow: 41)
    leaf: 0x100004d 16777293 (0: nrow: 41 rrow: 41)
    leaf: 0x100004e 16777294 (1: nrow: 41 rrow: 41)
    leaf: 0×1000054 16777300 (2: nrow: 41 rrow: 41)
    leaf: 0×1000055 16777301 (3: nrow: 50 rrow: 50)
    leaf: 0x100004c 16777292 (4: nrow: 31 rrow: 31)
    leaf: 0×1000041 16777281 (5: nrow: 41 rrow: 41)
    leaf: 0×1000042 16777282 (6: nrow: 41 rrow: 41)
    leaf: 0×1000043 16777283 (7: nrow: 41 rrow: 41)
    leaf: 0x100004f 16777295 (8: nrow: 40 rrow: 40)
    leaf: 0×1000050 16777296 (9: nrow: 41 rrow: 41)
    leaf: 0x100004a 16777290 (10: nrow: 41 rrow: 41)
    leaf: 0x100004b 16777291 (11: nrow: 41 rrow: 41)
    leaf: 0x100005c 16777308 (12: nrow: 41 rrow: 41)
    leaf: 0x100005d 16777309 (13: nrow: 40 rrow: 40)
    leaf: 0x100005e 16777310 (14: nrow: 41 rrow: 41)
    leaf: 0×1000064 16777316 (15: nrow: 41 rrow: 41)
    leaf: 0×1000065 16777317 (16: nrow: 41 rrow: 41)
    leaf: 0×1000066 16777318 (17: nrow: 41 rrow: 41)
    leaf: 0×1000067 16777319 (18: nrow: 40 rrow: 40)
    leaf: 0×1000068 16777320 (19: nrow: 58 rrow: 58)
    leaf: 0×1000048 16777288 (20: nrow: 31 rrow: 31)
    leaf: 0×1000037 16777271 (21: nrow: 41 rrow: 41)
    leaf: 0×1000044 16777284 (22: nrow: 41 rrow: 41)
    leaf: 0×1000045 16777285 (23: nrow: 41 rrow: 41)
    leaf: 0×1000047 16777287 (24: nrow: 67 rrow: 67)
    leaf: 0×1000046 16777286 (25: nrow: 41 rrow: 41)
    leaf: 0×1000036 16777270 (26: nrow: 37 rrow: 37)
    leaf: 0×1000035 16777269 (27: nrow: 41 rrow: 41)
    leaf: 0×1000051 16777297 (28: nrow: 41 rrow: 41)
    leaf: 0×1000052 16777298 (29: nrow: 41 rrow: 41)
    leaf: 0x100005b 16777307 (30: nrow: 67 rrow: 67)
    leaf: 0×1000053 16777299 (31: nrow: 41 rrow: 41)
    leaf: 0x100005f 16777311 (32: nrow: 41 rrow: 41)
    leaf: 0×1000060 16777312 (33: nrow: 41 rrow: 41)
    leaf: 0x100005a 16777306 (34: nrow: 67 rrow: 67)
    leaf: 0×1000058 16777304 (35: nrow: 31 rrow: 31)
    leaf: 0×1000056 16777302 (36: nrow: 71 rrow: 71)
    leaf: 0×1000057 16777303 (37: nrow: 25 rrow: 25)
    —– end tree dump

    *****************************
    Index tree dump for ix_big
    *****************************

    —– begin tree dump
    branch: 0x100003c 16777276 (0: nrow: 33, level: 1)
    leaf: 0×1000040 16777280 (-1: nrow: 41 rrow: 41)
    leaf: 0x100003f 16777279 (0: nrow: 41 rrow: 41)
    leaf: 0x100006c 16777324 (1: nrow: 41 rrow: 41)
    leaf: 0x100006d 16777325 (2: nrow: 41 rrow: 41)
    leaf: 0x100006f 16777327 (3: nrow: 67 rrow: 67)
    leaf: 0x100006e 16777326 (4: nrow: 41 rrow: 41)
    leaf: 0x100003e 16777278 (5: nrow: 37 rrow: 37)
    leaf: 0x100003d 16777277 (6: nrow: 41 rrow: 41)
    leaf: 0×1000072 16777330 (7: nrow: 41 rrow: 41)
    leaf: 0×1000073 16777331 (8: nrow: 41 rrow: 41)
    leaf: 0×1000074 16777332 (9: nrow: 41 rrow: 41)
    leaf: 0×1000075 16777333 (10: nrow: 41 rrow: 41)
    leaf: 0×1000076 16777334 (11: nrow: 39 rrow: 39)
    leaf: 0×1000078 16777336 (12: nrow: 31 rrow: 31)
    leaf: 0×1000070 16777328 (13: nrow: 71 rrow: 71)
    leaf: 0×1000069 16777321 (14: nrow: 71 rrow: 71)
    leaf: 0x100006a 16777322 (15: nrow: 71 rrow: 71)
    leaf: 0x100006b 16777323 (16: nrow: 71 rrow: 71)
    leaf: 0×1000077 16777335 (17: nrow: 41 rrow: 41)
    leaf: 0x100007f 16777343 (18: nrow: 41 rrow: 41)
    leaf: 0×1000080 16777344 (19: nrow: 70 rrow: 70)
    leaf: 0×1000088 16777352 (20: nrow: 38 rrow: 38)
    leaf: 0×1000079 16777337 (21: nrow: 41 rrow: 41)
    leaf: 0x100007a 16777338 (22: nrow: 41 rrow: 41)
    leaf: 0x100007b 16777339 (23: nrow: 41 rrow: 41)
    leaf: 0×1000087 16777351 (24: nrow: 67 rrow: 67)
    leaf: 0x100007e 16777342 (25: nrow: 31 rrow: 31)
    leaf: 0x100007c 16777340 (26: nrow: 71 rrow: 71)
    leaf: 0x100007d 16777341 (27: nrow: 71 rrow: 71)
    leaf: 0×1000082 16777346 (28: nrow: 71 rrow: 71)
    leaf: 0×1000083 16777347 (29: nrow: 71 rrow: 71)
    leaf: 0×1000084 16777348 (30: nrow: 71 rrow: 71)
    leaf: 0×1000085 16777349 (31: nrow: 46 rrow: 46)
    —– end tree dump

    The rowid in the non-unique index, with shifting file#, caused more index leaf block splits in ix_small

    regards
    srivenu

    Comment by srivenu — November 7, 2009 @ 8:39 pm BST Nov 7,2009 | Reply

    • Exactly,
      You’ve picked an example right at the boundary to demonstrate the principle.

      You don’t actually have to do the manual allocation of extents, by the way. Apart from the special case of the first 1MB of space allocated under system managed extents, Oracle will round-robin extents through the available data files.

      To increase the visibility of the effect, you could even try using freelist management instead of the default 11g ASSM (automatic segment space management). (No need to post the results, though).

      Comment by Jonathan Lewis — November 9, 2009 @ 6:26 pm BST Nov 9,2009 | Reply

  2. There were more 50-50 block splits for IX_SMALL (from V$SESSTAT).
    regards
    srivenu

    Comment by srivenu — November 7, 2009 @ 10:22 pm BST Nov 7,2009 | Reply

  3. [...] Jonathan Lewis-Did you know? [...]

    Pingback by Blogroll Report 30/10/2009-06/11/2009 « Coskan’s Approach to Oracle — November 17, 2009 @ 12:55 am BST Nov 17,2009 | Reply


RSS feed for comments on this post. TrackBack URI

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

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,877 other followers