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: 0x1000034 16777268 (0: nrow: 39, level: 1)
    leaf: 0x1000038 16777272 (-1: nrow: 41 rrow: 41)
    leaf: 0x100004d 16777293 (0: nrow: 41 rrow: 41)
    leaf: 0x100004e 16777294 (1: nrow: 41 rrow: 41)
    leaf: 0x1000054 16777300 (2: nrow: 41 rrow: 41)
    leaf: 0x1000055 16777301 (3: nrow: 50 rrow: 50)
    leaf: 0x100004c 16777292 (4: nrow: 31 rrow: 31)
    leaf: 0x1000041 16777281 (5: nrow: 41 rrow: 41)
    leaf: 0x1000042 16777282 (6: nrow: 41 rrow: 41)
    leaf: 0x1000043 16777283 (7: nrow: 41 rrow: 41)
    leaf: 0x100004f 16777295 (8: nrow: 40 rrow: 40)
    leaf: 0x1000050 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: 0x1000064 16777316 (15: nrow: 41 rrow: 41)
    leaf: 0x1000065 16777317 (16: nrow: 41 rrow: 41)
    leaf: 0x1000066 16777318 (17: nrow: 41 rrow: 41)
    leaf: 0x1000067 16777319 (18: nrow: 40 rrow: 40)
    leaf: 0x1000068 16777320 (19: nrow: 58 rrow: 58)
    leaf: 0x1000048 16777288 (20: nrow: 31 rrow: 31)
    leaf: 0x1000037 16777271 (21: nrow: 41 rrow: 41)
    leaf: 0x1000044 16777284 (22: nrow: 41 rrow: 41)
    leaf: 0x1000045 16777285 (23: nrow: 41 rrow: 41)
    leaf: 0x1000047 16777287 (24: nrow: 67 rrow: 67)
    leaf: 0x1000046 16777286 (25: nrow: 41 rrow: 41)
    leaf: 0x1000036 16777270 (26: nrow: 37 rrow: 37)
    leaf: 0x1000035 16777269 (27: nrow: 41 rrow: 41)
    leaf: 0x1000051 16777297 (28: nrow: 41 rrow: 41)
    leaf: 0x1000052 16777298 (29: nrow: 41 rrow: 41)
    leaf: 0x100005b 16777307 (30: nrow: 67 rrow: 67)
    leaf: 0x1000053 16777299 (31: nrow: 41 rrow: 41)
    leaf: 0x100005f 16777311 (32: nrow: 41 rrow: 41)
    leaf: 0x1000060 16777312 (33: nrow: 41 rrow: 41)
    leaf: 0x100005a 16777306 (34: nrow: 67 rrow: 67)
    leaf: 0x1000058 16777304 (35: nrow: 31 rrow: 31)
    leaf: 0x1000056 16777302 (36: nrow: 71 rrow: 71)
    leaf: 0x1000057 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: 0x1000040 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: 0x1000072 16777330 (7: nrow: 41 rrow: 41)
    leaf: 0x1000073 16777331 (8: nrow: 41 rrow: 41)
    leaf: 0x1000074 16777332 (9: nrow: 41 rrow: 41)
    leaf: 0x1000075 16777333 (10: nrow: 41 rrow: 41)
    leaf: 0x1000076 16777334 (11: nrow: 39 rrow: 39)
    leaf: 0x1000078 16777336 (12: nrow: 31 rrow: 31)
    leaf: 0x1000070 16777328 (13: nrow: 71 rrow: 71)
    leaf: 0x1000069 16777321 (14: nrow: 71 rrow: 71)
    leaf: 0x100006a 16777322 (15: nrow: 71 rrow: 71)
    leaf: 0x100006b 16777323 (16: nrow: 71 rrow: 71)
    leaf: 0x1000077 16777335 (17: nrow: 41 rrow: 41)
    leaf: 0x100007f 16777343 (18: nrow: 41 rrow: 41)
    leaf: 0x1000080 16777344 (19: nrow: 70 rrow: 70)
    leaf: 0x1000088 16777352 (20: nrow: 38 rrow: 38)
    leaf: 0x1000079 16777337 (21: nrow: 41 rrow: 41)
    leaf: 0x100007a 16777338 (22: nrow: 41 rrow: 41)
    leaf: 0x100007b 16777339 (23: nrow: 41 rrow: 41)
    leaf: 0x1000087 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: 0x1000082 16777346 (28: nrow: 71 rrow: 71)
    leaf: 0x1000083 16777347 (29: nrow: 71 rrow: 71)
    leaf: 0x1000084 16777348 (30: nrow: 71 rrow: 71)
    leaf: 0x1000085 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,905 other followers