Forum Stats

  • 3,769,604 Users
  • 2,252,991 Discussions
  • 7,875,117 Comments

Discussions

Categories

Index-Organized Table Truncate vs Lock/Stage/Drop/Recreate

884401
884401 Member Posts: 15
edited Apr 24, 2013 1:51PM in SQL & PL/SQL
I ran into an issue in a project where a function is recreating an index-organized table by doing:

Table Structure:

CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;

Recreate Steps:

1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data

Along lines of

create table iot2 (
...
) organization index overflow parallel as
select
...
from gtt order by <primary_key_list>
);


5) Drop old iot table
6) Rename iot2 to iot
7) Recompile invalidated views and package (bodies)

One of the problems with this approach is the invalidation of dependent views and packages, etc

I am wondering if one can simply truncate the table instead of dropping it and recreating

and what are the considerations

The question becomes what is the best way to recreate an index-organized table?

Can one lock, copy to gtt, truncate, move back from gtt and ALTER TABLE table_iot MOVE? or can one just ALTER TABLE table_iot MOVE?

I note this url

http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables012.htm

And this section

Moving (Rebuilding) Index-Organized Tables

Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.

The following statement rebuilds the index-organized table admin_docindex:

ALTER TABLE admin_docindex MOVE;

You can rebuild index-organized tables online using the ONLINE keyword. The overflow data segment, if present, is rebuilt when the OVERFLOW keyword is specified.

Edited by: 881398 on Apr 24, 2013 10:47 AM
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Unknown
    Unknown
    edited Apr 23, 2013 7:08PM
    >
    The question becomes what is the best way to recreate an index-organized table?
    >
    The first question is: are you sure the IOT even needs to be rebuilt?

    You don't indicate that you or your code does ANYTHING to actually determine that there is a even a problem to begin with.

    That quote says 'you can encounter fragmentation'; it doesn't say, or mean, that you WILL encounter fragmentation.

    If it ain't broke, don't fix it.

    And why would you truncate the table if you want to keep the data?

    What is your question or issue? You already posted your own answer on how to rebuild so what is the problem with that?
    · Share on TwitterShare on Facebook
  • 884401
    884401 Member Posts: 15
    edited Apr 23, 2013 7:19PM
    No i'm not sure, but the current code is doing what i think is really ill-advised with the drop, and recompile..


    Is this the answer on how to re-create an index-organized table?

    ALTER TABLE table_iot MOVE OVERFLOW


    The original code is recreating the data using the steps i outlined above. I assume that was to avoid fragmentation. When i said truncate i meant as opposed to drop.

    I was thinking one could get the same effect with truncate (as opposed to drop) and repopulate. As you mention neither seems like the correct approach.


    But the question is then, is ALTER MOVE OVERFLOW the correct approach?

    Is there any conceivable consideration to be had, between using alter move (i assume correct), and either the drop approach or truncate approach outlined above?
    · Share on TwitterShare on Facebook
  • Unknown
    Unknown
    >
    No i'm not sure, but the current code is doing what i think is really ill-advised with the drop, and recompile..
    >
    Well you shouldn't be rebuilding a table for no reason at all.

    If you don't know that it is fragmented then leave it alone.

    Sounds like you aren't doing ANYTHING at all to even try to determine if the table is, or might be, fragmented. When you first create the table you should collect some basic info about the the total number of rows, total number of blocks, number of branch blocks and number of leaf blocks.

    You would do that after you validate the structure of the primary key 
    ANALYZE INDEX my_iot_pk VALIDATE STRUCTURE;

SELECT BLOCKS, BR_BLKS, LF_BLKS FROM index_stats . . .;
    Then you can collect the same data later and compare it to the original.
    >
    Is this the answer on how to re-create an index-organized table?

    ALTER TABLE table_iot MOVE OVERFLOW
    >
    Yes - if you mean 'rebuild'. But you are overlooking that an IOT has more than one component:

    1. the primary key columns
    2. additional columns you include in the main segment in addition to the key columns (see the INCLUDING clause of CREATE TABLE)
    3. a PCTTHRESHOLD value that will cause non-key columns to stored in the index blocks. See the SQL Language doc
    http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_7002.htm#i2218529
    >
    PCTTHRESHOLD integer Specify the percentage of space reserved in the index block for an index-organized table row. PCTTHRESHOLD must be large enough to hold the primary key. All trailing columns of a row, starting with the column that causes the specified threshold to be exceeded, are stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50. If you do not specify PCTTHRESHOLD, then the default is 50.
    >
    4. the OVERFLOW segment

    You didn't post the DDL for the table or indicate if you are using the INCLUDING or PCTTHRESHOL clauses. If only key columns are included in the index blocks then the main segment most likely will never be fragmented. That is also true if the non-key columns included in the index blocks have little or no DML on them.
    >
    The original code is recreating the data using the steps i outlined above. I assume that was to avoid fragmentation. When i said truncate i meant as opposed to drop.

    I was thinking one could get the same effect with truncate (as opposed to drop) and repopulate. As you mention neither seems like the correct approach.
    >
    Neither one IS the correct approach. Oracle can rebuild the table without doing ANY of that. Why would you manually do something with major side-effects like those when Oracle can do it for you without the side-effects? That just doesn't make sense.
    >
    Is there any conceivable consideration to be had, between using alter move (i assume correct), and either the drop approach or truncate approach outlined above?
    >
    Yes - whether you want to do the operation ONLINE or not. That can be open to opinion but mine is that it should be done offline if possible. The main reason is that any ONLINE operation always entails some performance degradation for uses doing DML on the IOT and some risk. An offline operation has no performance issue and if any problem occurs you have more options for dealing with it when you are offline.
    · Share on TwitterShare on Facebook
This discussion has been closed.