you are talking two method
one is
an online index rebuild

i tried this one but it didn't work.

alter index SYS_IOT_TOP_87241 rebuild
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

I have been searching for this answer for 2 days. I should have come here first. This is exactly what I was looking for.

Hallo Tom,

your "answer and reply" is very useful to learn many details!

I had changed some tables to IOT some month before.
Now I had problems with deleting more than one rows in the IOTs. The IOTs have foreign keys with on delete cascade to one table (TestDok).
I don't know whether the problem is new or not because normally rows don't be deleted in TestDok, they were signed. 

SQL> r
  1  delete from TESTDok
  2* where objektid = '38060'
delete from TESTDok
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [25012], [8], [10], [], [], [], [], []


I saw in the trc-file the problem occurs when oracle must delete the rows from the child table.


SQL> select * from TESTDokH where objektid = '38060' or superobjektid = '38060';

OBJEKTID                         SUPEROBJEKTID
-------------------------------- --------------------------------
27654                            38060
28830                            38060

2 rows selected.

SQL> delete from TESTDokH where superobjektid = '38060';
delete from TESTDokH where superobjektid = '38060'
                    *
ERROR at line 1:
ORA-00600: internal error code, arguments: [25012], [8], [10], [], [], [], [], []

The tables are now :

/* TESTDok */
CREATE TABLE TESTDok
(
    OBJEKTID        VARCHAR2(32) 
            CONSTRAINT PK_TESTDok PRIMARY KEY
            using index tablespace user_i,
    MANDANT              VARCHAR2(30) NOT NULL,
    NOTESID         VARCHAR2(32) 
            CONSTRAINT UK_TESTDok UNIQUE 
            using index tablespace user_i,
    DELETED             VARCHAR2(1) DEFAULT '0',
. . .
    AID_MZ         VARCHAR2(32),
    AID_EF         VARCHAR2(32),
CONSTRAINT FK_TESTDok1 FOREIGN KEY (AID_MZ)
        REFERENCES TESTAR (FE_ID)
        ON DELETE SET NULL,
CONSTRAINT FK_TESTDok2 FOREIGN KEY (AID_EF)
        REFERENCES TESTAR (FE_ID)
        ON DELETE SET NULL
) tablespace user_tg pctfree 8 pctused 86
/

create bitmap index BM_TESTDok_mandant ON TESTDok (mandant) 
tablespace user_i
/

/* TESTDokH IOT*/
CREATE TABLE TESTDokH
(
    OBJEKTID        VARCHAR2(32),
    SUPEROBJEKTID    VARCHAR2(32),
  CONSTRAINT IOTPK_TESTDokHiera PRIMARY KEY (OBJEKTID, SUPEROBJEKTID),
  CONSTRAINT FKTESTDokHiera FOREIGN KEY (OBJEKTID)
        REFERENCES TESTDOK (OBJEKTID)
        ON DELETE CASCADE,  
  CONSTRAINT FKTESTDokHiera2 FOREIGN KEY (SUPEROBJEKTID)
        REFERENCES TESTDOK (OBJEKTID)
        ON DELETE CASCADE
)ORGANIZATION INDEX PCTTHRESHOLD 5
tablespace user_tg pctfree 10
/

What is wrong?

Thanks
 
 

If i have say an Index organized table 'T' who has columns a,b,c,d and declared a,b to be the primary key.

What i really needed columns a,b,c to be the primary key. Is there an alter table command that allows me to alter IOT and add another column in the composite primary key ?

Many thanks.

If i have say an Index organized table 'T' who has columns a,b,c,d

this time i've a,b,c as primary key in that order also.

And i found out that majority of my queries has a,c in the predicate will the composite key of a,b,c be useful ? say compared to composite key a,c ?

Half of my queries are a,c and the other half is a,b

Will making a,b,c as composite primary key work for both ? a,b has a little bit more queries than a,c.

I tried in our developmental server the effects of using an IOT as compared to the heap organized table and the difference via tkprof was enough to convince me to implement it on our production server.

It's a 24x7 system and downtime should be kept to a minimum.

How will i freeze the said table ? is there a 'alter table <tablename> read only command can't seem to find it on 8i. Or will the CTAS freeze the source table ?

I was planning to do the following on our off-peak times.

1. rename the orgininal table to <temp>- so no further changes would be introduced
2. create table <original name> as select from <temp>

I did check for other object dependencies and found none. The table's pk are not enforced as fk on other tables and the said table doesn't contain any columns defined as fk's.

in one session

lock table t in exclusive mode;

in another, copy it. 

then in the fist, drop it and rename the other.


in 8i, if you have queries of the type a,b and a,c, you'll probably want to index

a,b,c
c


the reason only "c" is because all indexes on an IOT have the primary keys in them already.  consider:

ops$tkyte@ORA817DEV> create table t ( a int, b int, c int, d char(20), primary key (a,b,c) ) organization index;

Table created.

ops$tkyte@ORA817DEV> insert into t
  2  select rownum, -rownum, rownum*2, 'x'
  3  from all_objects where rownum <= 5000;

5000 rows created.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create index t_idx on t(c);

Index created.

ops$tkyte@ORA817DEV> analyze table t compute statistics for table for all indexes for all indexed columns;

Table analyzed.

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> set autotrace traceonly explain
ops$tkyte@ORA817DEV> select a,c from t where c = 44 and a = 22;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=6)



ops$tkyte@ORA817DEV> set autotrace off


See how it was able to use the index in its entirety to answer the question?  it did not need to goto the table to get A, it was already there. 

why should we rebuild indexes?. You're against index rebuild. Refer your answer

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6601312252730, <code>

Your comment please.

Tom,

Thank you for the great services you provide.

I created IOT as such for use as a drop down list

create table category
(name VARCHAR2(50)
CONSTRAINT category$name$nn NOT NULL
,display_yn VARCHAR2(1)
CONSTRAINT category$display$nn NOT NULL
,CONSTRAINT category$pk
PRIMARY KEY (name)
)
organization index tablespace indx
including display_yn overflow;

alter table category add constraint category$display$ck01
check(display_yn in ('Y','N'));

create unique index category$name$x on category(UPPER(name))
tablespace indx;

My questions are:

1. Basically, there are two indexes for the table on the same column. Is this efficient and how can I get around with using only one index?

NEREUS@mdl1> select index_name, index_type from dba_indexes
2 where owner='NEREUS' and index_name like 'CATEGORY%';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
CATEGORY$NAME$X FUNCTION-BASED NORMAL
CATEGORY$PK IOT - TOP

2. How do I name the OVERFLOW table?

NEREUS@mdl1> select table_name, iot_name from dba_tables
2 where owner='NEREUS'
3 and table_name='CATEGORY'
4 or IOT_NAME='CATEGORY';

TABLE_NAME IOT_NAME
------------------------------ -----------------------------
CATEGORY
SYS_IOT_OVER_27251 CATEGORY

Any recommendation you can make for performance design as well would be great.

Tom, does oracle use PK value to locate the row fast in IOT? it does not store rowid as conventional btree index does. Could you explain the underlying mechanism? Thanks.

the "rowid" is a universal rowid -- not a real rowid rowid.

as such, it actually incorporates the primary key value.  Consider:

ops$tkyte@ORA920> create table t ( x varchar2(50) primary key ) organization index;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> insert into t values ( 'xxx' );

1 row created.

ops$tkyte@ORA920> insert into t values ( rpad('x',50,'x') );

1 row created.

ops$tkyte@ORA920> select x, rowid from t;

X                                                  ROWID
-------------------------------------------------- -------------------------------------------------------------------------------
xxx                                                *BAGAAjoDeHh4/g
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx *BAGAAjoyeHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHh4eHj+




see how big a rowid can be... 

Tom, if I create an index on a column other than the primary key columns in IOT, what does the index just created on the column store? Does it store what you have shown in the demo? I don't know much about using IOT so I would appreciate your time in explaining this concept. Thanks much.

Tom, does it mean that the index entry for the indexed column (secondary index) in IOT will have key value, primary key value, rowid (logical?)

thanks.

yes, note how in the following example, only the index T_IDX is accessed -- never the table.  even tho the index is on Y and Y alone

ops$tkyte@ORA920> create table t ( x int primary key, y int ) organization index;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> create index t_idx on t(y);

Index created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dbms_stats.set_table_stats( user, 'T', numrows => 100000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> set autotrace traceonly explain
ops$tkyte@ORA920>
ops$tkyte@ORA920> select x from t where y = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=26000)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1000 Bytes=26000)



ops$tkyte@ORA920> select y from t where y = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=13000)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1000 Bytes=13000)



ops$tkyte@ORA920> select rowid from t where y = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=30000)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=1000 Bytes=30000)



ops$tkyte@ORA920>
ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> 

ops$tkyte@ORA920> exec dbms_stats.set_table_stats( user, 'T', numrows => 100000
);

Tom, what does set_table_stats do? Thanks.

Is it used to study the execution plan for certain number of rows even though the table is empty? Thanks.

Hi Tom,

I am considering converting some "big and lean" tables of more than 10000 rows to IOT and I found it helps the performance a lot.

In your opinion, do you think it makes sense to convert tables of less than 10000 rows to IOT? Will it give me much benefit, compared to using heap table with indexes?


Thanks.

1)When to use ALTER iot_table COALESCE and when ALTER iot_table MOVE ONLINE? Is there some circumstance when using of COALESCE is reasonable?

2) If there is an index on iot_table and we do MOVE ONLINE do we need to rebuild also that index?


Thank you, Jan

1) use coalesce to "compact" the iot.

use move to MOVE it. total rebuild.  2x the space.

2) indexes on iots are based on primary keys -- the move doesn't change the key, so no.


ops$tkyte@ORA920LAP> create table heap( x int primary key, y int );

Table created.

ops$tkyte@ORA920LAP> create index heap_idx on heap(y);

Index created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create table iot( x int primary key, y int ) organization index;

Table created.

ops$tkyte@ORA920LAP> create index iot_idx on iot(y);

Index created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into heap select rownum, rownum from all_objects;

30694 rows created.

ops$tkyte@ORA920LAP> insert into iot  select rownum, rownum from all_objects;

30694 rows created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> analyze table heap compute statistics for table;

Table analyzed.

ops$tkyte@ORA920LAP> analyze table iot  compute statistics for table;

Table analyzed.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> set autotrace on explain
ops$tkyte@ORA920LAP> select * from heap where y = 55;

         X          Y
---------- ----------
        55         55


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=307 Bytes=3684)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HEAP' (Cost=2 Card=307 Bytes=3684)
   2    1     INDEX (RANGE SCAN) OF 'HEAP_IDX' (NON-UNIQUE) (Cost=1 Card=123)



ops$tkyte@ORA920LAP> select * from iot  where y = 55;

         X          Y
---------- ----------
        55         55


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=307 Bytes=4298)
   1    0   INDEX (RANGE SCAN) OF 'IOT_IDX' (NON-UNIQUE) (Cost=1 Card=307 Bytes=4298)



ops$tkyte@ORA920LAP> set autotrace off
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> alter table heap move;

Table altered.

ops$tkyte@ORA920LAP> alter table iot  move online;

Table altered.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> set autotrace on explain
ops$tkyte@ORA920LAP> select * from heap where y = 55;
select * from heap where y = 55
*
ERROR at line 1:
ORA-01502: index 'OPS$TKYTE.HEAP_IDX' or partition of such index is in unusable state


ops$tkyte@ORA920LAP> select * from iot  where y = 55;

         X          Y
---------- ----------
        55         55


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=307 Bytes=4298)
   1    0   INDEX (RANGE SCAN) OF 'IOT_IDX' (NON-UNIQUE) (Cost=1 Card=307 Bytes=4298)



ops$tkyte@ORA920LAP> set autotrace off


the heap table needs to have indexes rebuilt (rowids changed) the iot does not 

Hi,

SQL> Select Tablespace_name,block_size from Dba_Tablespaces
  2  /

TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
....
TEST                                 2048
SIXTEEN_K                           16384

6 rows selected.

SQL> Alter user j default tablespace test;

SQL> create table a(  a  varchar2(700) primary key )
     organization index;

Table created.

SQL> alter table a modify (a   varchar2(4000));
alter table a modify (a   varchar2(4000))
                      *
ERROR at line 1:
ORA-01404: ALTER COLUMN will make an index too large  <<--

SQL> Select index_name,table_name,tablespace_name from user_indexes;

INDEX_NAME         TABLE_NAME  TABLESPACE_NAME
------------------ ----------- ----------------
SYS_IOT_TOP_25958  A           TEST     <<--- 2k Tablespace

SQL> Alter table a move tablespace sixteen_k;

Table altered.

SQL> select index_name,table_name,tablespace_name from user_indexes ;

INDEX_NAME        TABLE_NAME TABLESPACE_NAME
----------------- ---------- ---------------
SYS_IOT_TOP_25958 A          SIXTEEN_K

SQL> alter table a modify (a   varchar2(4000));

Table altered.

SQL> 
SQL> alter table a move tablespace test ;   <<--- Moving back in 2k

Table altered.

SQL> select index_name,table_name,tablespace_name from user_indexes ;

INDEX_NAME        TABLE_NAME TABLESPACE_NAME
----------------- ---------- ---------------
SYS_IOT_TOP_25958 A          TEST   ??????

how can an index can have a 4000+ bytes within 2k block size
for a single entry.


 

IOT seems to be nice way to organize data in my case. We have 6 million rows in a table, which is always accessed via one index, only 5 columns and 3 are already in index, so IOT seems to be perfect solution. But i have two concerns, how can i actually transfer this table to IOT?
if i create new table and do: insert into ... select * ... on 6 million rows it will eventually crash (after eating up all rollback segments) so is there any fancy way to change the organization. Or if not, if i export table, and drop and create with the same name, but different organization, will import work?
Is there any reason (intensive insert/update...) or any other reason why i should _not_ use IOT?
And one more question, can you partition IOT? does it make sense at all?
Thanks!
Jelena

Hi Tom,
thanks for your answer, it's really helpfull.
I was worried that i have to do
"insert into ... select..."
on 6 mil rows, which would mean for sure some downtime for our production (since we are running 7x24 system, its not so easy to organize) "Create table ... as select" looks perfect!
Besides that, you said that it makes sense to partition IOT (not in my case but in general). My question is: since it's index organized table, and if you partition it, does oracle create "index organization" in each partition (ie the effect of local index) or it's still organized as global index, and in that case what's the use of partitioning?
Thanks,
Jelena

hi Tom,
on a 9iR2, I have a thin (3 column) table of about 1 billion rows and growing fast. I'm considering a range partitioned, compressed, IOT scenario.

Is this asking too much? :-)
I've already build the partitioned IOT table and it looks like the performance (and not only) is greatly improved, from many aspects which I won't dwell on.

I'll do some tests on the feasibility/speed etc of the COMPRESSED scenario (on top of the above) but my main question is IF (if presuming this yields better results) this is a STABLE long term, "production" solution, ie, if there are no known bugs, "gotchas" or otherwise issues (for this scenario) that would be considered as major showstoppers or of major future potential risk.

hi Tom,
thanks much for the feedback but I want to make certain I've explained myself clearly, as this is crital for our environment:

what I want it an INDEX ORGANIZED, RANGE PARTITIONED AND COMPRESSED table, all in one, like below (I'll find the compressed syntax somewhere) [and thanks for the tip on the 'first repetitive column']

It sounded for a second, if I understood right, that you were advocating either IOT or a partitioned table but I was thinking of wanting both AND the compression and furthermore was concerned about long term chances of corruption, etc. We have plenty memory, bazillion of RAM so the compression time would not be of concern, I believe (but still need to run a benchmark). I'm more concerned about corruption and the like, PLUS if I issue a TRUNCATE (which incidentally I have yet to get the syntax on the truncation of a partition) if I have to rebuild the ENTIRE table or just that IOT'ed partition :-)

Basically, has the above scenario been approached by any major companies with success and little administrative headaches.
:-)


create table myparttable
(
field1 number(10),
field2 number(10),
field3 number(10),
constraint table_pk primary key (field1,field2,field3)
organization index nologging
partition by range(field1)
(partition p7 values less than (8) tablespace yadayada.....

This alone will **vastly** improve our processing however, for the kicker, are there new schema/schematic/storage features in 10g (other than compiled PL/SQL which we try to use as little as we can-- mostly pure sql or analytical funcs for speed purposes) that would be worth considering to make this even more of a speed deamon as a "dream of a point in time" where we'd be swamped processing tens if not hundreds of billions of operations.

hi Tom,
thx a bunch for all the info.
I've compressed the range partitioned IOT'ed table (with the most repeatable value columns first) and yeah, it compresses to about 1/2 the size which is really cool. Now, one thing that I don't understand is why the actual cost (of various simple operations; after 'analysis' of partitions) is about a thousand-fold higher than on the non-compressed counterpart table.

Do I need to be looking into histograms and the like or is there something else, more specific compression-related that I'm overlooking?

I'll play in the next few months w/ 10g, just for kicks but thought I'd inquire about this on 9iR2 as this is the release we're deploying on next.

do you have an example of what you mean?  I show full scan and index scan costs that are lower for the compressed, partitioned IOT -- can you help me make this example mimick what you are seeing?

ops$tkyte@ORA9IR2> create table t1
  2  ( object_type, owner, object_name, object_id,
  3    constraint t1_pk primary key(object_type,owner,object_name))
  4  partition by range (object_type)
  5  ( partition values less than ( 'S' ),
  6    partition values less than ( MAXVALUE )
  7  )
  8  as
  9  select object_type, owner, object_name, max(object_id)
 10    from all_objects
 11   group by object_type, owner, object_name
 12  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create table t2
  2  ( object_type, owner, object_name, object_id,
  3    constraint t2_pk primary key(object_type,owner,object_name))
  4  organization index compress 2
  5  partition by range (object_type)
  6  ( partition values less than ( 'S' ),
  7    partition values less than ( MAXVALUE )
  8  )
  9  as
 10  select * from t1
 11  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> analyze table t1 compute statistics for table for all indexes;
 
Table analyzed.
 
ops$tkyte@ORA9IR2> analyze table t2 compute statistics for table for all indexes;
 
Table analyzed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=30543 Bytes=1374435)
   1    0   PARTITION RANGE (ALL)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=23 Card=30543 Bytes=1374435)
 
 
 
ops$tkyte@ORA9IR2> select * from t2;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=30543 Bytes=977376)
   1    0   PARTITION RANGE (ALL)
   2    1     INDEX (FAST FULL SCAN) OF 'T2_PK' (UNIQUE) (Cost=16 Card=30543 Bytes=977376)
 
 
 
ops$tkyte@ORA9IR2> select object_id from t1 where owner = 'X' and object_type = 'Y' and object_name='Z';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=44)
   1    0   TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'T1' (Cost=2 Card=1 Bytes=44)
   2    1     INDEX (UNIQUE SCAN) OF 'T1_PK' (UNIQUE) (Cost=1 Card=16890)
 
 
 
ops$tkyte@ORA9IR2> select object_id from t2 where owner = 'X' and object_type = 'Y' and object_name='Z';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=32)
   1    0   INDEX (UNIQUE SCAN) OF 'T2_PK' (UNIQUE) (Cost=1 Card=1 Bytes=32)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
 

Hi Tom,
I was reading your book Expert one on One Oracle.I read about the four main conditions when i can possibly use IOT.Say i have an Address table where i store Address information both for my customers as well as sales force - the structure of the table is like this :
AddressId NUMBER,
CustomerId NUMBER,
SaleForceId NUMBER,
Street1 VARCHAR2(100),
Street2 VARCHAR2(100),
Street3 VARCHAR2(100),
AddressType CHAR(1),
City NUMBER,
Zip VARCHAR2(10),
State NUMBER,
Country NUMBER

Now am i supposed to use IOT in this case.Or traditional heap organized table with AddressId as my primary key and a bitmap index on AddressType which can have possibly three value and my Address table can have millions of rows so the cardinality is < 0.1.What is your suggestion?

hi Tom,
I was able to replicate your test/results almost identically; I rebuilt my table and indeed the COST went down substantially, well below the cost of the uncompressed table (when I analyzed the index as well, in addition to the table, I think that was the issue) HOWEVER, the actual elapsed time of a simple select or a count(*) would not finish in minutes (vs. milliseconds). I guess toying w/ TKPROF might come in handy. Also, the funny thing is that the space taken by a simple compressed partition vs the identical non-compressed partition slightly increased, even though I started the table definition w/ the most frequent value columns first.

That being said, I don't think we'll go to compression this release as we're sometimes doing intensive write IO and I don't think it's feasible, besides the fact that we're doing the upgrade v. soon and we're now at 'crunch time' and a lot more research is required on my part to get make this a strong case for our management.

I'll toy w/ compression more in the future, perhaps also on 10g however for now, Partitioned IOT should give us substantial performance increase;

I will come back w/ more feedback/questions, in the next few weeks I hope, after I put out all the current fires and I document myself a bit more :-)


thx much for all your help & looking forward for your book on 10g :-)
Cosmin

Excerpt
"Great Info  August 23, 2003 
Reviewer:  Samuel Bernard  from Salt Lake City 

Tom, if I create an index on a column other than the primary key columns in IOT, 
what does the index just created on the column store? Does it store what you 
have shown in the demo? I don't know much about using IOT so I would appreciate 
your time in explaining this concept. Thanks much. 


Followup:  
an index on an IOT always includes the primary key. 
 

Bookmark Review | Bottom | Top 
index key contains .....  August 23, 2003 
Reviewer:  David  from Austin, TX 

Tom, does it mean that the index entry for the indexed column (secondary index) 
in IOT will have key value, primary key value, rowid (logical?)

thanks. 


Followup:  
yes,"

Pls. review the following :

SQL> create table iot_table(x number, y number, constraint pk_iot_table primary key (x,y));

Table created.

SQL> create index idx_iot_table on iot_table(x, y);
create index idx_iot_table on iot_table(x, y)
                                        *
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> create index idx_iot_table on iot_table(y);

Index created.

SQL> drop index idx_iot_table;

Index dropped.

SQL> create unique index idx_iot_table on iot_table(y);

Index created.

SQL> disc
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.3.0 - 64bit Production

Q1 : If secondary index on an IOT table always contain primary columns, then creating secondary index in first and (second or third) case shouldn't have given the same error, which was raised in first case, may be I got it all wrong.

Q2 : Since IOT tables are indexes in reality (I assume), What difference on structural level they have in comparision with indexes on columns in case all columns of my IOT table are primary key, such as

Table IOT(x number, y number), PK : x,y
Table HEAP(x number, y number), PK : x,y

What structural level difference do IOT table and Primary key index of HEAP have ?

Thanks 

hi tom,
i'm not aware of this index organized tables and also the concepts of optimization.and i have to attend the interview.can u please kindly let me know those concepts
please tom send me some useful links

Hi Tom,

1) If I have a very large IOT table with the following PK (id number, booking_date date).
2) I'm querying for a list of IDs.
3) The list of IDs can be huge - several thousands.
4) IDs are random (1, 4000288, 1200, 2, 1924562, etc.)

My query looks like SELECT * FROM LARGE_IOT WHERE ID IN (?, ?, ?, ..., ?);

Would it help if I sort the IDs first? Or would it not matter at all.

Thanks,
-Peter

Tom
Why Oracle has introduced the Index Organised Tables? What is it's advantage over the normal table.

Thanks

Thanks

I have one question relating IOT & Clustered Table.

Q1. Since IOT tables group data based on primary key, and single table cluster too organizes data on cluster key, given a choice what are the pros & cons of using one over the other ?

Hey Tom

I am new to IOT's. Only read about them never used them. Looking at the advantages it offers do you think we should always use IOT's & not opt for regular tables (with indexes).

What I mean to ask is when are IOT's not to be used. In which scenarios will IOT's not be a good candidate to choose over regular tables (with indexes)

Rgrds

IOT - data ARE ordered by PK - good for index range scan (e.g. ... WHERE pk BETWEEN 1 AND 100) - but inserts/updates are probably more expensive (due to ordering - but I did not tested it)

Single Cluster - data ARE NOT ordered - good enough for equality conditions (e.g. ... WHERE cluster_key = 50), worse for index range scan (data are not ordered by cluster key), but probably faster inserts/updates (again - I did not tested it)

Thanks Tom & Jan

Tom,

I have created two tables one is index organized table
and the other normal heap table

sql>create table IOT(x int primary key)
2 organization index
3 /

sql>create table NON_IOT(x int primary key)
2 /

When I query user_table I do not see the tablespace name for the table IOT.

sql> select table_name, tablespace_name from user_tables
2 where table_name in ('IOT', 'NON_IOT')
3 /

TABLE_NAME TABLESPACE_NAME
------------------- ------------------------------
IOT
NON_IOT USERS

Why does it not display the tablespace_name from Index organized tables?.

My database has several "intersection tables" i.e. resolver tables for many-to-many relationships. These tables have the (surrogate) PKs for upto 3-4 tables as their composite PK. So, this intersection table has a 3-column composite PK plus an additional 3-4 columns like timestamp, updated by, note, etc.

Since they dont contain any real, usable information, they are *always* used by joining with the 3 real tables by each of the PK columns.

1. Would using IOTs for these intersection tables make sense?

2. Since they are always joined, would that negate the benefits of using IOTs, i.e. would clusters be more suitable here?

3. I read that "skinny" tables benefit from being IOT so that no overflow segment, etc are needed.

How skinny is skinny? Upto what row length?

Thanks

<quote>
pk's for 3-4 tables? really? interesting, association objects in my experience are for 2 tables. I don't know as if I've seen a 3/4 way one.
<quote>

Well, it is a association object for a many-many-many relationship! :) Or think of it as a "fact" table. The dimensions are A, B and C. The association/fact table is a_b_c (with composite pk as (a_pk, b_pk, c_pk)

<quote>
Your "note" field opens an area of concern -- and could be a reasonable overflow column as I doubt it is accessed very often at all (so the retrieval price isn't an issue). It sounds "wide", perhaps you could model it as an out of line CLOB even
<quote>

You hit the nail on the head. This "note" field is just defined in the model, but most apps dont use it, it is NULL for >95% of the rows.

1. How can I define it as a out of line CLOB?

2. What are the similarities & differences between using overflow segments and using this out-of-line clob technique?

Thanks

if I think of it as a fact table, I'm not liking the idea of a IOT anymore.  

1) using the lob storage clause

ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (X NUMBER,
  3   Y CLOB)
  4  LOB (Y) STORE AS
  5  (TABLESPACE USERS
  6   DISABLE STORAGE IN ROW
  7   CHUNK 8192
  8   PCTVERSION 10
  9   NOCACHE )
 10  /
 
Table created.
 
2) they are similar in that any dereference of the lob locator will mandate a read elsewhere in another segment, like reading an overflow would - but the lob allows for arbitrarily long notes, not just 4000 bytes. 

"if I think of it as a fact table, I'm not liking the idea of a IOT anymore"

Why?

All my statements above are still the same, so just because it is a fact table (2+ tables) and not an association table (2 tables), why does it make you not like the idea of an IOT?

As per conventional DW theory, fact tables are always
joined with the dimension tables (since they have non-intelligent surrogate keys), so IOTs would make perfect sense, since the index structure itself contains all the data, no table access by rowid needed!

Thanks

"the DIMENSIONS make great sense for IOTs"

What?! Typical DIMENSION tables are very wide, contain lots of descriptive attributes and would lead to heavy use of IOT overflow segment! IMHO dimension tables are a bad choice for IOT

"an IOT is best come at from "one" direction (the key direction"

And in the case of dimensions (most likely having surrogate keys), it would never be come from the key direction! So I am surprised that you suggest using IOTs for dimension tables!

You are right, thats why it is so frustrating to discuss data warehousing-type things with others. Every person has a different thing in mind when they say DW/DSS, etc.

You are talking about the quinessential "time dimension", which is very "skinny".

I am talking about the "real" dimensions in the DW which are the CUSTOMER, INVOICE_DETAIL, etc. CUSTOMER dimension in a CRM-type data warehouse is extremely wide, more than 100 columns and lots of VARCHAR2(2000) columns!

Thanks

OK maybe "quintessential" was not the right choice of words, but the fact remains that TIME dimension is not a real busines-oriented dimension. For a day-grain time dimension, populate heck even 100 years worth, its only 36500 rows and 99% of the companies out there would have the exact same table. Its nothing but a darn calendar, for crying out loud.

The other "real" (for lack of a better term) dimensions have lots of real-world headaches. handling slowly changing dimensions, type1 or type 2 or a hybrid, handling their loading, handling history, etc, etc.

Anyway, I dont want to sidetrack this thread on IOTs into a discussion about data warehousing techniques, but I think still think that most real-world dimensions out there in mid-to-large sized DWs are very wide and NOT suitable for a IOT.

Thanks

"we'll have to agree to "IOT's are a data structure that, once you understand what they are, how they work, what they do well -- may well be used in your OLTP, DW, DSS, reporting, mixed workload, etc etc etc etc systems in a variety of places"

I agree completely.

Few more questions, if you dont mind

I have some table(s) that I think are suitable to be converted to IOTs.

1. How do I go about methodically verifying and quantifying the benefit of doing this? Run all the queries that use the table as a regular table and a IOT, compare the LIOs, elapsed time, etc? Do you have any guidelines?

2. The existing tables have row-level triggers, secondary unique indexes, sps, views on them, etc. Do IOTs support all this or are there any restrictions I have to consider?

3. How can I seamlessly migrate the existing table to IOT while preserving all the things above? Can I use the dbms_redef package for this? How?

Thanks a lot

Hi Tom,

I am trying to create a IOT and rebuild the indexes. I am getting error like ....

SQL> ed
Wrote file afiedt.buf

  1  create table demo_iot
  2      ( object_id int primary key,
  3        oname     varchar2(30),
  4        owner     varchar2(30),
  5        status    varchar2(30) )
  6*     organization index
SQL> /

Table created.

SQL> insert into demo_iot select object_id, object_name, owner, status
  2  from all_objects;

30233 rows created.

SQL> column index_name new_value iname
SQL> ed
Wrote file afiedt.buf

  1  select index_name
  2        from user_indexes
  3*      where table_name = 'DEMO_IOT'
SQL> /

INDEX_NAME
------------------------------
SYS_IOT_TOP_35158

SQL> alter index SYS_IOT_TOP_35158 validate structure;
alter index SYS_IOT_TOP_35158 validate structure
                              *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

..... I am using oracle 9.2.0.4.0 (test database) and having dba privs.

 

<b>that is not a "rebuild" and demo_iot is a "table"</b>

ops$tkyte@ORA9IR2> create table t ( x int primary key, y int ) organization index;
 
Table created.
 
ops$tkyte@ORA9IR2> insert into t select rownum, user_id from all_users;
 
48 rows created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> analyze table t validate structure;
 
Table analyzed.

 
ops$tkyte@ORA9IR2> select name, lf_rows from index_stats;
 
NAME                              LF_ROWS
------------------------------ ----------
SYS_IOT_TOP_136082                     48


<b>
and to "rebuild", you would:
</b>

ops$tkyte@ORA9IR2> alter table t move online;
 
Table altered.

<b>or</b>

ops$tkyte@ORA9IR2> alter table t move;
 
Table altered.


 

Hello Tom,
Is it possible to transform a IOT to Heap Organized Table?
Do you have any way to that?
Bye!

While creating IOT what if I dont give INCLUDING clause but specify PCTTHRESHOLD ?

A couple of posts above, you made the comments:

--
create table t ( id1 int, id2 int, primary key(id1,id2) ) organization index;
create index on t(id2);

lets me go back and forth either way via index only access (the primary key
index, the table, goes id1->id2. the secondary index has the primary key AND is
sorted by id2 so goes id2->id1 without accessing the table)
--

I was testing (in 10gr1) IOT's, and found that even without creating the secondary index on id2, that queries looking for id2 still used the primary key index of the IOT. The difference was that queries looking for id1 did an index range scan (very few lio's) and those looking for id2 used an index fast full (lots more lio's).

But regardless of whether I had id1 = :x or id2 = :x in the predicate, both queries used the iot pk index.

Then, when I created a secondary index on just id2, and re-ran, the optimizer chose the same path and the secondary index was not used. This of course could be due to the size and type of data I was testing with on my slow pc.

ops$tkyte@ORA10G> create table t (id1, id2, primary key(id1,id2) ) organization index
  2  as
  3  select object_id, -object_id from all_objects;
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create index t_idx on t(id2);
 
Index created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set autotrace traceonly explain
ops$tkyte@ORA10G> select * from t where id1 = 5;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=10)
   1    0   INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_73989' (INDEX (UNIQUE)) (Cost=2 Card=1 Bytes=10)
 
 
 
ops$tkyte@ORA10G> select * from t where id2 = -5;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=10)
   1    0   INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=1 Card=1 Bytes=10)
 
 
 
ops$tkyte@ORA10G> set autotrace off
ops$tkyte@ORA10G> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE    10.1.0.3.0      Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
 
 

SQL> 
SQL> create table iop1
  2  (
  3           p1pk         number
  4           ,data         char(100)
  5           ,constraint iop1 primary key (p1pk)
  6  )
  7  ;

Table created.

SQL> 
SQL> insert into iop1 select rownum, 'x' from all_objects where rownum <= 400;

400 rows created.

SQL> 
SQL> create table iop2
  2  (
  3           p2pk         number
  4           ,data         char(100)
  5           ,constraint iop2 primary key (p2pk)
  6  )
  7  ;

Table created.

SQL> 
SQL> insert into iop2 select rownum, 'x' from all_objects where rownum <= 400;

400 rows created.

SQL> 
SQL> create table ioa
  2  (
  3           p1pk         number
  4           ,p2pk         number
  5           ,data         char(20)
  6           ,constraint ioapk primary key (p1pk,p2pk)
  7           ,constraint ioafk1 foreign key (p1pk) references iop1 (p1pk)
  8           ,constraint ioafk2 foreign key (p2pk) references iop2 (p2pk)
  9  )
 10  organization index
 11  ;

Table created.

SQL> 
SQL> create index ioa_idx on ioa(p2pk);

Index created.

SQL> 
SQL> -- intentional cartesian product
SQL> insert /*+ append */ into ioa select p1pk, p2pk, 'x' from iop1, iop2;

160000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user,'iop1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'iop2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'ioa',cascade=>true);

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> explain plan for select * from ioa where p1pk = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 121472429

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |   401 | 11228 |     4   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IOAPK |   401 | 11228 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("P1PK"=1)

13 rows selected.

SQL> 
SQL> set autotrace traceonly statistics;
SQL> --alter session set sql_trace=true;
SQL> select /* ioap1pk */ * from ioa where p1pk = 1;

400 rows selected.


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

SQL> --alter session set sql_trace=false;
SQL> set autotrace off;
SQL> 
SQL> 
SQL> explain plan for select * from ioa where p2pk = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 3782169291

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |   401 | 11228 |   157   (0)| 00:00:02 |
|*  1 |  INDEX FAST FULL SCAN| IOAPK |   401 | 11228 |   157   (0)| 00:00:02 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("P2PK"=1)

13 rows selected.

SQL> 
SQL> set autotrace traceonly statistics;
SQL> --alter session set sql_trace=true;
SQL> select /* ioap2pk */ * from ioa where p2pk = 1;

400 rows selected.


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

SQL> --alter session set sql_trace=false;
SQL> set autotrace off;
SQL> 
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0    Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> 
SQL> spool off;
 

No, it is a significantly different thing here.

the question the optimizer was asking itself was "should I fast full scan the index to find the <b>400</b> rows, or should I do an index range scan with <b>400 MORE index range scans</b>"


It found it cheaper to fast full scan the index rather than do 401 index range scans (1 range scan to find p2pk = 1, 400 more to pick up the rows again to get "data")

apples and oranges here.


lower the number of rows returned by p2pk = 1

ops$tkyte@ORA10G> create table ioa
  2  (
  3           p1pk         number
  4           ,p2pk         number
  5           ,data         char(20)
  6           ,constraint ioapk primary key (p1pk,p2pk)
  7  )
  8  organization index
  9  ;
 
Table created.
 
ops$tkyte@ORA10G> create index ioa_idx on ioa(p2pk);
 
Index created.
 
ops$tkyte@ORA10G> insert /*+ append */ into ioa select p1pk, rownum, 'x' from iop1, iop2;
 
160000 rows created.


and it'll:

ops$tkyte@ORA10G> select /* ioap2pk */ * from ioa where p2pk = 1;
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=29)
   1    0   INDEX (UNIQUE SCAN) OF 'IOAPK' (INDEX (UNIQUE)) (Cost=3 Card=1 Bytes=29)
   2    1     INDEX (RANGE SCAN) OF 'IOA_IDX' (INDEX) (Cost=1 Card=1)
 


quite naturally 

If I increase the size of the data field from char(20) to char(400) in ioa, I get an index skip scan, but still it is of ioapk (and much slower than range scan):

SQL> 
SQL> set echo on
SQL> 
SQL> -- asktom_iot_2.sql
SQL> -- io = indexed organized; p1 and p2 are entities; ioa is their association
SQL> 
SQL> drop table ioa;

Table dropped.

SQL> drop table iop1;

Table dropped.

SQL> drop table iop2;

Table dropped.

SQL> 
SQL> create table iop1
  2  (
  3           p1pk         number
  4           ,data         char(100)
  5           ,constraint iop1 primary key (p1pk)
  6  )
  7  ;

Table created.

SQL> 
SQL> insert into iop1 select rownum, 'x' from all_objects where rownum <= 400;

400 rows created.

SQL> 
SQL> create table iop2
  2  (
  3           p2pk         number
  4           ,data         char(100)
  5           ,constraint iop2 primary key (p2pk)
  6  )
  7  ;

Table created.

SQL> 
SQL> insert into iop2 select rownum, 'x' from all_objects where rownum <= 400;

400 rows created.

SQL> 
SQL> create table ioa
  2  (
  3           p1pk         number
  4           ,p2pk         number
  5           ,data         char(400)
  6           ,constraint ioapk primary key (p1pk,p2pk)
  7           ,constraint ioafk1 foreign key (p1pk) references iop1 (p1pk)
  8           ,constraint ioafk2 foreign key (p2pk) references iop2 (p2pk)
  9  )
 10  organization index
 11  ;

Table created.

SQL> 
SQL> create index ioa_idx on ioa(p2pk);

Index created.

SQL> 
SQL> -- intentional cartesian product
SQL> insert /*+ append */ into ioa select p1pk, p2pk, 'x' from iop1, iop2;

160000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user,'iop1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'iop2',cascade=>true);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'ioa',cascade=>true);

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> explain plan for select * from ioa where p1pk = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 121472429

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |   400 |   159K|    24   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| IOAPK |   400 |   159K|    24   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("P1PK"=1)

13 rows selected.

SQL> 
SQL> set autotrace traceonly statistics;
SQL> --alter session set sql_trace=true;
SQL> select /* ioap1pk */ * from ioa where p1pk = 1;

400 rows selected.


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

SQL> --alter session set sql_trace=false;
SQL> set autotrace off;
SQL> 
SQL> 
SQL> explain plan for select * from ioa where p2pk = 1;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 2253818249

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |   400 |   159K|   415   (1)| 00:00:05 |
|*  1 |  INDEX SKIP SCAN | IOAPK |   400 |   159K|   415   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("P2PK"=1)
       filter("P2PK"=1)

14 rows selected.

SQL> 
SQL> set autotrace traceonly statistics;
SQL> --alter session set sql_trace=true;
SQL> select /* ioap2pk */ * from ioa where p2pk = 1;

400 rows selected.


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

SQL> --alter session set sql_trace=false;
SQL> set autotrace off;
SQL> 
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0    Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> 
SQL> spool off;
 

Yep this is a test db on my pc. I'm not sure what my db_cache_size is, but I set sga_target (and sga_max_size) to 128M, so it has got to be small. I initially had them at 64M but kept running into ORA shared memory errors.

Thanks for your feedback on my two questions, it is very helpful. I'm off to do more reading and testing, including forcing use of the index via hint to compare.

Dear Tom,

having read this useful thread I would like to have your opinion on the following

I have the following table structures

create table SSP_CMCL_BREAK_PATTERN (
cmcl_break_pattern_key number (10) not null,
channel_key number (4) not null,
schd_episode_key number (10),
cmcl_break_dt date not null,
start_tm varchar2 (6) not null,
duration number (5,2) not null,
break_type varchar2 (2) not null,
schd_type varchar2 (4) not null,
next_schd_episode_key number (10),
day_type varchar2 (1) not null,
modify_by varchar2 (10) not null,
modify_dt date not null,
prog_break_key number (10) not null,
constraint ssp_cmcl_break_pattern_pk
primary key ( cmcl_break_pattern_key ) ) ;

create table SSP_COMMERCIAL_BREAK (
cmcl_break_key number (10) not null,
cmcl_break_pattern_key number (10) not null,
cmcl_break_narr varchar2 (60),
territory_key number (4) not null,
airtime_status varchar2 (1) not null,
avail_airtime_flg varchar2 (1) default 'y' not null,
unmatched_airtime_flg varchar2 (1) default 'n' not null,
txm_flg varchar2 (1) default 'n',
spl_flg varchar2 (1) default 'n',
modify_dt date not null,
modify_by varchar2 (10),
cert_no number (3),
national_flg varchar2 (1) default 'n' not null,
sequenced varchar2 (1) default 'n',
constraint ssp_commercial_break_uk1
unique (cmcl_break_pattern_key, territory_key),
constraint ssp_commercial_break_pk
primary key ( cmcl_break_key ) ) ;

alter table ssp_commercial_break add constraint scb_fk2
foreign key (cmcl_break_pattern_key)
references stowner.ssp_cmcl_break_pattern (cmcl_break_pattern_key) ;

TABLE_NAME NUM_ROWS
------------------------------ ----------
SSP_CMCL_BREAK_PATTERN 368345
SSP_COMMERCIAL_BREAK 418770

Tables SSP_CMCL_BREAK_PATTERN and SSP_COMMERCIAL_BREAK are accessed
1. through cmcl_break_key and/or cmcl_break_pattern_key
2. often through queries like this
select ....
from SSP_CMCL_BREAK_PATTERN a,SSP_COMMERCIAL_BREAK b
where a.cmcl_break_pattern_key = b.cmcl_break_pattern_key
and a.cmcl_break_dt = <>
OR
select ....
from SSP_CMCL_BREAK_PATTERN a,SSP_COMMERCIAL_BREAK b
where a.cmcl_break_pattern_key = b.cmcl_break_pattern_key
and a.cmcl_break_dt between <> and <>
OR
select ....
from SSP_CMCL_BREAK_PATTERN a,SSP_COMMERCIAL_BREAK b
where a.cmcl_break_pattern_key = b.cmcl_break_pattern_key
and b.cmcl_break_key = <>

These two tables are heavily accessed from front-end applications and background jobs as well.
My question is :

1. Will having SSP_CMCL_BREAK_PATTERN (and/or SSP_COMMERCIAL_BREAK) as IOT improve on the performance?
2. I needs few secondary indexes on SSP_CMCL_BREAK_PATTERN and SSP_COMMERCIAL_BREAK. what could be the perils if I use IOT?

Thanks in advance for your help and guidence.

Regards

Thanks Tom,

I too had the same feeling about secondary indexes on IOT.
I always wondered whether a master-detail type relationship where both table uses surrogate keys as PK will ever benfit much with IOT as in my case above ? Here you would always needs a unique key on the detail table for the FK constraint column.

You view would be much appreciated.

Regards

Tom,

Is there any way to determine when a secondary index on IOT goes stale? Probably then an index-rebuild would be called for..(index-rebuild!!! ;-) probably this would be one good reason).

Regards

2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?

The database should be in ARCHIVE mode if you are going to use RMAN as backup method.


3. How can you tell how much space is left on a given file system and how much space each of the file system's subdirectories take-up?

4. Define the SGA and:

&#8226; How you would configure SGA for a mid-sized OLTP environment?
&#8226; What is involved in tuning the SGA?


5. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?

6. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?

7. How do you tell what your machine name is and what is its IP address?

8. How would you go about verifying the network name that the local_listener is currently using?

9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?

10. What view(s) do you use to associate a user's SQLPLUS session with his o/s process?

11. What is the recommended interval at which to run statspack snapshots, and why?

12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?

13. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.

14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?

15. What do the 9i dbms_standard.sql_txt() and
dbms_standard.sql_text() procedures do?

16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?

17. How would you best determine why your MVIEW couldn't FAST REFRESH?

18. How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?

19. Which dictionary view(s) would you first look at to
understand or get a high-level idea of a given Advanced Replication environment?

20. How would you begin to troubleshoot an ORA-03113 error?

21. Which dictionary tables and/or views would you look at to diagnose a locking issue?

22. An automatic job running via DBMS_JOB has failed. Knowing only that "it's failed", how do you approach troubleshooting this issue?

23. How would you extract DDL of a table without using a GUI tool?

24. You're getting high "busy buffer waits" - how can you find what's causing it?

25. What query tells you how much space a tablespace named "test" is taking up, and how much space is remaining?

26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.

27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle's. What database recovery options are available? Database is in archive log mode.

28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).

29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?

30. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.

Tom,

Is there any workaround for the following error, other than a drop and re-create of the table.

01:56:25 SQL> show rel
release 801070400

01:56:26 SQL> create table t (a number primary key , b number) organization index
01:56:26   2  partition by range(a)
01:56:26   3  (partition t1 values less than (10),
01:56:26   4  partition t2 values less than (40),
01:56:26   5  partition t3 values less than (80),
01:56:26   6  partition t4 values less than (maxvalue))
01:56:26   7  /

Table created.

01:56:26 SQL> alter table t move online initrans 10 storage (buffer_pool keep);
alter table t move online initrans 10 storage (freelists 23 freelist groups 17 buffer_pool keep)
            *
ERROR at line 1:
ORA-14511: cannot perform operation on a partitioned object

Thanks,
 Sai. 

you have to rebuild to get freelist groups but the others can be altered in

ops$tkyte@ORA9IR2> create table t (a number primary key , b number) organization index
  2  partition by range(a)
  3  (partition t1 values less than (10),
  4  partition t2 values less than (40),
  5  partition t3 values less than (80),
  6  partition t4 values less than (maxvalue))
  7  /
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t modify partition t1 initrans 10 storage (freelists 23 buffer_pool keep);
 
Table altered.


so change the storage and then "move" 

Is someone trying to get you to do their exam for them? :-)

I have a big IOT that experiences a lot of physical reads during massive inserts:

CREATE TABLE t (
        member_id INTEGER NOT NULL,
        newsletter_id INTEGER NOT NULL,
        CONSTRAINT t_pk PRIMARY KEY (member_id, newsletter_id) )
ORGANIZATION INDEX;

This table is large -- about 83M rows at the moment. Index structure:

SQL> analyze index T_PK validate structure;

Index analyzed.

SQL> select height, blocks, del_lf_rows from index_stats;

    HEIGHT     BLOCKS DEL_LF_ROWS
---------- ---------- -----------
         4     331792           0

The table experiences massive single-row inserts each and every day. About 1 Million of the following insert can happen within a day by multiple concurrent sessions (about 8-10 concurrent sessions) :

insert into t (member_id, newsletter_id) values (:member_id, :newsletter_id);

Freelists is currently at 20. Freelist groups at 1.

Doing a TKPROF for the above insert statement shows:

********************************************************************************
INSERT INTO T ( MEMBER_ID,NEWSLETTER_ID)
VALUES (:member_id, :newsletter_id)


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.07          2         69          0           0
Execute      1      0.00       0.01          1          2          6           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.08          3         71          6           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 85
********************************************************************************

My questions:

1) Why consistent read for an insert? (based on what I see in tkprof above). I would think it's mostly concurrent read at best?

2) Our 15-min statspack reports often show that the most dominating wait event as "db file sequential read" and it's contributed mainly by the insert statement above. Any suggestion you may have to reduce that? We are thinking of adding another column to the leading edge of the primary key, say, "org_id" (and enable compression) and thus the table would look something like:

CREATE TABLE t (
        org_id INTEGER NOT NULL,
        member_id INTEGER NOT NULL,
        newsletter_id INTEGER NOT NULL,
        CONSTRAINT t_pk PRIMARY KEY (org_id, member_id, newsletter_id) )
ORGANIZATION INDEX compress 2;

We know that the massive inserts happen for member_id's within one or two specific org_id's at a time. Perhaps this new design helps us reduce the physical reads. 

3) Building on #2 above, perhaps we can add partitioning to the org_id column. Since we are using 8.1.7.4, we can only do range partitioning (not hash) on an IOT. But we can work around that. We assume that would help us even more with reducing physical reads?

Thank you very much for any insights. 

Hi Tom,
I have an IOT around 100MB, plus indexes total < 300MB. When I run 'ALTER TABLE ... MOVE', the process eventually gets ORA-03113, and 0RA-04030 error found in user dump file. I constantly check v$process for PGA usage of this session, it keeps growing and reaches 10GB before the process fails.

Why such a small table requires 10GB+ PGA memory, does it look like a bug?

Sincerely

Need example, I have a 200mb IOT:

ops$tkyte@ORA10G> exec show_space( 'SYS_IOT_TOP_80546', user, 'INDEX');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................          24,212
Total Blocks............................          24,576
Total Bytes.............................     201,326,592
Total MBytes............................             192
Unused Blocks...........................             164
Unused Bytes............................       1,343,488
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          66,441
Last Used Block.........................             860
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> @mystat "session pga memory max"
ops$tkyte@ORA10G> set echo off
 
NAME                                                    VALUE
-------------------------------------------------- ----------
session pga memory max                                1874508
 
ops$tkyte@ORA10G> alter table iot move;
 
Table altered.
 
ops$tkyte@ORA10G> @mystat2
ops$tkyte@ORA10G> set echo off
 
NAME                                                        V
-------------------------------------------------- ----------
DIFF
------------------
session pga memory max                              118790732
     116,916,224
 

<b>and did not need 10gb of pga?</b>
 

An August 24, 2004 reader asked: "How do I go about methodically verifying and quantifying the benefit of doing this? Run all the queries that use the table as a regular table and a IOT, compare the LIOs, elapsed time, etc? Do you have any guidelines?"

I had exactly the same question as I read your Expert 1-on-1 chapter/section on IOTs last night. The usually recommended technique of counting LIOs doesn't seem like it would work (e.g. </code> http://asktom.oracle.com/pls/ask/f?p=4950:8:16781446805926749277 <code>) because -- as I recall -- the book says that LIOs won't change. Am I correct in guessing there is no simple means of quantifying the benefits of IOTs without falling back on raw timings on a single user system ??

Hmm, that's what I get for reading late into the night. Now I have the book before me. In 1-on-1 (p. 215) you wrote "Having all of the addresses.... physically located near each other will reduce the amount of I/O... The logical I/O would be the same, the physical I/O could be significantly less." I didn't find any LIO/PIO measurements in the IOT section, so when I read the answer you gave to the August 24, 2004 reader...it seemed to confirm my (groggy) impression.

I subsequently found </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:627128171401 <code>and ran some confirmations that LIO and PIO are measureably different for IOT and heap tables. Thank you!

Hi Tom,

I went to your seminar in Norway the other day, and I really enjoyed it.

In the seminar, you talked a bit about different table types: heap, iot, and hash cluster.

Let's say I have a table DISTANCE that contains distances (in, say, miles) from A to B (between two cities).

create table distance
(from varchar2(20),
to varchar2(20),
distance number);

insert into distance (from, to, distance) values ('OSLO', 'ROTTERDAM', 1000);

etc.

The table will be used frequently to look up the distance between two given cities. The table will initially be populated with a lot of combinations (around 4 million combinations), but there will be more inserts as the users add their own distances.

1) What would be the best physical table type to use? I'm guessing an IOT would be better than a hash cluster, since the table will keep growing, and we don't know the max size?

2) What about compression? There will be a lot of repeating data here, so I guess there will be a huge potential for space saving by compression. Could you give an example of the syntax to enable compression (for an IOT, or other table type depending on your answer to 1, above).

Thanks and keep up the good work!

Hi Tom. I am experiencing some problems trying to move a IOT. DB version is 8.1.7. Please, see below:

SQL> select segment_type, count( * )
  2    from dba_segments 
  3   where tablespace_name = 'PVD' 
  4   group by segment_type;

SEGMENT_TYPE         COUNT(*)
------------------ ----------
INDEX                       2
SPACE HEADER                1

SQL> select 'alter index ' || owner || '.' || index_name || ' rebuild tablespace pvd_tmp;'
  2    from dba_indexes
  3   where tablespace_name = 'PVD';

'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILDTABLESPACEPVD_TMP;'
----------------------------------------------------------------------------------------------------
alter index PV.SYS_IOT_TOP_136545 rebuild tablespace pvd_tmp;
alter index PV.SYS_IOT_TOP_136550 rebuild tablespace pvd_tmp;

SQL> alter index PV.SYS_IOT_TOP_136545 rebuild tablespace pvd_tmp;
alter index PV.SYS_IOT_TOP_136545 rebuild tablespace pvd_tmp
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

As per your suggestion, I tried:


SQL> alter table PV.SYS_IOT_TOP_136545 move online tablespace pvd_tmp;
alter table PV.SYS_IOT_TOP_136545 move online tablespace pvd_tmp
               *
ERROR at line 1:
ORA-00942: table or view does not exist

But received error messages.. could you please tell me what else should I try or is this the normal behaivour ? What I am trying to achieve, as you may notice, is to move this table to another tablespace.

Thank you 

Tom, Thanks.

And sorry to bother you again, but do you know how can I 'move' this SPACE HEADER object ? I have never heard of it before neither I can't seem to find any info about it on the dba views.

Thanks again!

Is there a way to MOVE/COALESCE a partitioned IOT( am using a range partitioned one) in 9i?

Thanks

ops$tkyte@ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date primary key,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  organization index
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  /

Table created.

ops$tkyte@ORA10GR2> insert into t select to_date( '01-mar-2003' ) + rownum, rownum, rownum from all_users;

34 rows created.

ops$tkyte@ORA10GR2> select count(*) from t partition(part1);

  COUNT(*)
----------
        11

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> alter table t move partition part1;

Table altered.

 

Tom,

Does it make sense to gather statistics on an IOT? Wouldn't gathering statistics on the primary key suffice?

IOT always gets accessed through its primary key, right? I cannot build/force an access plan without the PK ... and hence the question.

Thanks,
Rennie

The statement from the beginning of the thread:

"because we know that this empty space in the indexes will not be reused (unlike, when it is reused in table blocks after PCTUSED threshold met in the block)."

Is that statement strictly true? A key has a specific place in an index and if its the same key being inserted again, wont the space be reused?

Thanks

Why is the cardinality off for the partitioned IOT?

SQL> CREATE TABLE tt
  2  (
  3     a int,
  4     b   int,
  5     c int,
  6      d   varchar2(25),
  7      e int,
  8      constraint pk_tt primary key(a,b,c,d,e)
  9    )
 10    organization index compress 3
 11    PARTITION BY RANGE (b)
  (
 12   13      PARTITION part1 VALUES LESS THAN (2) ,
 14      PARTITION part2 VALUES LESS THAN (3) ,
 15      PARTITION junk VALUES LESS THAN (MAXVALUE)
 16    )
 17  /

Table created.

Elapsed: 00:00:00.13

SQL> insert into tt select 1,1,1,to_char(trunc(dbms_random.value(1,999))),trunc(dbms_random.value(1,999))
from all_users;
  2
35 rows created.

Elapsed: 00:00:00.43
SQL> insert into tt select 2,2,2,to_char(trunc(dbms_random.value(1,999))),trunc(dbms_random.value(1,999))
from all_users;  2

35 rows created.

Elapsed: 00:00:00.43

SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL> exec dbms_stats.gather_table_stats( user, 'TT',cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.57
SQL> set autot traceonly exp stat;
SQL> select * from tt where a=1;

35 rows selected.

Elapsed: 00:00:00.39

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=35 Bytes=560)
   1    0   PARTITION RANGE (ALL)
   2    1     INDEX (RANGE SCAN) OF 'PK_TT' (UNIQUE) (Cost=1 Card=35 B
          ytes=560)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       1828  bytes sent via SQL*Net to client
        673  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         35  rows processed
SQL> insert into tt select 1,1,1,to_char(trunc(dbms_random.value(1,999))),trunc(dbms_random.value(1,999))
from all_users;  2
/
35 rows created.


Elapsed: 00:00:00.43

SQL> /

35 rows created.

Elapsed: 00:00:00.40

SQL> /

35 rows created.

Elapsed: 00:00:00.40

SQL> exec dbms_stats.gather_table_stats( user, 'TT',cascade=>true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.58
SQL> select * from tt where a=1;

140 rows selected.

Elapsed: 00:00:00.39

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=88 Bytes=1408
          )

   1    0   PARTITION RANGE (ALL)
   2    1     INDEX (RANGE SCAN) OF 'PK_TT' (UNIQUE) (Cost=1 Card=88 B
          ytes=1408)





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


After another set of insert, commit and stats:

SQL> select * From tt where a=1;

210 rows selected.

Elapsed: 00:00:00.39

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=123 Bytes=196
          8)

   1    0   PARTITION RANGE (ALL)
   2    1     INDEX (RANGE SCAN) OF 'PK_TT' (UNIQUE) (Cost=1 Card=123
          Bytes=1968)





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

You must be using 9i or before.  The default method opt there didn't pick up histograms.  That this is an IOT (or even a partitioned table), with other columns and a primary is a red herring, not relevant to the problem at all.

Consider in 9i:



ops$tkyte@ORA9IR2> CREATE TABLE tt
  2  (
  3     a int
  4  )
  5  /
 
Table created.
 
ops$tkyte@ORA9IR2> insert into tt
  2  select 1
  3    from (select 1 from dual connect by level <= 35);
 
35 rows created.
 
ops$tkyte@ORA9IR2> insert into tt
  2  select 2
  3    from (select 1 from dual connect by level <= 35);
 
35 rows created.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'TT',cascade=>true);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from tt where a=1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=35 Bytes=105)
   1    0   TABLE ACCESS (FULL) OF 'TT' (Cost=2 Card=35 Bytes=105)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select count(*), count(distinct a), count(*)/count(distinct a) guess,
  2         count(case when a=1 then 1 end) count_a
  3    from tt;
 
  COUNT(*) COUNT(DISTINCTA)      GUESS    COUNT_A
---------- ---------------- ---------- ----------
        70                2         35         35
 
<b>that is what the optimizer had to work with given your dbms_stats command - it knows

a) 70 rows
b) two values of A
c) therefore about 35 rows will be returned.... (the guess)</b>

ops$tkyte@ORA9IR2> insert into tt
  2  select 1
  3    from (select 1 from dual connect by level <= 35);
 
35 rows created.
 
ops$tkyte@ORA9IR2> /
 
35 rows created.
 
ops$tkyte@ORA9IR2> /
 
35 rows created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'TT',cascade=>true);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from tt where a=1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=88 Bytes=264)
   1    0   TABLE ACCESS (FULL) OF 'TT' (Cost=2 Card=88 Bytes=264)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select count(*), count(distinct a), count(*)/count(distinct a) guess,
  2         count(case when a=1 then 1 end) count_a
  3    from tt;
 
  COUNT(*) COUNT(DISTINCTA)      GUESS    COUNT_A
---------- ---------------- ---------- ----------
       175                2       87.5        140

<b>Now, it knows:

A) 175 rows
B) still two distinct values for A
C) therefore ABOUT 88 rows will be returned. 

it did not know "140" because the default method_opt is "size 1" - no histograms</b>
 
ops$tkyte@ORA9IR2> insert into tt
  2  select 1
  3    from (select 1 from dual connect by level <= 35);
 
35 rows created.
 
ops$tkyte@ORA9IR2> /
 
35 rows created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'TT',cascade=>true);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * From tt where a=1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=123 Bytes=369)
   1    0   TABLE ACCESS (FULL) OF 'TT' (Cost=2 Card=123 Bytes=369)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select count(*), count(distinct a), count(*)/count(distinct a) guess,
  2         count(case when a=1 then 1 end) count_a
  3    from tt;
 
  COUNT(*) COUNT(DISTINCTA)      GUESS    COUNT_A
---------- ---------------- ---------- ----------
       245                2      122.5        210

<b>you get the picture by now....

Now, in 10g, you would see something totally different.  This is due to what dbms_stats defaults to - size auto.

See

"Why Does My Plan Change?"
for what is going on under the covers here - but basically by runnin the first query with "where a=1", you told the database "I query by A" and the database would on subsequent gathers - collect histograms on A if it wants to (and it did).  So, in 10g, the first run gets the right cardinality ABSOLUTELY BY ACCIDENT - the rest are right "on purpose" because of the histograms</b>


ops$tkyte@ORA10GR2> CREATE TABLE tt
  2  (
  3     a int
  4  )
  5  /
 
Table created.
 
ops$tkyte@ORA10GR2> insert into tt
  2  select 1
  3    from (select 1 from dual connect by level <= 35);
 
35 rows created.
 
ops$tkyte@ORA10GR2> insert into tt
  2  select 2
  3    from (select 1 from dual connect by level <= 35);
 
35 rows created.
 
ops$tkyte@ORA10GR2> commit;
 
Commit complete.
 
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'TT',cascade=>true);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * from tt where a=1;

<b>that query will be remembered by the database - and that it was executed will affect FUTURE gather stats..</b>
 
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    35 |   105 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TT   |    35 |   105 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A"=1)
 
ops$tkyte@ORA10GR2> set autotrace off
ops$tkyte@ORA10GR2> select count(*), count(distinct a), count(*)/count(distinct a) guess,
  2         count(case when a=1 then 1 end) count_a
  3    from tt;
 
  COUNT(*) COUNT(DISTINCTA)      GUESS    COUNT_A
---------- ---------------- ---------- ----------
        70                2         35         35
 
ops$tkyte@ORA10GR2> insert into tt
  2  select 1
  3    from (select 1 from dual connect by level <= 35);
 
35 rows created.
 
ops$tkyte@ORA10GR2> /
 
35 rows created.
 
ops$tkyte@ORA10GR2> /
 
35 rows created.
 
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'TT',cascade=>true);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * from tt where a=1;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   140 |   420 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TT   |   140 |   420 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A"=1)
 
ops$tkyte@ORA10GR2> set autotrace off
ops$tkyte@ORA10GR2> select count(*), count(distinct a), count(*)/count(distinct a) guess,
  2         count(case when a=1 then 1 end) count_a
  3    from tt;
 
  COUNT(*) COUNT(DISTINCTA)      GUESS    COUNT_A
---------- ---------------- ---------- ----------
       175                2       87.5        140

<b>We get the "right" cardinality becaue of the histograms</b>
 
ops$tkyte@ORA10GR2> insert into tt
  2  select 1
  3    from (select 1 from dual connect by level <= 35);
 
35 rows created.
 
ops$tkyte@ORA10GR2> /
 
35 rows created.
 
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'TT',cascade=>true);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select * From tt where a=1;
 
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   210 |   630 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TT   |   210 |   630 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("A"=1)
 
ops$tkyte@ORA10GR2> set autotrace off
ops$tkyte@ORA10GR2> select count(*), count(distinct a), count(*)/count(distinct a) guess,
  2         count(case when a=1 then 1 end) count_a
  3    from tt;
 
  COUNT(*) COUNT(DISTINCTA)      GUESS    COUNT_A
---------- ---------------- ---------- ----------
       245                2      122.5        210

 

Thanks

Hi

I am reviewing an application design, there are many partition tables. The primary key of these partitioned tables are made up many many columns.

For example a table with 7 columns (one of them is BLOB), the PK is composed of 6 columns (except the BLOB field). Is that a good candidate to make it IOT?
There is another table with 12 columns and PK again is composed of 8 columns, not sure if this is another candidate?

These tables are used to store historical data. HP-UX running Oracle 9.2.0.7

RIMMA

Hi

Since IOT are B-Tree structured therefore during a load process I guess it will be slower than a heap table without index correct?

Tom,

How to calculate the space used by a partitioned IOT (not the entire partition, just 1 partition only)? Should the size of overflow segment be considered?

Tom,

A few questions.

1) What is the best approach to analyze a 1 billion rows partitioned-IOT in 10g?

2) Is there a way to measure the used space for a given partition without analyzing the partitioned-IOT?

3) Is it recommended to have 10 columns as part of PK for an IOT table?

Thanks

Dear Tom,

Since I have your book "Effective Oracle by design" and since I used to read the documentation I am aware of the advantages and concept of IOT's.
As a DBA I am involved in the design of very large OLTP databases, I can decide whether a table will be heap either index organized.
In our databases I notice a lot of IOT candidates because of

1. limited row length
2. table access always or almost always by the primary key
3. No updates of the primary key (off course not) almost never updates of the other columns

However

1. insert frequency & concurrency is rather high, let' s say 1 insert per [ 5 sec - 20 sec ]
2. delete frequency will be lower but number of deleted rows will be important ( will be done in batch )
2. since our Java Middle Tier team didn' t want composite primary keys, my candidate IOT's have a sequence based primary key.
3. number of rows in the range [ 5.000.000 - 50.000.000 ]

I think in many cases I can still go for IOT since, and this is my - possible bad - opinion

1. I can move online the IOT with the "alter table iot_name move online" or rebuild with "alter table iot_name coalesce" clause which does not invalidate secundary indexes. ( if the table is not partitioned )
either with the "alter table iot_name modify partition partition_name coalesce" ( if IOT is partitioned )
I think there will be the need to rebuild the IOT now and then since the sequence based primary key "trigger" a "right hand" IOT and as such delete entries at the left site won' t be reused.
What is your opinion please ?

2. Am I right when I think the hash partitioning can partially reduce the hot spot at the right site of the IOT, since there will be n hot blocks ( where n is the number of partitions )
What is your opinion please ?

3. After hours of sql tuning in test databases I believe in hash partitiong and the partition wise join because of equi partitioning of the join columns
(I am aware of the difference between hash and range partitioning with regard to partition wise join)
I have the join between the IOT and heap organized table which are BOTH hash partitioned on the join column.
What is your opinion please ?

4. With IOT' s I always save disk space.
I guess you will always agree with that

5. In your book "Effective Oracle by design" at page 404 I read : "... there are issues with very wide tables in an IOT structure ..."
Let' s say we keep 20.000.000 rows with an avg row length of 100 up to max 200 bytes. Is that a too big for an IOT ? I think no
What is your opinion please ?

6a. Is an insert frequency of let' s say 1 per 10 seconds too high for an IOT ? I think no
6b. Is an insert frequency of let' s say 1 per second too high for an IOT ? I think possible yes
What is your opinion please ?

The motivation behind my question is the lack of practical experience with IOT whereas my limited theoretical knowledge say, ... go for it.

As always thank you in advance and

Respectfull Greetings

Guy Lambregts

Hello Tom,
Any v$ views or x$ tables exist specific to
Index Organized Tables?

I have an extremely large partitioned table (150 Million rows, lowest detail level in a Scorecard engine) that has a unique key & index, but does not qualify for a Primary Key (some of the key fields are NULL).

All queries against this table currently hit the unique index - none go against the table. As such, the table (and associated 20 Gig of space) is useless.

Since the index is currently the only usefull structure, is there a way that I can eleminate the underlying table? Is there some way to create an IOT without an associated Primary Key index?

Thanks for your reply!

Unfortunatly a hash cluster has a limited nubmer of keys and doesn't do well with range scans (which I have many).

I guess I will have to convert the NULL values to something else so that I can use IOTs, and modify all of the retrieval code as necessary.

I just can't stand the thought of that much wasted space...

Hi Tom,

This site rocks!
Some of the tables in our application have the following pattern of columns (just mentioning the relevant ones):

Table test:

col_a
col_b
date1
date2
flag

This is right now a heap table with PK being (col_a,col_b) and unique index on (col_a,date1,date2,flag)
Most of the queries surrounding these tables are accessed via col_a,date1,date2,flag with a range scan...
Like:
SELECT col_a,col_b FROM test
WHERE col_a = (join with another table)
AND (V_Calendar_Dt BETWEEN date1 AND date2)
AND Flag = 'Y'

I dont have a problem with single table access on these tables...
But, I find an enormous performance hit in cases where I have to join these tables to other heap tables just like shown above...

Volume wise, these tables are small with the max record table containing about 100000 records ...
But, when they are joined to other bigger heap tables(which run into millions of records), then it really is a grind to get data back quickly...

I was also considering to convert them to Hash clusters , but, based on the forum discussions here, looks like hash cluster is not a good option since I will
seldom be querying based on the PK...

But, are these kind of tables good candidates for
IOT instead of heap...since, I need to range scan them as well...
based on "AND (V_Calendar_Dt BETWEEN date1 AND date2)" in the query...

Any pros/cons that can be listed would be much help...

Thanks...

Bad query example...
My apologies...
Here it is:
"test" is as explained before...
assume test1 to have col_a to be the PK...

SELECT a.col_a,a.col_b,a.some_other_columns
FROM test a,test1 b
WHERE a.col_a = b.col_a
AND (:Calendar_Dt BETWEEN a.date1 AND b.date2)
AND a.Flag = 'Y'

I did try this out by creating the "test" as IOT and then executed the above query (please note that test1 is still a heap here)...
But, considering all other things such as data returned and cpu time to be equal, I found IOT version query to perform more LIOs than when I tried with both tables being heaps...!

What am I missing here...

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=82 Bytes=4059
0)

1 0 INDEX (UNIQUE SCAN) OF 'SYS_IOT_TOP_59831' (UNIQUE) (Cost=
1 Card=82 Bytes=40590)

2 1 INDEX (RANGE SCAN) OF 'XAK1CFD_IOT' (UNIQUE) (Cost=1 Car
d=33)

Here , XAK1CFD_IOT is the secondary unique index that I created on col_a,date1,date2,flag.(This definitely needs to be enforced in our application)
SYS_IOT_TOP_59831 is the PK on col_a,col_b...

Now, I do know that when it comes to secondary indexes, IOTs are a dicey...But, i have to have that secondary unique index ...no helping that...
Any pros/cons that can be listed as to where exactly I might see a gain in using IOT/index clustered tables/hash clusters would be much help...

where a.col_a = b.col_a
  and a.date1 <= :c
  and b.date2 >= :c
  and a.flag = 'Y'

Hi Tom,

I am really sorry for the typo in my query from my end that caused a mis-interpretion from you regarding the relationship between the 2 tables : test,test1

"test" as before has columns:
col_a
col_b
date1
date2
flag

col_a and col_b being PK...
col_a,date1,date2,flag have a unique index on them...

"test1" just has one main column(in addition to other columns that are usual attributes) col_a to be the PK...
Its more like a parent child relation with test1.col_a being referenced as a foreign key in test...

But, the correct query is:

SELECT a.col_a,a.col_b,a.some_other_columns
FROM test a,test1 b
WHERE a.col_a = b.col_a
AND (:Calendar_Dt BETWEEN a.date1 AND a.date2)
AND a.Flag = 'Y'

Please note the change here: both date1 and date2 belong to test...

Now, can you please answer my earlier question if this is a possible candidate for IOT?
Can test1 be a heap if the above is possible or should it be some thing like a hash cluster where predominantly things get accessed by PK(test1.col_a)?

But, LIO wise, I could not see much of a difference in performance between test being a heap or IOT...
Our main performance hits so far have stemmed from the fact when tables like test,test1 are joined with other huge volume intensive tables that run in millions...

Left alone , just a simple join among test and test1 are fine ...but, we seldom use them like that...

Hi ,
I am new to Oracle and was wondering can IOT take up more space than heap table. 
I have a situation. 

SQL> select count(*) from dm_message_route_s;

  COUNT(*)
----------
    353401

SQL> select num_rows,blocks,empty_blocks,avg_row_len,avg_space,num_freelist_bloc
ks from user_tables where table_name='DM_MESSAGE_ROUTE_S';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- ---------- -------------------
    353401       2346           86          62         68                   0


SQL> select sum(blocks)  from (select extent_id,bytes,blocks from user_extents w
here segment_name='DM_MESSAGE_ROUTE_S' and segment_type='TABLE');

SUM(BLOCKS)
-----------
       2432

Space used by table data only = 2346 * 8192 - 2346*68 = 19072980 = 18Mb.

SQL> create table dm_message_route_iot(r_object_id,addr_id,message_object_id, route_bit_flags,route_type,i_is_replica,i_vstamp, constraint pk_route_iot primary key(r_object_id)) organization index overflow tablespace dm_oraperf_docbase as (
select * from dm_message_route_s);

SQL> select bytes,blocks,extents from user_segments where segment_name='PK_ROUTE
_IOT'
  2  ;

     BYTES     BLOCKS    EXTENTS
---------- ---------- ----------
  28311552       3456         42

Space allocated to the index segment not necessarily the space on disk = 27Mb.

SQL> select num_rows,blocks,empty_blocks,avg_row_len,avg_space,num_freelist_bloc
ks from user_tables where table_name='DM_MESSAGE_ROUTE_IOT';

  NUM_ROWS     BLOCKS EMPTY_BLOCKS AVG_ROW_LEN  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ---------- ------------ ----------- ---------- -------------------
    353401                                  64
Am I doing this right. I know I am calculating the IOT size from index, is this the right way. 

Hi,
You said that IOT= HEAP+INDEX ON PRIMARY KEY.
and asked me why I didnt add the size of the primary key.
Doesnt the query select bytes,blocks,extents from user_segments where segment_name='PK_ROUTE
_IOT'

Do exactly that where PK_ROUTE_IOT was the primary key on the table.
Is the value that I get 27Mb right value.
Can you give me an example so that I can learn to calculate the size properly?

thanks
Mohnish

ops$tkyte%ORA10GR2> create table t_heap (
  2  OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
  3  LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, constraint t_pk primary key(object_id) )
  4  as
  5  select * from all_objects;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t_iot (
  2  OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
  3  LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, primary key(object_id) )
  4  organization index
  5  as
  6  select * from t_heap;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select segment_name, segment_type, blocks from user_segments ;

SEGMENT_NAME                   SEGMENT_TYPE           BLOCKS
------------------------------ ------------------ ----------
SYS_IOT_TOP_56193              INDEX                     768
T_PK                           INDEX                     120
T_HEAP                         TABLE                     768

Hi Tom

I am wondering why a IOT table does not need a normal rowid, but has a urowid?
Is it because rowid only exists for a heap table?

Thanks!

In our current production environment, we have 10 IOT tables for which 100% stats are gathered every day which takes a couple of hrs. What is the best way to maintain stats for IOTs ?

I also have a requirement to consider about the best method of gathering statistics on IOT tables. From your latest reply above, you said that "it depends". What does this depend on ? What do we have to consider and how do we determine the best method to do stats on IOT tables. Need some help / guidance.

Thanks.

Hi Tom

I read somewhere that "we cannot create unique constraint in IOTs". { URLs : http://www.dbasupport.com/oracle/ora8/iot.shtml ; http://books.google.co.in/books?id=xxx0KAwY_ZMC&pg=PA184&lpg=PA184&dq=unique+constraint+iot+oracle&source=bl&ots=4DmjBEq-4z&sig=mVrIRgfKGuueEU5lu6dd861NPeA&hl=en&ei=oS31SfjlENCAkQXi2_juCg&sa=X&oi=book_result&ct=result&resnum=5 }But I found it to be false in 9i R2 database. I googled for these things but not able to come to any conclusion. Could you verify the statement ? Is it true for lower versioned databases ?

Thanks for your Follow-Up :)

Dear Tom,

1. Is it possible to transform range partitioned table to Index Oraganised table?

Thanks very much

ops$tkyte%ORA10GR2> CREATE TABLE t1
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 11  )
 12  ;

Table created.

ops$tkyte%ORA10GR2> insert into t1 select to_date( '12-mar-2003','dd-mon-yyyy')+mod(rownum,3), user_id, username from all_users;

42 rows created.

ops$tkyte%ORA10GR2> alter table t1 add constraint t1_pk primary key(x);

Table altered.

ops$tkyte%ORA10GR2> select count(*) from t1;

  COUNT(*)
----------
        42

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2 ( dt date, x int primary key, y varchar2(30) ) organization index;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_redefinition.start_redef_table( user, 'T1', 'T2' )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> REM other things might need be done here, like getting constraints/grants/etc in place
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_redefinition.finish_redef_table( user, 'T1', 'T2' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> drop table t2;

Table dropped.

ops$tkyte%ORA10GR2> select count(*) from t1;

  COUNT(*)
----------
        42

ops$tkyte%ORA10GR2> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','T1')
-------------------------------------------------------------------------------

  CREATE TABLE "OPS$TKYTE"."T1"
   (    "DT" DATE,
        "X" NUMBER(*,0),
        "Y" VARCHAR2(30),
         PRIMARY KEY ("X") ENABLE
   ) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
 PCTTHRESHOLD 50

rajesh@11GR2> create table t(
  2     owner,
  3     object_name,
  4     object_type,
  5     object_id,
  6     created,
  7     constraint t_pk primary key(object_id)
  8  )organization index
  9  nologging
 10  as
 11  select owner,object_name,object_type,object_id,created
 12  from all_objects;

Table created.

Elapsed: 00:00:14.32
rajesh@11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname=>user,
  4     tabname=>'T',
  5     cascade=>true,
  6     method_opt=>'for all columns size 254',
  7     estimate_percent=>100);
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.86
rajesh@11GR2>
rajesh@11GR2> set autotrace traceonly explain statistics;
rajesh@11GR2>
rajesh@11GR2> select * from t
  2  where created between to_date('01/07/2010','dd/mm/yyyy') and sysdate;

862 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 320690585

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   987 | 51324 |   163   (1)| 00:00:02 |
|*  1 |  FILTER               |      |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_PK |   987 | 51324 |   163   (1)| 00:00:02 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')<=SYSDATE@!)
   2 - filter("CREATED">=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "CREATED"<=SYSDATE@!)


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

rajesh@11GR2> create index t_ind on t(created) nologging;

Index created.

Elapsed: 00:00:00.92
rajesh@11GR2> select * from t
  2  where created between to_date('01/07/2010','dd/mm/yyyy') and sysdate;

862 rows selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 170811138

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |   987 | 51324 |    14   (0)| 00:00:01 |
|*  1 |  FILTER            |       |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| T_PK  |   987 | 51324 |    14   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN| T_IND |   987 |       |     5   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')<=SYSDATE@!)
   2 - access("CREATED">=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "CREATED"<=SYSDATE@!)
   3 - access("CREATED">=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "CREATED"<=SYSDATE@!)


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

rajesh@11GR2>
rajesh@11GR2> drop index t_ind;

Index dropped.

Elapsed: 00:00:00.93
rajesh@11GR2> create index t_ind on t(object_id,created) nologging;

Index created.

Elapsed: 00:00:00.18
rajesh@11GR2>
rajesh@11GR2> set autotrace traceonly explain statistics;
rajesh@11GR2>
rajesh@11GR2> select * from t
  2  where created between to_date('01/07/2010','dd/mm/yyyy') and sysdate;

862 rows selected.

Elapsed: 00:00:00.12

Execution Plan
----------------------------------------------------------
Plan hash value: 320690585

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   987 | 51324 |   163   (1)| 00:00:02 |
|*  1 |  FILTER               |      |       |       |            |          |
|*  2 |   INDEX FAST FULL SCAN| T_PK |   987 | 51324 |   163   (1)| 00:00:02 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss')<=SYSDATE@!)
   2 - filter("CREATED">=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "CREATED"<=SYSDATE@!)


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

rajesh@11GR2>

Tom:

<quote>
Secondary indexes provide fast and efficient access to index-organized tables using columns that are neither the primary key nor a prefix of the primary key.
</quote>

http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/indexiot.htm#sthref408
I dont find the usefullness of Secondry Indexes on IOT. Can you tell me when this will be really usefull?

Thanks Tom its really helpful to understand.

rajesh@11GR2> select * from t
  2  where created = to_date('01/07/2010','dd/mm/yyyy')
  3  /

no rows selected

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2448192542

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    44 |  2288 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| T_PK  |    44 |  2288 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN| T_IND |    44 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("CREATED"=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   2 - access("CREATED"=TO_DATE(' 2010-07-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))


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

Tom:

this does not apply to secondary indexes exclusively.

what i have created above (T_IND) is acutally a secondary indexes . Index on IOT. I couldn't get you clearly.

Tom:

This really confuses a lot.

http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/indexiot.htm#sthref408

rajesh@11GR2> create bitmap index t_bitmap on t(deptno);
create bitmap index t_bitmap on t(deptno)
                                *
ERROR at line 1:
ORA-28669: bitmap index can not be created on an IOT with no mapping table


Elapsed: 00:00:00.12
rajesh@11GR2>

Don't know where this mapping table needs to be specified. it that documented in SQL Reference manuals?

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_5012.htm#i2129648

<quote>
Oracle Database creates the mapping table or mapping table partition in the same tablespace as its parent index-organized table or partition. You cannot query, perform DML operations on, or modify the storage characteristics of the mapping table or its partitions.
</quote>

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_7002.htm#i2146256

rajesh@11GR2> SELECT table_name,iot_type, uo.object_id
  2  FROM user_tables ut, user_objects uo
  3  where iot_type like 'IOT%'
  4  and uo.object_name = ut.table_name
  5  /

TABLE_NAME                     IOT_TYPE      OBJECT_ID
------------------------------ ------------ ----------
SYS_IOT_MAP_76674              IOT_MAPPING       76675
T                              IOT               76674

Elapsed: 00:00:00.42
rajesh@11GR2>
rajesh@11GR2>
rajesh@11GR2> alter table sys_iot_map_76674 nologging;
alter table sys_iot_map_76674 nologging
*
ERROR at line 1:
ORA-28668: cannot reference mapping table of an index-organized table


Elapsed: 00:00:00.37
rajesh@11GR2>
rajesh@11GR2> delete from sys_iot_map_76674;
delete from sys_iot_map_76674
            *
ERROR at line 1:
ORA-28668: cannot reference mapping table of an index-organized table


Elapsed: 00:00:00.00
rajesh@11GR2>
rajesh@11GR2> select count(*) from sys_iot_map_76674;

  COUNT(*)
----------
     71790

Elapsed: 00:00:00.00
rajesh@11GR2>

Tom:

Its mentioned in doc's that We cannot query the Mapping table. But see above, i am able to query that.

Hi Tom

since i tried the conventional move and get this error:
alter index IX_ANCESTORS_1 rebuild tablespace LIVELINK_INDEX ONLINE
*
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

just to make sure I understand these IOT indexes, i want to move the index to my index tablespace so would i just say:
ALTER TABLE IX_ANCESTORS_1 MOVE TABLESPACE LIVELINK_INDEX;

or is there more to it ?

Thanks
Jim

Hi Tom

thanks for the follow-up, but now I am lost on this concept

I have this table and these two indexes:
OWNER TABLE_NAME TABLESPACE_NAME IOT_TYPE STATUS
-------------- ------------------------------ ------------------------------------ ------------ --------
LLPROD DTREEANCESTORS IOT VALID


OWNER TABLESPACE_NAME TABLE_NAME INDEX_NAME STATUS INDEX_TYPE
-------------- ------------------------------------ ------------------------------ ------------------------------ -------- ----
LLPROD LIVELINK_DATA DTREEANCESTORS IX_ANCESTORS_1 VALID IOT - TOP
LLPROD LIVELINK_INDEX DTREEANCESTORS IX_ANCESTORS_2 VALID NORMAL



i moved IX_ANCESTORS_2 successfully into Livelink_index from Livelink_Data

so how can i move IX_ANCESTORS_1 into Livelink_Index ?

or because it is an IOT, should i just leave it where it is ?

Thanks
Jim

ops$tkyte%ORA11GR2> create table t ( x int constraint t_pk primary key, y int ) organization index;

Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(y);

Index created.

ops$tkyte%ORA11GR2> insert into t values ( 1, 1 );

1 row created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select index_name, tablespace_name, index_type from user_indexes;

INDEX_NAME                     TABLESPACE_NAME                INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
T_PK                           USERS                          IOT - TOP
T_IDX                          USERS                          NORMAL

ops$tkyte%ORA11GR2> alter table t move tablespace example;

Table altered.

ops$tkyte%ORA11GR2> alter index t_idx rebuild tablespace example;

Index altered.

ops$tkyte%ORA11GR2> select index_name, tablespace_name, index_type from user_indexes;

INDEX_NAME                     TABLESPACE_NAME                INDEX_TYPE
------------------------------ ------------------------------ ---------------------------
T_PK                           EXAMPLE                        IOT - TOP
T_IDX                          EXAMPLE                        NORMAL

excellent work tom!

Tom:

Does the above only apply to IOT or I can monitor del_lf_rows column to determine if any indexes need to be rebuilt

select height, blocks, del_lf_rows from index_stats;

ops$tkyte%ORA11GR2> create sequence s;

Sequence created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table t
  2  as
  3  select s.nextval id, stage.*
  4    from stage;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> alter table t add constraint t_pk primary key(id);

Table altered.

ops$tkyte%ORA11GR2> analyze index t_pk validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select del_lf_rows, lf_rows, lf_blks, lf_rows/lf_blks from index_stats;

DEL_LF_ROWS    LF_ROWS    LF_BLKS LF_ROWS/LF_BLKS
----------- ---------- ---------- ---------------
          0      72704        151      481.483444

ops$tkyte%ORA11GR2> delete from t where mod(id,482) > 5;

71799 rows deleted.

ops$tkyte%ORA11GR2> alter system flush buffer_cache;

System altered.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> analyze index t_pk validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select del_lf_rows, lf_rows, lf_blks, lf_rows/lf_blks from index_stats;

DEL_LF_ROWS    LF_ROWS    LF_BLKS LF_ROWS/LF_BLKS
----------- ---------- ---------- ---------------
          0        905        151      5.99337748

ops$tkyte%ORA11GR2> insert into t
  2  select s.nextval id, stage.*
  3    from stage;

72704 rows created.

ops$tkyte%ORA11GR2> analyze index t_pk validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select del_lf_rows, lf_rows, lf_blks, lf_rows/lf_blks from index_stats;

DEL_LF_ROWS    LF_ROWS    LF_BLKS LF_ROWS/LF_BLKS
----------- ---------- ---------- ---------------
          0      73609        287      256.477352

ops$tkyte%ORA11GR2> delete from t where mod(id,482) > 5;

71798 rows deleted.

ops$tkyte%ORA11GR2> alter system flush buffer_cache;

System altered.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> analyze index t_pk validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select del_lf_rows, lf_rows, lf_blks, lf_rows/lf_blks from index_stats;

DEL_LF_ROWS    LF_ROWS    LF_BLKS LF_ROWS/LF_BLKS
----------- ---------- ---------- ---------------
          0       1811        287      6.31010453

ops$tkyte%ORA11GR2> alter index t_pk coalesce;

Index altered.

ops$tkyte%ORA11GR2> analyze index t_pk validate structure;

Index analyzed.

ops$tkyte%ORA11GR2> select del_lf_rows, lf_rows, lf_blks, lf_rows/lf_blks from index_stats;

DEL_LF_ROWS    LF_ROWS    LF_BLKS LF_ROWS/LF_BLKS
----------- ---------- ---------- ---------------
          0       1811          4          452.75

Thanks Very Much Tom

I will try this out

Jim

Hi Tom

I have mimicked your example with my own tablespace names
Can you tell me why there is no entry for the tablespace of the table when i look at user_tables or dba_tables for that matter

Thanks
Jim


SQL> create table t ( x int constraint t_pk primary key, y int ) organization index;

Table created.

SQL> create index t_idx on t(y);

Index created.

SQL> insert into t values ( 1, 1 );

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name, tablespace_name, index_type from user_indexes;

INDEX_NAME                     TABLESPACE_NAME                INDEX_TYPE
------------------------------ ------------------------------ ------------
T_PK                           TEST_DATA                      IOT - TOP
T_IDX                          TEST_DATA                      NORMAL

2 rows selected.

SQL> alter table t move tablespace test_index;

Table altered.

SQL> alter index t_idx rebuild tablespace test_index;

Index altered.

SQL> select index_name, tablespace_name, index_type from user_indexes;

INDEX_NAME                     TABLESPACE_NAME                INDEX_TYPE
------------------------------ ------------------------------ ------------
T_PK                           TEST_INDEX                     IOT - TOP
T_IDX                          TEST_INDEX                     NORMAL

2 rows selected.


SQL> select table_name, tablespace_name, iot_name, iot_type from user_tables;

TABLE_NAME                     TABLESPACE_NAME   IOT_NAME    IOT_TYPE
------------------------------ ----------------- ----------- ------------
T                                                            IOT

1 row selected.

SQL>

Hi Tom

I have a question about importing a conventional table's data into a converted IOT.
First, is that possible and second, if not, what would be the best way to import the data
from the conventional table export into the now converted IOT ?

Reason I ask is that when the Vendor ran their script to convert the table to IOT, 
it lost all the data in the conventional table and thus the new IOT was created empty
(approx 82.75 million rows of data=> 1.2 GB)
All I have is a full export before the change was made


The tables have the same two defined columns in them


IOT

SQL> desc dtreeancestors
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 DATAID                                    NOT NULL NUMBER(10)
 ANCESTORID                                NOT NULL NUMBER(10)

TABLE_NAME      TABLESPACE_NAME IOT_TYPE
--------------- --------------- ----------
DTREEANCESTORS                  IOT


TABLESPACE_NAME      TABLE_NAME      INDEX_NAME       STATUS   INDEX_TYPE
-------------------- --------------- ---------------- -------- ------------
LIVELINK_DATA        DTREEANCESTORS  IX_ANCESTORS_1   VALID    IOT - TOP
LIVELINK_INDEX       DTREEANCESTORS  IX_ANCESTORS_2   VALID    NORMAL



Conventional 

SQL> desc dtreeancestors
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 DATAID                                    NOT NULL NUMBER(10)
 ANCESTORID                                NOT NULL NUMBER(10)

TABLE_NAME      TABLESPACE_NAME IOT_TYPE
--------------- --------------- ----------
DTREEANCESTORS  LIVELINK_DATA  

      
TABLESPACE_NAME      TABLE_NAME      INDEX_NAME       STATUS   INDEX_TYPE
-------------------- --------------- ---------------- -------- ------------
LIVELINK_INDEX       DTREEANCESTORS  IX_ANCESTORS_2   VALID    NORMAL
LIVELINK_INDEX       DTREEANCESTORS  IX_ANCESTORS_3   VALID    NORMAL



Thanks

ops$tkyte%ORA11GR2> create table t ( x int primary key, y date );

Table created.

ops$tkyte%ORA11GR2> insert into t select user_id, created from all_users;

41 rows created.

ops$tkyte%ORA11GR2> select count(*) from t;

  COUNT(*)
----------
        41

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> !exp userid=/ tables=t

Export: Release 11.2.0.2.0 - Production on Tue Mar 22 13:02:35 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8MSWIN1252 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              T         41 rows exported
Export terminated successfully without warnings.

ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> create table t ( x int primary key, y date ) ORGANIZATION INDEX;

Table created.

ops$tkyte%ORA11GR2> !imp userid=/ tables=t ignore=y

Import: Release 11.2.0.2.0 - Production on Tue Mar 22 13:02:36 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"         41 rows imported
IMP-00017: following statement failed with ORACLE error 28667:
 "ALTER TABLE "T" ADD  PRIMARY KEY ("X") USING INDEX PCTFREE 10 INITRANS 2 MA"
 "XTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 FREELISTS 1 FREE"
 "LIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING ENABLE"
IMP-00003: ORACLE error 28667 encountered
ORA-28667: USING INDEX option not allowed for the primary key of an IOT
Import terminated successfully with warnings.

ops$tkyte%ORA11GR2> select count(*) from t;

  COUNT(*)
----------
        41

Thanks Very Much Tom for your time

I will give it a shot

Jim

Hi Tom

in the Administrators Guide for 11g it says on page 20-61:
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.

My question is how can I determine if there is fragmentation on the table and at what point would i consider doing the rebuild (move) ?

Thanks

Thanks Tom

appreciate it

Hi Tom,

I was reading your book - Expert Oracle database architecture.

"wherever a table stored in a heap is unorganized, data in an IOT is stored and sorted by primary key.

does it mean in IOT table - it will keep some room for same data comes in future.. i mean in a example you created heap_address table emono, addr_type is the key.. lets say when we insert 2 employee address then first record fit in first block, then it will keep some few blocks and then store second employee data so in future if we get first employee other address that can fit in the earlier free space. that way data will be always together for one employee and retrieval will be faster. is this correct understanding.. i am missing something to understand.. can you pl. help me what exactly happy inside with iot? thanks

In a previous post, you said a partitioned IOT would be many IOTs. Then if the partition key is not the leading column, the performance would be worse than not partitioned, isn't it?

For example, I have a IOT on (id, dt), partitioned on date column dt, then query select * from t where id = :1 and dt between :2 and :3;

would have to access multiple IOTs (partitions), while non-partitioned table just have one IOT.

Is partitioning a IOT just for the benefit of administration at cost of slower query?

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    x   int primary key,
  4    dt  date,
  5    y   varchar2(30)
  6  )
  7  organization index
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  
ops$tkyte%ORA11GR2> /
PARTITION BY RANGE (dt)
                    *
ERROR at line 8:
ORA-25199: partitioning key of a index-organized table must be a subset of the
primary key

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    x   int,
  4    dt  date,
  5    y   varchar2(30),
  6    primary key(dt,x)
  7  )
  8  organization index
  9  PARTITION BY RANGE (dt)
 10  (
 11    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 12    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 13    PARTITION junk VALUES LESS THAN (MAXVALUE)
 14  )
 15  
ops$tkyte%ORA11GR2> /

Table created.

Sorry Tom, I wasn't clear, the PK of IOT is on (id, dt), and range partitioned on dt. The access pattern is

select * from t where id =:1 and dt between :2 and :3;

Hi Tom,

Thank you very much for your time.

We have an IOT table(for ex., test1) and index on primary key(for ex., test1_idx). Initial extent size of test1_idx is 2.6GB. Currently this table doesn't have any rows. Not from last 1month atleast. Even though it will increase, I don't want the initial extent to be 2.6GB. I would like to have the default settings(1MB or so).
Since we couldn't drop and recreate the primary key index on the IOT, how to change the initial extent size of this index? Please provide some inputs.

Database version: 11.1.0.7

Hi Tom,

Sorry for not being clear.

From dba_indexes I can see there is an index TEST2_IDX of type "IOT_TOP" on the table TEST2.

I already rebuilt the table using alter table move. But I am mainly concerned about the index INITIAL_EXTENT which has 2.6GB size. I want to make this set to default(1MB).

Through dbms_metadata.get_ddl, I pulled the scripts of index and table(whatever is showing from dba_indexes). Please check it here.

create table test2
( COL1 VARCHAR2(50 CHAR) NOT NULL ENABLE,
COL2 CHAR(1 CHAR) NOT NULL ENABLE,
COL3 NUMBER NOT NULL ENABLE,
COL4 NUMBER NOT NULL ENABLE,
.....
COL12 VARCHAR2(40 CHAR) NOT NULL ENABLE,
CONSTRAINT CONST_NAME PRIMARY KEY(COL1,COL2,COL3,COL4,COL5,COL6) ENABLE
) ORGANIZATION INDEX;

CREATE UNIQUE INDEX TEST2_IDX ON TEST2(COL1,COL2,COL3,COL4,COL5,COL6);

Thanks for all your inputs.

ops$tkyte%ORA11GR2> create table t
  2  ( x int,
  3    y int,
  4    z int,
  5    a varchar2(200),
  6    constraint t_pk primary key(x,y,z)
  7  )
  8  organization index;

Table created.

ops$tkyte%ORA11GR2> create unique index t_idx on t(x,y,z);
create unique index t_idx on t(x,y,z)
                               *
ERROR at line 1:
ORA-01408: such column list already indexed

Sorry Tom.

My editing mistake. I should have changed TEST2_IDX to CONST_NAME in previous question.

Infact it's primary key index.

SQL> create table t (x int,y int,z int,a varchar2(200),constraint t_pk primary key(x,y,z)) organization index;

Table created.

SQL> select index_name,index_type,table_type from dba_indexes where table_name like 'T';

INDEX_NAME                     INDEX_TYPE                  TABLE_TYPE
------------------------------ --------------------------- -----------
T_PK                           IOT - TOP                   TABLE

In the above example, (Let's say) T_PK has 3G INITIAL extent(not sure how it got at the first place). How can we make it to default 1M?

Sorry once again.

Thanks for all your help.

ops$tkyte%ORA11GR2> create table t ( x int, y int, z int, a varchar2(30),
  2                   constraint t_pk primary key(x,y,z) )
  3  organization index;

Table created.

ops$tkyte%ORA11GR2> insert into t select rownum, rownum, rownum, username from all_users;

49 rows created.

ops$tkyte%ORA11GR2> commit;

Commit complete.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select segment_name, initial_extent from user_segments;

SEGMENT_NAME                   INITIAL_EXTENT
------------------------------ --------------
T_PK                                    65536

ops$tkyte%ORA11GR2> alter table t move storage ( initial 1m );

Table altered.

ops$tkyte%ORA11GR2> select segment_name, initial_extent from user_segments;

SEGMENT_NAME                   INITIAL_EXTENT
------------------------------ --------------
T_PK                                  1048576

Hi - We recently upgraded our prod database from 11.2.0.3 to 11.2.0.4. Before the upgrade, gather stats on 2 large IOT tables with 250+ mil rows took around 5-8 mins. But after the upgrade, stats on one of the tables is taking around 18 hrs and the other is taking around 5 hrs. There are a few other IOT tables (with rows ranging from a few hundreds to 50+ mil rows) and they seem to be doing fine. Just wanted to check in and see if there is something specific that would cause this. We ran fixed objects stats and dictionary stats after the upgrade

will do