Need help to understand:
FRED, August 16, 2001 - 3:37 pm UTC
since the data is added to the end of the heap table each night --
that will be 5 or 6 DIFFERENT blocks. Each row in the table for the stock
ticker ORCL will be on a DIFFERENT block due to the nature of your load routine
-- every night you get 1,000 more and they are added to the end. Each and every
entry for ORCL (or any stock) is on a separate block.
WHY UN-ENABLE LOADING A FEW RECORDS IN A BLOCK IN REGULAR TABLE?
Heap tables
Andre, June 10, 2002 - 11:00 am UTC
Why are non-IOT tables often called "heap" tables ?
June 11, 2002 - 10:04 am UTC
Because that is what they are? It is the default table type:
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int ) organization HEAP;
Table created.
The data in a heap organized table is stored in a "heap" fashion -- data goes where ever it fits. In an IOT -- data goes where it belongs (by key). For tons of details on this (and all of the table types) -- I will recommmend my book. I go through this in some detail.
Gopi, December 20, 2002 - 10:20 pm UTC
It was an excellent example. But I could not link the example with your last statement: 'If you have lots of lookup tables like "zip_code,state" -- an IOT only makes sense.'
Can you kindly elaborate? Will it be a good idea to make the dimension tables in a star schema IOT? Thanks verymuch.
December 21, 2002 - 8:58 am UTC
if you have a two or three column table like
ZIP_CODE primary key, STATE char(2)
or
STATE_ABBREV char(2) primary key, STATE_NAME varchar2(80)
etc, you have to ask:
o do I want a table and a primary key index, such that I'll use the index to read the table to go from code to code value
o or do I just want an index with the code and code value?
IOT in a Datawarehouse
A reader, June 21, 2003 - 3:17 pm UTC
Hi Tom,
Will an IOT be a good candidate for a large Fact table in a datawarehouse, where large amount of data is loaded daily followed by massive reads?
What are the points to be considered, before implementing the Fact table as an IOT?
June 21, 2003 - 4:42 pm UTC
fact? probably not.
dimension? probably so.
that is where they seem more useful.
How many keyed reads into a fact table do you do? there the index structure would just get in the way. full scan ahead.
dimensions -- perhaps you would want to benchmark them there.
Thanks
A reader, June 21, 2003 - 10:40 pm UTC
What about an IOT Dual table?
Murali, October 31, 2003 - 11:24 am UTC
Hi Tom,
One of my friends was mentioning that their setup has an IOT called X$DUAL and they use it to select values like sysdate , user etc instead of a regular DUAL table.
Do you think this is a good idea?. If either yes or no, Can you please elaborate the reasons?.
Thanks.
November 01, 2003 - 12:00 pm UTC
well, x$ tables are "special", they'd have to create a view of that in order to grant on it.
I can say in 10g -- the problem goes away. Check this out:
ops$tkyte@ORA10G> select * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=2)
1 0 TABLE ACCESS (FULL) OF 'DUAL' (TABLE) (Cost=3 Card=1 Bytes=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
389 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
ops$tkyte@ORA10G> select sysdate from dual;
SYSDATE
---------
01-NOV-03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1)
1 0 FAST DUAL (Cost=3 Card=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
397 bytes sent via SQL*Net to client
511 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
<b>see "fast dual" in the second one. If you do not actually select a column from dual -- it short circuts and just executes your function. No IO at all.</b>
I would setup my OWN table -- not use x$dual
IOT or not IOT - that is the question
j., November 24, 2003 - 8:31 am UTC
one of our applications uses one single table as storage for all language specific text values.
those texts are stored in 3 columns (10, 30 or 80 characters long) and accessed through a composite primary key consisting of an ID and an additional "language marker".
views join this table's content to present text according to the language specified as an environment variable.
apart from attacking the issue using the brute force approach (just measure the impact) how can i evaluate
whether or not an IOT would reduce I/O (and be a performance gain after all)?
November 24, 2003 - 8:59 am UTC
IOT's are awesome for these sort of keyed lookups.
Suggest you do this
a) trace your application, save the tkprofs of the queries that access this table
b) simply rebuild the table as an IOT and do (a) again.
there is nothing short of benchmarking here... we can hypothesize all day long, but until you measure it, you'll never know for sure.
what about storage options
j., November 30, 2003 - 3:19 am UTC
thank you. i'll give it a try.
but please let me ask an additional question first: since the application almost always accesses only the 30 characters long pieces of text from the lookup would you recommend to put the 80 characters long column into the overflow segment to get more blocks stored into the IOTs segment itself?
November 30, 2003 - 8:21 am UTC
not really -- that offsets the benefits of the IOT itself.
You would be back to the equivalent of:
index range scan -> table access by index rowid
where the table access would be the read into the overflow segment.
???
j., December 01, 2003 - 7:24 am UTC
but this only holds true in case one retrieves the 80 characters long text instead of the 30 characters long (stored in the index), right?
December 01, 2003 - 7:50 am UTC
ok, read too fast, missed the "almost always only accesses"
Here -- I would want to benchmark but -- I would still tend to want to avoid overflow segments.
The concept of the IOT to me is to store KEY + functionally dependent data together, sorted by key. Overflows defeat that.
I would still tend to want to have everything in the one segment.
which datatype is good for an IOT?
Ashiq Shamsudeen A, December 01, 2003 - 8:10 am UTC
Hi Tom,
My colleague was telling me ,if you use IOT, create it for a number datatype than for other datatypes.But he didn't gave the any metrics for it. Does we any advantage using number datatype than using other type for index ?
December 01, 2003 - 8:17 am UTC
ask them
a) why
b) for the proof
else ignore it. Use the right datatype, if your primary key is in fact a VARCHAR2, make it so. think:
postal code -> region lookup (are you going to make it really be SURROGATE_KEY -> postal_code,region lookup????)
state_code -> state lookup (are you going to make it be SURROGATE_KEY-> state_code,state ??)
I wouldn't.
Loading Index Organised Tables
CJ, December 01, 2003 - 10:04 am UTC
Is there any good performing way to load an IOT? I've got a 55M row table to load nightly. It is a 4 column lookup table in a warehouse, and I'm considering an IOT. Since nologging does not apply to IOT, it takes a while to load. Is there some tips you can give to speed it up?
December 01, 2003 - 5:54 pm UTC
insert /*+ APPEND */ into iot select * from external_table;
seems to reduce by about 50% the amount of redo I generate -- create table iot as select * from external table -- a little more then 50% (noarchivelog mode)
how about you?
IOT freelist
Alvin, December 01, 2003 - 10:17 pm UTC
1. On concurrent inserts can you change the freelist settings of an IOT ?
2. How "wide or fat" should a table be before an IOT table "overflows" ?
December 02, 2003 - 8:29 am UTC
1) yes, IOTs can have multiple freelists.
2) I think if a table is so wide or fat that it would overflow, you would be hard pressed to have me say "make it an IOT". I do not overflow IOTs myself.
Re: "Loading Index Organised Tables "
Sudhir, December 02, 2003 - 6:41 am UTC
sort_area_size/sort_area_retained_size of very large value should help?
Thanks
December 02, 2003 - 8:44 am UTC
pga_aggregate_target in 9i is more appropriate.
it can help upto a point (diminishing returns after a while)
Good information about IOTs
Raj, February 06, 2004 - 1:58 pm UTC
Hi Tom,
After reading about IOTs chapter (7) in your book Effective Oracle by Design. It looks like IOT is only effective if we have to query the table by its Primary Key.
In my case we have many tables showing many to many relationship between tables. But as a rule we are using first field ID as a primary key in all the tables hence the composite key is not the primary key.
Eg. table A ( A_Id number, A_desc (varchar2(100))
table B ( B_Id number, b_desc (varchar2(100))
table C ( C_Id number, A_Id number, B_Id number )
in all this table a_id, b_id, c_id is the primary key respectively.
It is not only that C_Id is used as rule over here but it is also use to group other two id's multiple times hence it is important to have it in the table but most of our queries run on A_Id and B_Id on table C. So my question is should I make this table as IOT. If yes then can you please show me how should I create the table and should I have extra indexes for A_Id and B_Id on table C or not.
Oracle 9iR2.
February 07, 2004 - 1:40 pm UTC
elaborate on why C has it's own primary key again? that was not clear at all.
"use to group 2 other 2 id's multiple times" doesn't compute with me.
Who is killing me
Dhrubo, March 30, 2004 - 9:43 am UTC
Hi Tom,
I have this table -
create table myheap (
a number,
b varchar2(10),
c varchar(100),
constraint pk_my_heap primary key (a, b)
);
Now i do the following -
1)Run this anonymous block to insert 10000 rows
begin
dbms_output.put_line('T1-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
for i in 1..10000 loop
insert into myheap values( i,'aa','vvvvvvvvvvvvvvvvvv-'||i);
end loop;
commit;
dbms_output.put_line('T2-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end;
3)Note the timings :
T1-->72463
T2-->72467
4)Drop and recreate the table
5)Run the same script but this time i use a sequence "IOT_PK_SEQ"
begin
dbms_output.put_line('T1-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
for i in 1..10000 loop
insert into myheap values( IOT_PK_SEQ.nextval,'aa','vvvvvvvvvvvvvvvvvv-'||i);
end loop;
commit;
dbms_output.put_line('T2-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end;
6)Note the time again
T1-->72528
T2-->72543
Now my question is why is the last one takes more time than
the first.Is the sequence killing me?If yes why
March 30, 2004 - 10:52 am UTC
well, you should use bulk binds. (to see how much was "sequence", one would simply drop the use of the sequence in the test case!)
IOT's force ORDER on the data -- all of the data. the index blocks split more frequently.
Fact: An IOT is slower to insert into.
Fact: An IOT can be amazing on speeding retrieval and making your queries more scalable.
Ask yourself "how many times do I insert a row". "how many times do I retrieve it". "is it OK to pay a penalty during INSERTION in order to make retrieval super efficient". "how do I really use this table".
the answers to those questions will lead you to the right implementation
Check the timmings now
Dhrubo, March 31, 2004 - 11:54 pm UTC
declare
type t1 is table of number index by binary_integer;
--type t2 is table of varchar2(200) index by binary_integer;
a t1;
--b t2;
begin
dbms_output.put_line('T1-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
for i in 1..10000 loop
a(i):=i;
end loop;
dbms_output.put_line('T2-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
forall i in 1..10000
insert into myheap values( a(i),'aa','vvvvvvvvvvvvvvvvvv-');
commit;
dbms_output.put_line('T3-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end;
T1-->71414
T2-->71414
T3-->71415
===========================================================
declare
type t1 is table of number index by binary_integer;
--type t2 is table of varchar2(200) index by binary_integer;
a t1;
--b t2;
begin
dbms_output.put_line('T1-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
for i in 1..10000 loop
SELECT IOT_PK_SEQ.nextval INTO a(i) FROM DUAL ;
end loop;
dbms_output.put_line('T2-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
forall i in 1..10000
insert into myheap values( a(i),'aa','vvvvvvvvvvvvvvvvvv-');
commit;
dbms_output.put_line('T3-->'||TO_CHAR(SYSTIMESTAMP,'SSSSS'));
exception
when others then
rollback;
dbms_output.put_line(sqlerrm);
end;
T1-->71542
T2-->71553
T3-->71554
===========================================================
Now who is killing me .. the sequence ... what is the reason tom?
April 01, 2004 - 9:47 am UTC
what is the reason for what exactly?
what you have shown is that going to the database 10,000 times, one after the other after the other, to request a sequence takes longer then simply assigning a number in a loop.
so, why would you call nextval in a loop, just insert it. also, consider the effect of the sequence cache which at 20 by default is too low for many highly active systems
ops$tkyte@ORA9IR2> /*
DOC>drop table t;
DOC>drop sequence iot_pk_seq;
DOC>create sequence iot_pk_seq;
DOC>create table t ( x int, y varchar2(2), z varchar2(25) );
DOC>*/
ops$tkyte@ORA9IR2> truncate table t;
Table truncated.
Elapsed: 00:00:00.71
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set timing on
ops$tkyte@ORA9IR2> declare
2 type t1 is table of number index by binary_integer;
3 a t1;
4 type t2 is table of varchar2(2) index by binary_integer;
5 b t2;
6 begin
7 for i in 1..10000
8 loop
9 a(i):=i;
10 b(i):='aa';
11 end loop;
12 forall i in 1..10000
13 insert into t values( a(i),b(i),'vvvvvvvvvvvvvvvvvv-');
14 commit;
15 end;
16 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
ops$tkyte@ORA9IR2> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter sequence iot_pk_seq cache 20;
Sequence altered.
Elapsed: 00:00:00.00
ops$tkyte@ORA9IR2> declare
2 type t2 is table of varchar2(2) index by binary_integer;
3 b t2;
4 begin
5 for i in 1..10000
6 loop
7 b(i):='aa';
8 end loop;
9 forall i in 1..10000
10 insert into t values( iot_pk_seq.nextval,b(i),
11 'vvvvvvvvvvvvvvvvvv-');
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.25
ops$tkyte@ORA9IR2> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
ops$tkyte@ORA9IR2> alter sequence iot_pk_seq cache 10000;
Sequence altered.
Elapsed: 00:00:01.02
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 type t2 is table of varchar2(2) index by binary_integer;
3 b t2;
4 begin
5 for i in 1..10000
6 loop
7 b(i):='aa';
8 end loop;
9 forall i in 1..10000
10 insert into t values( iot_pk_seq.nextval,b(i),
11 'vvvvvvvvvvvvvvvvvv-');
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.12
ops$tkyte@ORA9IR2> /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
<b>there you go -- 0.11 vs 0.09/0.08. no significant difference</b>
Why is NOLOGGING not relevant to IOTs
VKOUL, May 06, 2004 - 8:21 pm UTC
Excerpt:
Loading Index Organised Tables December 01, 2003
Reviewer: CJ from NY
Is there any good performing way to load an IOT? I've got a 55M row table to
load nightly. It is a 4 column lookup table in a warehouse, and I'm considering
an IOT. "Since nologging does not apply to IOT", ..........
Could you please elaborate ?
May 07, 2004 - 7:21 am UTC
nologging only applies during the CTAS (create table as select) using SQL.
ops$tkyte@ORA9IR2> create table t
2 ( a,b,c,d, primary key(a,b)) organization index
3 as
4 select object_id, object_name, owner, object_type
5 from all_objects
6 where 1=0;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t NOLOGGING;
Table altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> define S="&1"
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME VALUE
------------------------------ ----------
redo size 8169020
ops$tkyte@ORA9IR2> insert /*+ APPEND */ into t
2 select object_id, object_name, owner, object_type
3 from all_objects
4 /
30656 rows created.
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 1: select a.name, b.value V, b.value-&V diff
new 1: select a.name, b.value V, b.value- 8169020 diff
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME V DIFF
------------------------------ ---------- ----------
redo size 14325504 6156484
<b>6 meg of redo, no avoiding it on an insert -- indexes always generate redo on insert</b>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> @mystat "redo size"
ops$tkyte@ORA9IR2> column value new_val V
ops$tkyte@ORA9IR2> define S="&1"
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> select a.name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME VALUE
------------------------------ ----------
redo size 14351956
ops$tkyte@ORA9IR2> create table t
2 ( a,b,c,d, primary key(a,b)) organization index
3 NOLOGGING
4 as
5 select object_id, object_name, owner, object_type
6 from all_objects
7 /
Table created.
ops$tkyte@ORA9IR2> @mystat2
ops$tkyte@ORA9IR2> select a.name, b.value V, b.value-&V diff
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 and lower(a.name) like '%' || lower('&S')||'%'
5 /
old 1: select a.name, b.value V, b.value-&V diff
new 1: select a.name, b.value V, b.value- 14351956 diff
old 4: and lower(a.name) like '%' || lower('&S')||'%'
new 4: and lower(a.name) like '%' || lower('redo size')||'%'
NAME V DIFF
------------------------------ ---------- ----------
redo size 14412820 60864
<b>but with a CTAS, we can avoid it</b>
Candidate for IOT?
A reader, December 22, 2004 - 2:04 am UTC
Hi Tom,
We are using Oracle 9i R2.I have never worked with IOT's,but after reading your book I gave a serious thought to it. We have a READ ONLY table which has the following structure :-
SQL> desc temp_cdr_archive
Name Null? Type
-----------------------------------------------------
TMP_CDR_SRC_ID VARCHAR2(30)
TMP_CDR_DEST_ID VARCHAR2(30)
TMP_CDR_TOT_BYTES VARCHAR2(200)
TMP_CDR_ATTRIB1 VARCHAR2(100)
TMP_CDR_ATTRIB2 VARCHAR2(100)
TMP_CDR_START_TIME DATE
TMP_CDR_END_TIME DATE
Around 1 million rows are appended to the table every 3 hours. Currently it is a compressed table ordered by TMP_CDR_DEST_ID.
The table is queried in the form of
where TMP_CDR_DEST_ID =:value and TMP_CDR_START_TIME between :value1 and :value2
The table stats are as :-
COLUMN_NAME NUM_DISTINCT AVG_COL_LEN SAMPLE_SIZE
------------------------------------------------------------
TMP_CDR_SRC_ID 424949 14 1101407
TMP_CDR_DEST_ID 19402 14 1101407
TMP_CDR_TOT_BYTES 6499 2 1101407
TMP_CDR_ATTRIB1 127700 4 1101407
TMP_CDR_ATTRIB2 1137 2 1101407
TMP_CDR_START_TIME 1 7 1101407
TMP_CDR_END_TIME 1 7 1101407
Is this table a good canditate for an IOT ?(Primary Key as TMP_CDR_DEST_ID and TMP_CDR_START_TIME)
Thanks
December 22, 2004 - 9:30 am UTC
you'll lose the ability to "append" (no direct pathing here)
You won't be able to compress the table table either (you have index key compression, but the only repeating field will be tmp_crd_src_id)
the IOT will make the retrievals more efficient (all of the records for a given tmp_crd_dest_id between a given date will be physically right next to eachother)
how is the data purged from this thing?
Canditdate for IOT?
A reader, December 23, 2004 - 12:02 am UTC
Hi Tom,
The Table is range partitioned on TMP_CDR_START_TIME and Sliding window is used to purge the data after 3 months.
How about making all the columns as part of the primary Key? Will this compress the IOT the same way as a compressed table?
December 23, 2004 - 11:06 am UTC
ok, so no problem on the purging of data.
index key compression works on the LEADING EDGE of the individual records.
segment compression works on the entire database blocks -- all columns, rows, bits and bytes.
given your key would be "unique" after the first bit, it would not compress very well.
If you had an IOT on all_objects, having the key be:
owner,object_type,object_name,.....
would compress pretty well -- an owner has LOTS of tables (owner,table stored once followed by the rest of the information) but a key
object_name,owner,object_type
would compress miserably since object_name hardly repeats and of course an owner has very few object_names that are repeated.
Can it be IOT
A reader, January 07, 2005 - 9:13 am UTC
Hi Tom
I have a table structure as below
create table test
(col1 NUMBER(12),
col2 VARCHAR2(80),
col3 VARCHAR2(255),
col4 VARCHAR2(1),
col5 NUMBER(2),
col6 DATE )
PARTITION BY RANGE (col6)
(
PARTITION p1 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY')) TABLESPACE tablespace1,
PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE tablespace2
) ENABLE ROW MOVEMENT;
The table will potentially contain billions of records. It has been partitioned on col6 purely for archiving. The suggestion on this site is, a table can be an IOT if it is lean and large. Can the above table be converted into IOT?
I have also read that IOT can become fragmented and needs to be rebuilt periodically. What are the other things to be considered as far as maintenance is concerned?
Many Thanks
January 07, 2005 - 9:39 am UTC
where is the suggestion that lean and large -> IOT
I have said, if a table is "skinny", it can be considered for an IOT if it makes sense.
It depends on HOW you use it. totally. without that kind of detail, one cannot recommmend a table type (do you have access to Effective Oracle by Design -- or Expert One on One Oracle -- i go into the details in depth there)
IOT
A reader, January 07, 2005 - 11:30 am UTC
Hi Tom
Sorry didn't mention how the table will be used. For the above table combination of col1,col4,col2 and col5 is unique( primary key ). The table will be frequently queried on col1 and col4 combined. I have now read the section Index Organized Tables( Chapter 6) in Expert one on one Oracle where you have also discussed about COMPRESS,PCTTHRESHHOLD,OVERFLOW and INCLUDING. The example of the table that you have given ( iot ) is skinny. Is my table a good candidate for IOT?
Thanks
January 08, 2005 - 3:34 pm UTC
pretend X is c1,c4,c2,c5. Y is c6
ops$tkyte@ORA9IR2> create table t ( x int primary key, y date )
2 organization index
3 partition by range(y)
4 ( partition p1 values less than ( to_date('01-jan-2006','dd-mon-yyyy')),
5 partition p2 values less than ( maxvalue )
6 )
7 /
partition by range(y)
*
ERROR at line 3:
ORA-25199: partitioning key of a index-organized table must be a subset of the
primary key
so.... no, not in your case it won't be appropriate. (and you'll have a GLOBAL index on the primary key with your heap table)
IOT
A reader, January 09, 2005 - 6:23 am UTC
Thanks very much Tom.
IOT Strategy
A reader, January 12, 2005 - 4:18 pm UTC
Tom,
I have a 20 million+ summary table (growing) partitioned per quarter. Every quarter my analysts require a report whch give Columns A,B,C,D and COUNT(DISTINCT E) from the table.
I require all records of that quarter to the tune of millions. Would this table be a good candidate for an IOT.
I would be querying -
SELECT A,B,C,D, COUNT(DISTINCT E) FROM tab partition (_xyz)
GROUP BY A,B,C,D
(This query is inherently slow and takes hours to complete)
I can create a PK on A,B,C and D
Would like to know your opinion on this? Is there a better approach to handle this?
Thanks a ton in advance.
January 12, 2005 - 7:43 pm UTC
if it is taking "hours", i would think you either
a) have really slow disk
b) have no cpu left to speak of
c) haven't sized your memory (pga aggregate target/sort area size) correctly
borrowing from work this morning:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:5424649653071#31612037254810 <code>
i took the same table and ran:
big_table@ORA9IR2> l
1 select owner, object_name, object_id, object_type, count(distinct id)
2 from big_table
3* group by owner, object_name, object_id, object_type
big_table@ORA9IR2> /
27935 rows selected.
Elapsed: 00:15:45.26
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=279909 Card=35000000 Bytes=2240000000)
1 0 SORT (GROUP BY) (Cost=279909 Card=35000000 Bytes=2240000000)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=76995 Card=35000000 Bytes=2240000000)
Statistics
----------------------------------------------------------
223 recursive calls
338 db block gets
507382 consistent gets
1022787 physical reads
720 redo size
1183069 bytes sent via SQL*Net to client
20981 bytes received via SQL*Net from client
1864 SQL*Net roundtrips to/from client
4 sorts (memory)
1 sorts (disk)
27935 rows processed
Now, it'll be more a function of how many rows are returned from the 20million, so flipping that around (using ID which is unique in my case instead of object id.. 1 select owner, object_name, id, object_type, count(distinct object_id)
2 from big_table
3* group by owner, object_name, id, object_type
big_table@ORA9IR2> /
35000000 rows selected.
Elapsed: 00:35:03.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=656366 Card=35000000 Bytes=2240000000)
1 0 SORT (GROUP BY) (Cost=656366 Card=35000000 Bytes=2240000000)
2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=76995 Card=35000000 Bytes=2240000000)
Statistics
----------------------------------------------------------
0 recursive calls
5708 db block gets
507329 consistent gets
1424776 physical reads
720 redo size
680964643 bytes sent via SQL*Net to client
25667162 bytes received via SQL*Net from client
2333335 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
35000000 rows processed
Given the information above -- I cannot say if an IOT would or would not be of overall benefit (i'm sure you do lots of other stuff to this table during the qtr, I would rather pay attention to that - then a single query!)
But here, I think you have something "undersized" by a lot (memory). it should not take hours to do something like this.
reader
A reader, March 20, 2005 - 8:44 am UTC
If I have 3 columns on a table and all three columns
are part of a primary key. Can I create a IOT for this
table ( table with only 3 column primary key ). Do I need
to size the pctthreshold value appropriately.
March 20, 2005 - 6:32 pm UTC
I would hope that the three columns would fit on the leaf -- if not, sort of "wide" for a primary key wouldn't it be?
(and a table with just a primary key and no functionally dependent attributes?)
really excellent
shameem Ahmed, March 21, 2005 - 1:38 am UTC
really very much explainatory. Very much helpfull service for oracle community. Tom after going through ur explainations and reader's view , i come to following conclusion:
1. We use IOT either for LOOKUP table and tables who are static in nature in OLTP applications and all FACT and DIMENSION tables in OLAP
2. If Tables are Volatile in nature then we use Heap Oragnised Table i.e. Default type of table's.
PLz Confirm me
Thanx and Regards
March 21, 2005 - 10:25 am UTC
1) why static? not so.
2) no, not at all - what leads you to that conclusion from this material?
think of a document management system. primary key is (userid,document_name)
most frequently executed query is:
select * from docs where userid=:x;
now the documents arrive continously from many users (so each of my docs are far from each other in the heap table - just like the stocks), but we retrieve all of them for a user (or all of the most recent -- whatever).
We'd like my documents to be together, at a slight cost of extra work on the insert.
because we retrieve my documents lots, but insert them once.
they apply in read write systems.
Number of columns
Dhamo, March 23, 2005 - 9:09 am UTC
Thanks you so much for your valuable service.
Please give me your thoughts :
1)
Is it worth considering a table having 100+ or
500+ columns for IOT ?
(Assuming that this table is mostly queried using primary
key columns in the where clause)
What will be the performance impacts in this case.?
2)
Are the index-organized tables only suitable for tables
having less number of columns (say below 10 cols)??
3)
Do we need to consider the number of columns of a table,
before converting into an IOT.?
Many Thanks.
March 23, 2005 - 9:18 am UTC
1) probably not, it'll overflow and everything will become a "index range scan" to find the primary key and then a "overflow access by pointer" (made up terms there) to get the rest of the row
as opposed to a conventional index range scan plus table access by index rowid.
the IOT would provide not much here.
2) the number is not as relevant as the width, you want them to fit on the leaf block nicely.
3) width.
Dhamo
Dhamo, March 24, 2005 - 1:28 am UTC
Thanks a lot Tom.
How to calculate space consumed by IOT
syed, May 18, 2005 - 6:24 am UTC
Tom
is there an easy way to find the number of bytes consumed by an IOT ?
thanks
syed
May 18, 2005 - 9:10 am UTC
dba_extents
dba_segments
dbms_space
just like for any segment type.
I still cant find the info though
syed, May 19, 2005 - 4:15 am UTC
Tom
SQL> create table iot_test
( col1 number(6) not null,
col2 varchar2(3),
constraint it_pk primary key (col1)
)
organization index;
Table created.
SQL> select * from dba_extents where segment_name='IOT_TEST';
no rows selected
SQL> select * from dba_segments where segment_name='IOT_TEST';
no rows selected
SQL> @printbl 'select * from dba_tables where table_name="IOT_TEST"'
OWNER : NEIL
TABLE_NAME : IOT_TEST
TABLESPACE_NAME :
CLUSTER_NAME :
IOT_NAME :
PCT_FREE : 0
PCT_USED : 0
INI_TRANS : 0
MAX_TRANS : 0
INITIAL_EXTENT :
NEXT_EXTENT :
MIN_EXTENTS :
MAX_EXTENTS :
PCT_INCREASE :
FREELISTS :
FREELIST_GROUPS :
LOGGING : YES
BACKED_UP : N
NUM_ROWS :
BLOCKS :
EMPTY_BLOCKS :
AVG_SPACE :
CHAIN_CNT :
AVG_ROW_LEN :
AVG_SPACE_FREELIST_BLOCKS :
NUM_FREELIST_BLOCKS :
DEGREE : 1
INSTANCES : 1
CACHE : N
TABLE_LOCK : ENABLED
SAMPLE_SIZE :
LAST_ANALYZED :
PARTITIONED : NO
IOT_TYPE : IOT
TEMPORARY : N
SECONDARY : N
NESTED : NO
BUFFER_POOL :
ROW_MOVEMENT : DISABLED
GLOBAL_STATS : NO
USER_STATS : NO
DURATION :
SKIP_CORRUPT : DISABLED
MONITORING : NO
CLUSTER_OWNER :
-----------------
PL/SQL procedure successfully completed.
SQL>
May 19, 2005 - 8:01 am UTC
the segment is an index segment, not a table segement and will have the name of the primary key constraint:
ops$tkyte@ORA9IR2> create table iot_test
2 ( col1 number(6) not null,
3 col2 varchar2(3),
4 constraint it_pk primary key (col1)
5 )
6 organization index;
Table created.
ops$tkyte@ORA9IR2> set echo off
Wrote file /tmp/xtmpx.sql
OBJECT_TYPE OBJECT_NAME S TABLESPACE_NAME
------------ ------------------------------ - ------------------------------
INDEX IT_PK USERS
TABLE IOT_TEST
so, you are looking for IT_PK, not IOT_TEST
IOT vs. Regular Table INDEX
Robert, May 19, 2005 - 10:59 am UTC
Tom,
What advantages does an IOT have over just putting the appropriate index on a regular table.
It seems to me they are both accomplishing the same thing... Reducing LIOs by getting all the data you need from the 'index' without having to make a trip the corresponding table..
..Instead of creating a separate IOT, why not just create an index with those same columns on a table?
Thanks,
Robert.
May 19, 2005 - 11:39 am UTC
did you see the example above?
stock information -- primary key = (symbol,date)
needed information = (symbol,date,VALUE (and maybe more.......))
choices:
a) IOT
b) table + index which more than 100% overhead as the index on all of the columns would be larger than the table itself.
Clarified
Robert, May 19, 2005 - 12:38 pm UTC
Tom,
I was a little quick on the trigger, but thank you for clarifying this. I have gone back and read most of the thread starting at the top.
My understanding of IOTs vs. regular tables has solidly increased 75% today.
Thanks,
Robert.
Improvement text
A reader, June 15, 2005 - 11:41 am UTC
Hi Tom I had create a table heap and iot, and used a function that queries 187 distinct values in 180806
rows.
I found a difference of 555245 consistent gets in heap table and 374439 consistent gets in iot table, about ~1,448 MB, but in time there were no difference.
1) Question:
I didn't expected minutes, but for 1,448 MB, I think 1-3 second, of difference should be shown. The most I get were centiseconds.
Why there is such small difference in time when there is a difference of 1,448 MB.
2) Question:
Using IOT, the most important performance improvement should be reduce block reads?
Thank you tom in advance
The test:
SQL> SELECT daz.db_lecdolar_IOT( HCA_FECHA ) FROM HICARTERA;
180806 filas seleccionadas.
real: 25708
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=100447 Bytes
=904023)
1 0 VIEW OF 'HICARTERA' (Cost=44 Card=100447 Bytes=904023)
2 1 UNION-ALL
3 2 INDEX (FAST FULL SCAN) OF 'CST_HCA_CODCART_RO' (UNIQUE
) (Cost=42 Card=100446 Bytes=703122)
4 2 INDEX (FULL SCAN) OF 'CST_HCA_CODCART' (UNIQUE)
Statistics
----------------------------------------------------------
180806 recursive calls
0 db block gets
374439 consistent gets
0 physical reads
0 redo size
2801804 bytes sent via SQL*Net to client
1338300 bytes received via SQL*Net from client
12055 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
180806 rows processed
SQL> SELECT daz.db_lecdolar( HCA_FECHA ) FROM HICARTERA;
180806 filas seleccionadas.
real: 25717
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=100447 Bytes
=904023)
1 0 VIEW OF 'HICARTERA' (Cost=44 Card=100447 Bytes=904023)
2 1 UNION-ALL
3 2 INDEX (FAST FULL SCAN) OF 'CST_HCA_CODCART_RO' (UNIQUE
) (Cost=42 Card=100446 Bytes=703122)
4 2 INDEX (FULL SCAN) OF 'CST_HCA_CODCART' (UNIQUE)
Statistics
----------------------------------------------------------
180806 recursive calls
0 db block gets
555245 consistent gets
0 physical reads
0 redo size
2801800 bytes sent via SQL*Net to client
1338300 bytes received via SQL*Net from client
12055 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
180806 rows processed SQL> SELECT TCS_TC_COMPRADOR,TCS_TC_VENDEDOR
2 FROM daz.utl_tcs_iot2 WHERE TCS_FECHA = TO_DATE('06052005','DDMMYYY
Y') AND TCS_MONEDA = PCK_TCS.ME ;
TCS_TC_COMPRADOR TCS_TC_VENDEDOR
---------------- ---------------
.818022 .818022
real: 140
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 INDEX (UNIQUE SCAN) OF 'AEIOU' (UNIQUE) (Cost=1 Card=1 Byt
es=20)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
320 bytes sent via SQL*Net to client
417 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SELECT TCS_TC_COMPRADOR,TCS_TC_VENDEDOR
2 FROM daz.utl_tcs WHERE TCS_FECHA = TO_DATE('06052005','DDMMYYYY') A
ND TCS_MONEDA = PCK_TCS.ME ;
TCS_TC_COMPRADOR TCS_TC_VENDEDOR
---------------- ---------------
.818022 .818022
real: 141
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=20)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'UTL_TCS' (Cost=2 Card=1
Bytes=20)
2 1 INDEX (UNIQUE SCAN) OF 'CST_TCS_FECHA_MONEDAS1' (UNIQUE)
(Cost=1 Card=473)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
320 bytes sent via SQL*Net to client
417 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
June 15, 2005 - 3:42 pm UTC
if your function is taking the vast majority of the time (which I would guess to be the case) then making something that doesn't take the vast majority of the time faster won't do much for you.
what is in your function, that is what we want to "tune away", - optimially there would not be a plsql function there at ALL
ops$tkyte@ORA9IR2> select 180000*0.01/60/60 from dual;
180000*0.01/60/60
-----------------
.5
even of the function is called in 0.01 seconds, it'll be at least 1/2 of a minute!
A reader, June 15, 2005 - 4:13 pm UTC
Thank you Tom, I pasted the function.
I'll appreciate your advice in tune it.
But the questions I did, are because I'm trying to test iot against normal tables and understand benefits, I'll appreciate your comment about two questions too.
1) Question:
I didn't expected minutes, but for 1,448 MB, I think 1-3 second, of difference should be shown. The most I get were centiseconds.
Why there is such small difference in time when there is such difference when not using IOT read 1,448 MB more data.
(8k block size)
2) Question:
Using IOT, the most important performance improvement should be to reduce block reads?
FUNCTION db_lecdolar_IOT( dFecha2 DATE ) RETURN NUMBER IS
dFecha DATE := TRUNC( dFecha2 );
nTCComp NUMBER := NULL;
nTCVend NUMBER := NULL;
cModoTC VARCHAR2(1) := NULL;
nTC NUMBER;
eError EXCEPTION;
BEGIN
/* Busca la fecha en TCS */
SELECT TCS_TC_COMPRADOR,TCS_TC_VENDEDOR INTO nTCComp,nTCVend
FROM daz.utl_tcs_iot2 WHERE TCS_FECHA = dFECHA AND TCS_MONEDA = PCK_TCS.ME ;
cModoTC := PCK_TCS.cModoTC;
IF cModoTC = 'C' THEN
nTC := nTCComp;
ELSE
nTC := nTCVend;
END IF;
RETURN nTC;
EXCEPTION WHEN OTHERS THEN
RAISE eError;
END;
June 16, 2005 - 3:33 am UTC
1) you called a function HUNDREDS OF THOUSANDS OF TIMES. that is where the VAST majority of time is spent. making something that does not consume a large percentage of the run time run faster, won't make the process itself run faster.
It is all about "math"
2) not relevant in this question -- first, look at your function. Looks alot like a JOIN TO ME.
Just join, no function, just JOIN.
A reader, June 16, 2005 - 8:55 am UTC
Thanks Tom.
Appropriate structure?
VA, June 26, 2005 - 3:57 pm UTC
I have a table with 4 columns like
create table t
(
i int,
j int,
k int,
vc varchar2(50)
)
It has about 100K rows. Queries on the table are of the form
select distinct i,j,k from t where vc in ('a','b','c');
select distinct i,j,k from t;
select .... from t where i=?
select .... from t where j=?
select .... from t where k=?
Cardinality of vc is about 10
Cardinality of i is about 15000
Cardinality of j is about 500
Cardinality of k is about 20
What is the appropriate table/indexes for storage/retrieval of this data?
An IOT with secondary indexes? A heap organized table with individual indexes on each column? Or a concatenated index? In what order?
Thanks for any help
June 26, 2005 - 4:58 pm UTC
VC has 10 values. where vc in 'a','b','c' returns 30% of the table. full scan.
distinct i,j,k from t -- returns all rows. full scan.
where i=? returns a small number, maybe an index on I would be good.
where j=? returns a smallish number, maybe an index on J would be good.
where k=? returns 20% of the table, probable full scan.
Unless of course, you are interested in getting the first rows fastest of all, then indexes on each column would be useful.
I'd be looking at compression, to make the full scans faster and process less blocks.
maybe an IOT with compress 4 with a key of (VC,k,j,i) in that order.
but, then again, I don't know if this is read only, read write, can we use bitmaps, what about segment space compression, we could have a nice compressed table if this is relatively static and so on.....
A reader, June 26, 2005 - 5:07 pm UTC
<quote>but, then again, I don't know if this is read only, read write, can we use bitmaps, what about segment space compression, we could have a nice compressed table if this is relatively static and so on.....
</quote>
Its read-only. Its truncated/recreated in a nightly batch job and then used read-only during the day.
first_rows wont work since it is used as part of larger queries and that would interfere with those queries.
Whats your final answer?
Thanks
June 26, 2005 - 5:54 pm UTC
100,000 rows is small.
but direct path load the table sorted by VC,k,j,i (maximum compression) and consider single column bitmap indexes on each of the four columns (4 indexes)
with 100,000 rows, it should take all of a couple seconds to do all of them.
Individual indexes
A reader, June 26, 2005 - 5:34 pm UTC
Yes, if I do use a regular heap table, I would enable compression to reduce the overall size of the segment and thus speed up full scans.
"where i=? returns a small number, maybe an index on I would be good.
where j=? returns a smallish number, maybe an index on J would be good"
Right, so are you suggesting
create index i on t(i);
create index j on t(j);
create index k on t(k);
create index vc on t(vc);
Isnt that wasteful? Would a concatenated index work? What should the order of the concat index be?
"maybe an IOT with compress 4 with a key of (VC,k,j,i) in that order"
But then what if I query only by "where k=?", that would do a full index scan of the IOT, right? Would you recommend any secondary indexes on the iot?
Thanks
June 26, 2005 - 5:55 pm UTC
you have a tiny number of rows, nothing is going to be very wasteful here.
That aside if you say "where i = ?", "where j = ?" and "where k = ?", a concatenated index is the last thing you want.
Lets prototype this:
heap table.
compressed.
loaded sorted as stated above.
4 single column bitmap indexes.
A reader, June 27, 2005 - 8:57 am UTC
I will prototype this, but I just found out that the table is going to have 500K rows, not 100K.
Would that change your answer?
Thanks
June 27, 2005 - 9:19 am UTC
500k is pretty small still.
no changes.
Smita, June 28, 2005 - 3:20 am UTC
Hi Tom,
I want to understand the different types of indexes in oracle in detail.
Can u please provide me the link where i can find the indexes explained.
Thanks.
Brilliant!
A reader, June 28, 2005 - 6:47 am UTC
Tom, did anyone ever tell you, you are amazing!
The compression reduced the size of the table from 45MB to 8MB! The 4 bitmap indexes make any query using any combination of the 4 columns blazingly fast. I am a happy camper.
As always, thanks a lot
Pls. help me get this straight
A reader, July 13, 2005 - 5:21 pm UTC
Tom,
In one of the above discussion threads you mentioned that the query "select * from t where primary_key = :x" will do an index range scan. The following are my questions :
1. Why should the above query do an index range scan when we access the data thru primary key. It should do a unique index scan and table access by rowid. Right? If my understanding is not correct, please clarify
2. Also, regarding the example of stock ticker feed example, I have the following question relating to storing this data in IOT vs HEAP. The key is TICKER and DATE
On day1, we insert 1000 records and the ticker for ORCL gets stored in block1.
On day2, we insert 1000 records and the ticker for ORCL will be stored in block1 so that data with same ticker value is close to each other.
My question is : On day2, Oracle has to push out the other data inserted in block1 (non-ORCL ticker data inserted on day1) to make room for ORCL ticker to be stored in block1. Will this not be an expensive operation and will it not impact the load performance for day2 and other subsequent days? Please correct me if my understanding on this is not correct. I read the chapter on IOT in ur Expert one-on-one, but still not clear and ur help is highly appreciated.
Thanks
July 14, 2005 - 9:37 am UTC
1) it'll do an index scan. yes, for the primary key it might do a index unique or an index range, but it'll be an index access (root -> branch/branch -> leaf)
ops$tkyte@ORA9IR2> create table t1 ( x int primary key deferrable );
Table created.
ops$tkyte@ORA9IR2> create table t2 ( x int primary key );
Table created.
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t1 where x = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'SYS_C009192' (NON-UNIQUE)
ops$tkyte@ORA9IR2> select * from t2 where x = 1;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (UNIQUE SCAN) OF 'SYS_C009193' (UNIQUE)
ops$tkyte@ORA9IR2> set autotrace off
Just consider it "an index access, must traverse index to get to data"
2) IOT's, hash clusters, b*trees, all structures that enforce data "goes somewhere" are all about DATA RETRIEVAL.
ctl-f on this page and look for the comments:
...
Ask yourself "how many times do I insert a row". "how many times do I retrieve
it". "is it OK to pay a penalty during INSERTION in order to make retrieval
super efficient". "how do I really use this table". .....
"ur", not sure what a Mesopotamian city is doing here :)
http://dictionary.reference.com/search?q=ur
iot or not?
Kamal Kishore, October 25, 2005 - 8:25 pm UTC
Hi Tom,
If there was a lookup table to translate code (NUMBER, PRIMARY KEY) into its description (VARCHAR2(50)) containing at the most 10 rows, would an IOT be a better choice, given that the table is sometimes used to select based on a given code or is most of the times joined to other heap tables again based on the code?
Given your followups above, I think an IOT might be the choice. just want to confirm?
Thanks,
October 26, 2005 - 11:36 am UTC
an IOT would work well - it should be 1 LIO to full scan and 1 LIO to range scan.
CACHE?
Kamal Kishore, November 05, 2005 - 3:49 pm UTC
In place of using an IOT for such a table, we were offered the option of using the CACHE option while creating a regular heap table.
Is that a better (or at least equivalent) alternative for the use as described above or an IOT would still be the better choice?
November 06, 2005 - 8:19 am UTC
cache only changes the way oracle looks at storing the blocks in the cache when doing a full scan of a LARGE TABLE and this is not one, cache would have no effect on this.
IOT seems to make most sense - single IO versus two IO's for heap+index. full scans best (1 IO versus a couple for the heap table)
All IO's are potentionally expensive ( logical and physical ). Having something in the cache does not make it "free to access" by any means.
IOT as a transaction table in multi user scenario
A reader, November 07, 2005 - 4:40 am UTC
Since in IOT table itself is an index and index being a complex structure, it means IOT is not a good choice for transaction tables which are simultaneously hit by multiple users.
Is my understanding correct?
Will IOT be a good choice for user log tables in a web based scenario, i.e tracking user navigation from page to page from entry to exit?
November 07, 2005 - 8:56 am UTC
depends on how many attributes (how WIDE - remember a regular table will have a primary key itself - both the table and primary key index to maintain. if the width of the row is not very wide, the IOT can make sense).
depends on how often you write it as opposed to READ it and which you need to optimize the most.
Index Selection In Multi - Indexed Table.
Vikas Sangar, November 11, 2005 - 7:47 am UTC
Dear Mr. Kyte,
I just wanted to know that on what Grounds/Criteria does an Oracle database selects an Index for its use to execute/answer a query of an user, If, a particular table has a number of different Indexes on its columns?
Also, suppose if I accidently created two similar Indexes of the same type(Say Unique Index, with different names), on the similar columns of the Table, then which index will get used? Will both the Indexes will get used or, any one out of them will get used for the purpose?
What effect does the indexes of same type on similar columns, but having different names will have on the performance of querries using them and entire database performance?
Does creating multiple Indexes of different types (Bitmap, Unique, Reverse index etc) will have any adverse effect on the querries and database perfoemane, as the database mechanism has to select the best suitable index for the purpose of executing users query?
Can it be rightly termed that a particular table is overloaded with numerous number of indexes with regards to performance?
Finally, What are the best examples that can define Indexes Both as - Boom and Curse?
Take care, Regards...
Vkas.
November 12, 2005 - 8:28 am UTC
there is no such concept as "similar columns" - I'm not sure at all what you mean by that. and why would having an index be "an accident"
This is sort of a book in hiding question. I can recommend two. Both are currently on my home page - one is Expert Oracle Database Architecture by me. I have a chapter on indexing. The other is a new book by Jonathan Lewis, Cost-Based Oracle Fundamentals - explains how things are costed by the optimizer when looking at various things, including indexes.
FEED BACK - -> Index Selection In Multi - Indexed Table.
VIKAS SANGAR, November 14, 2005 - 7:48 am UTC
Dear Mr. Kyte,
Thanx a lot for your pointer(Books) towards the probable Answers to my Questions.
Certainly i'll get one for me as long as they are avialable here, in India (I have your Effective Oracle By Design with me).
All I meant by by similar column indexes was to have two indexes(Ix1, Ix2) of same type (say Unique indexes), built on the same columns (Say col1 & col2) of Table T1.
What I wanted to know was...
Both Indexes being of the same type and build on the same columns of a table (but having different Names)which one out of the two will be used by Oracle to fetch / sort records, and why? or will they both be used by Oracle for the above purpose? and how the performance is going to be effected because of it?
Take care, regards...
Vikas.
November 14, 2005 - 1:16 pm UTC
you will not have "Indexes being of the same type and build on the same columns of a table"
we don't let you index the same exact set of columns in the same order twice.
FEED BACK - -> Index Selection In Multi - Indexed Table.
VIKAS SANGAR, November 14, 2005 - 11:19 pm UTC
Dear Mr. Kyte
Thanx a lot for your Clarification with regards to my Querries related to Indexes.
Take care, Regards.
Vikas.
IOT perfomance test
Gabriel, December 02, 2005 - 10:11 am UTC
Hello Tom,
In the first followup in this thread you said:
Well, on the conventional table that'll use the primary key to find the 5 or 6
matching rows and do a separate IO to the table for each stock_ticker to get the
price.
But I reproduced the test that you did in Effective Oracle by Design for a special case where all the columns in a table are part of the primary key. Based on my tests there are no performance gains. It seemed at least logical to me that at least on inserts the IOT would be faster as the heap table has to maintain two structures instead of 1. Please tell me where I went wrong:
create table iot
(username varchar2(30),
document_name varchar2(30),
constraint iot_pk
primary key (username,document_name)
)
organization index
;
create table heap
(username varchar2(30),
document_name varchar2(30),
constraint heap_pk
primary key (username,document_name)
)
;
begin
for i in 1..100
loop
for x in (select username from all_users)
loop
insert into heap
(username,document_name)
values
(x.username,x.username||'_'||i);
insert into iot
(username,document_name)
values
(x.username,x.username||'_'||i);
end loop;
end loop;
commit;
end;
/
declare
begin for i in 1..5
loop
for x in (select username from all_users)
loop for y in (select * from heap single_row where username=x.username)
loop
insert into heap (username,document_name) values (x.username||myseq.nextval,x.username||myseq.nextval);
update heap set document_name=x.username||myseq.nextval where username=x.username;
delete heap where username=x.username;
end loop;
for y in (select * from iot single_row where username=x.username)
loop
insert into iot (username,document_name) values (x.username||myseq.nextval,x.username||myseq.nextval);
update iot set document_name=x.username||myseq.nextval where username=x.username;
delete iot where username=x.username;
end loop;
end loop;
end loop;
end;
/
********************************************************************************
SELECT *
FROM
HEAP SINGLE_ROW WHERE USERNAME=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 39 0.00 0.00 0 0 0 0
Fetch 3939 0.26 0.16 0 4097 0 3900
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3979 0.26 0.17 0 4097 0 3900
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 71 (recursive depth: 1)
********************************************************************************
SELECT *
FROM
IOT SINGLE_ROW WHERE USERNAME=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 39 0.02 0.00 0 0 0 0
Fetch 3839 0.26 0.17 0 3976 0 3801
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3879 0.28 0.18 0 3976 0 3801
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71 (recursive depth: 1)
********************************************************************************
INSERT INTO HEAP (USERNAME,DOCUMENT_NAME)
VALUES
(:B1||myseq.NEXTVAL,
:B1||myseq.NEXTVAL)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3900 7.46 7.91 0 4689 27417 3900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3901 7.46 7.91 0 4689 27417 3900
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71 (recursive depth: 1)
********************************************************************************
INSERT INTO IOT (USERNAME,DOCUMENT_NAME)
VALUES
(:B1||myseq.NEXTVAL,
:B1||myseq.NEXTVAL)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3801 7.42 7.60 0 3349 20481 3801
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3802 7.42 7.61 0 3349 20481 3801
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71 (recursive depth: 1)
********************************************************************************
UPDATE HEAP SET DOCUMENT_NAME=:B1||myseq.NEXTVAL
WHERE
USERNAME=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 3900 8.18 8.72 0 30315 38246 3900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3901 8.19 8.72 0 30315 38246 3900
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71 (recursive depth: 1)
********************************************************************************
UPDATE IOT SET DOCUMENT_NAME=:B1||myseq.NEXTVAL
WHERE
USERNAME=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3800 7.81 8.07 0 26859 27138 3800
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3801 7.81 8.07 0 26859 27138 3800
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71 (recursive depth: 1)
********************************************************************************
DELETE HEAP
WHERE
USERNAME=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3900 1.29 1.30 0 26300 16390 3900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3901 1.29 1.30 0 26300 16390 3900
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71 (recursive depth: 1)
***********************************************************************************
DELETE IOT
WHERE
USERNAME=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3800 1.24 1.06 0 23803 383 3800
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3801 1.24 1.06 0 23803 383 3800
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 71 (recursive depth: 1)
********************************************************************************
December 02, 2005 - 11:23 am UTC
why do you believe you went wrong?
I would expect the same - if there are no other columns than primary key columns - I would expect not much difference.
you are retrieving everything from an index structure regardless here, the table never came into play.
What about inserts, deletes, updates
Gabriel, December 02, 2005 - 12:20 pm UTC
Hello Tom,
Thank you for your answer.
I was not surprised about the select performance but mostly by the insert/update/delete. I though that the heap table will perform relatively poor because it has to maintain the index blocks and the table blocks while the IOT only has the index block to maintain. How would you explain their similar performance for these operations (insert/update/delete)
Thank you,
December 02, 2005 - 12:25 pm UTC
they will be very different from a scalability perspective, look at the delete:
DELETE IOT WHERE USERNAME=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3900 1.29 1.30 0 26300 16390 3900
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3901 1.29 1.30 0 26300 16390 3900
****************
DELETE IOT WHERE USERNAME=:B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 3800 1.24 1.06 0 23803 383 3800
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3801 1.24 1.06 0 23803 383 3800
the increase in LIO (query and current mode gets - current mode gets being even more expensive then query mode gets...) will inhibit the scalability of the heap table in this case will kick up the cpu of the heap approach in the multi-user situation.
That is the main point of the IOT - to decrease LIO to increase scalability. More users getting less LIO's is generally better than more users getting more LIO's since the LIO's require latching.
Marius
A reader, April 26, 2006 - 12:59 pm UTC
I searched the net(including ths site) a lot and I'm almost sure an IOT would be better in our case, but not 100%.
We have a many to many relation table(assignments table if you wish) that stores various assignments between objects in our system.
This is what it looks like:
(
otype_1 int not null,
oid_1 int not null,
otype_2 int not null,
oid_2 int not null,
CONSTRAINT unq_r_oo UNIQUE(otype_1, oid_1, otype_2, oid_2)
)
An object in our system is identified by a tuple <object type id, object id>.
This table is not that big(a few millions) but it will grow rather fast.
Most of the times this table is used for getting all objects<otype_2, oid_2>(let's call it T2) assigned to an object, so it's nice to have them together, but sometimes it is also used for retrieving T1 based on T2.
This table is much more often read than updated/inserted into, say 90% is read and 10% is write.
Do you think it would be better to have it as an IOT?
I also read about some IOT related bugs that started surfacing a while ago. That kinda worried me, especially since this app must be able to run on 8i.
April 27, 2006 - 8:05 am UTC
There are heap table related bugs too.
And clustered tables
And external tables
And .....
But anyway - if you need to go "both ways", I'm not sure an IOT would be best - it would be perfect if you needed to go T1 to T2 only , but to go T2 back to T1, you would need a secondary index on the IOT and they can be problematic from a performance perspective (you will likely have TWO index range scans navigating from T2 to T1 - one on the secondary index on the IOT to find the T2 entry and on on the IOT by primary key to find that entry).
Just checked...
Marius, April 28, 2006 - 11:02 am UTC
So I just created two tables, each having about a million records.
The table cms_r_ooi is an IOT and the cms_r_ooh is a standard heap table. Both tables have an index on (otype_2, oid_2) compress 1.
The results were checked on 8.1.6.0.0, 10.1.0.2.0 and on XE, all behaved the same:
test1@ora8i>analyze table cms_r_ooi compute statistics;
Table analyzed.
Elapsed: 00:00:32.75
test1@ora8i>analyze table cms_r_ooh compute statistics;
Table analyzed.
test1@ora8i>SELECT otype_1, oid_1 FROM cms_r_ooi WHERE otype_2 = 1 AND oid_2 = 1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 INDEX (RANGE SCAN) OF 'IDX_CMS_R_OOI' (NON-UNIQUE) (Cost=3 Card=1 Bytes=17)
test1@ora8i>SELECT otype_1, oid_1 FROM cms_r_ooh WHERE otype_2 = 1 AND oid_2 = 1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=18)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'CMS_R_OOH' (Cost=2 Card=1 Bytes=18)
2 1 INDEX (RANGE SCAN) OF 'IDX_CMS_R_OOH' (NON-UNIQUE) (Cost=3 Card=1)
test1@ora8i>SELECT otype_2, oid_2 FROM cms_r_ooi WHERE otype_1 = 1 AND oid_1 = 1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_66356' (UNIQUE) (Cost=3 Card=1 Bytes=17)
test1@ora8i>SELECT otype_2, oid_2 FROM cms_r_ooh WHERE otype_1 = 1 AND oid_1 = 1;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=18)
1 0 INDEX (RANGE SCAN) OF 'SYS_C0050095' (UNIQUE) (Cost=3 Card=1 Bytes=18)
So, it looks like the IOT is up to the task, or am I missing something?
Since the IOT will also save some space, meaning less I/O, I guess I'll just use it.
April 28, 2006 - 12:47 pm UTC
I've no idea what I'm looking for or at here.
I see no measurement of work done (tkprofs).
I see no test case we can work with either (to reproduce your results)
The problem with the IOT is that you need to have a secondary index on it. Over time, you'll end up doing TWO RANGE scans when going from T2 to T1 - one on the secondary index, to get a primary key and then another on the IOT.
when to use IOT
A reader, June 06, 2006 - 1:18 pm UTC
Hi
I have read many thing about IOT, the impression I have is if you primary key contains most of columns of the table then IOT is a good idea am I correct? If I am then what would be the threshold to determine when to use IOT? 80% of columns used in PK lets say?
June 06, 2006 - 2:00 pm UTC
it is somewhere between 1% and 100% of the columns.
It is not the number of columns in the key, it is whether you would benefit from the fact that the entire table is stored sorted in the index structure
or not.
IOT
saradha, August 30, 2006 - 11:29 am UTC
I had a table with 6 columns and the primary key being on two number columns. I used the organised Index while creating the table. This table had 85000 Records. I compared the performance of fetching a single row with a range scan between IOT table and a normal indexed table. The normal indexed table seems to be better. Why is this?
August 30, 2006 - 5:38 pm UTC
"seems to be better"
prove it. show us. quantify it, measure it and quantify the "seems"
for example:
ops$tkyte%ORA9IR2> /*
DOC>drop table t1;
DOC>drop table t2;
DOC>
DOC>create table t1
DOC>( a ,
DOC> b ,
DOC> c , d, e, f ,
DOC> constraint t1_pk primary key(a,b)
DOC>)
DOC>as
DOC>select object_id, rownum,
DOC> owner, object_type, status, created
DOC> from dba_objects
DOC>/
DOC>
DOC>create table t2
DOC>( a ,
DOC> b ,
DOC> c , d, e, f ,
DOC> constraint t2_pk primary key(a,b)
DOC>)
DOC>organization index
DOC>as
DOC>select * from t1;
DOC>
DOC>exec dbms_stats.gather_table_stats( user, 'T1' );
DOC>exec dbms_stats.gather_table_stats( user, 'T2' );
DOC>*/
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
2 cursor c is select a, b from t1;
3 type array is table of c%rowtype;
4 l_data array;
5 l_rec t1%rowtype;
6 begin
7 open c;
8 fetch c bulk collect into l_data;
9 close c;
10
11 execute immediate '
12 alter session set events ''10046 trace name context forever, level 12''
13 ';
14
15 for i in 1 .. l_data.count
16 loop
17 select * into l_rec from t1 where a = l_data(i).a and b = l_data(i).b;
18 select * into l_rec from t2 where a = l_data(i).a and b = l_data(i).b;
19 end loop;
20 end;
21 /
PL/SQL procedure successfully completed.
Now, the tkprof shows they perform about the same <b>in single user mode</b>
SELECT * FROM T1 WHERE A = :B2 AND B = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 31216 4.40 4.21 0 0 0 0
Fetch 31216 0.42 0.38 0 93648 0 31216
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62433 4.82 4.60 0 93648 0 31216
Rows Row Source Operation
------- ---------------------------------------------------
31216 TABLE ACCESS BY INDEX ROWID T1
31216 INDEX UNIQUE SCAN T1_PK (object id 34103)
********************************************************************************
SELECT * FROM T2 WHERE A = :B2 AND B = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 31216 4.44 4.25 0 0 0 0
Fetch 31216 0.36 0.30 0 62432 0 31216
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 62433 4.81 4.55 0 62432 0 31216
Rows Row Source Operation
------- ---------------------------------------------------
31216 INDEX UNIQUE SCAN T2_PK (object id 34105)
<b>but I will put forth that the IOT will scale better retrieval wise in the long run due to the REDUCE LOGICAL IO, the query mode gets.
Each query mode get there will use a latch.
Latches are a type of lock
Locks are a serialization device
Serialization devices inhibit scalability.
Therefore, as more concurrent users are added - the amount of CPU needed to process an individual query will start to increase (as people start spinning on latches) so the query that does more logical IO's to perform the same work - it will scale 'not as well' - it will take more CPU faster than the second one will as you ramp up</b>
IOT
Kishore, March 14, 2007 - 7:01 am UTC
Hi Tom,
Are IOTs always used when you have large table. If I have a table which has 100 - 150 rows that doesn't change frequently and out of the 6 columns in the table 4 are primary key and the table is mostly accessed by the primary key. Is there any reason to have table as IOT or a normal table with B* index would be as good as that. Would IOT not save on space as we need to store everyting in index structure.
Thanks
Kishore
March 14, 2007 - 8:06 am UTC
"Is X always used when Y is true"
I can say the only reasonable answer to a question of that form is "It depends"
100-150 rows is tiny.
if I had a 6 column table were 4 of the columns where the primary key and the other two were reasonable in size (eg: all fit on much less than 1/2 of the block) and we accessed by primary key most all of the time - an IOT would make sense to reduce the amount of logical IO and the storage required.
reader
A reader, April 26, 2007 - 5:08 pm UTC
I have a 10g 10.2
WHen I query from the dba_tables for an index organized table, the tablespace shows blank, the iot_name is blank also;
I created the table with
create table .....
organiztaion index
tablespace tsname;
I try to find why the tablespace_name shows blank
April 27, 2007 - 10:25 am UTC
because there is no table.
there is an index.
reader
A reader, April 27, 2007 - 1:40 pm UTC
Thanks. Is this new in 10g for IOT
The instance 9.2 lists the tablespace in dba_tables but dba_indexes has no entry
The instance 10.2 does not list the tablespace_name in dba_tables nor in dba_indexes
As for the OEM, both database instances show the tablespace name in the "general" tab. However either of them show as greyed out
April 27, 2007 - 5:26 pm UTC
show us, I don't see that
ops$tkyte%ORA9IR2> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE 9.2.0.8.0 Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production
ops$tkyte%ORA9IR2> @test
ops$tkyte%ORA9IR2> drop table t purge;
drop table t purge
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create table t ( x int constraint t_pk primary key, y int ) organization index;
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select 'i', 'user', index_name, tablespace_name from user_indexes
2 union all
3 select 'i', 'all', index_name, tablespace_name from all_indexes where owner = user
4 union all
5 select 'i', 'dba', index_name, tablespace_name from dba_indexes where owner = user
6 union all
7 select 't', 'user', table_name, tablespace_name from user_tables
8 union all
9 select 't', 'all', table_name, tablespace_name from all_tables where owner = user
10 union all
11 select 't', 'dba', table_name, tablespace_name from dba_tables where owner = user
12 /
' 'USE INDEX_NAME TABLESPACE_NAME
- ---- ------------------------------ ------------------------------
i user T_PK USERS
i all T_PK USERS
i dba T_PK USERS
t user T
t all T
t dba T
6 rows selected.
........
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
ops$tkyte%ORA10GR2> @test
Table dropped.
Table created.
' 'USE INDEX_NAME TABLESPACE_NAME
- ---- ------------------------------ ------------------------------
i user T_PK USERS
i all T_PK USERS
i dba T_PK USERS
t user T
t all T
t dba T
6 rows selected.
pros of IOT
prasanna, July 18, 2007 - 4:53 pm UTC
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...
IOT plausible while allowing the PK to change?
Adam, March 29, 2008 - 11:07 pm UTC
Using your document management system example:
"think of a document management system. primary key is (userid,document_name)
most frequently executed query is:
select * from docs where userid=:x;
now the documents arrive continuously from many users (so each of my docs are far from each other in the heap table - just like the stocks), but we retrieve all of them for a user (or all of the most
recent -- whatever)."
Can you / should you use an IOT if you intend to allow the owner of a document to change from time to time?
If so, what actually happens when you change the PK, modifying the userid of a given document? Is the record, moved to a new leaf block?
If not, can you recommend an alternate table structure to help store the data onto as few blocks as possible given the number of documents per userid is unknown, and are added to the table randomly over time? I presume these load conditions would exclude the consideration of a cluster, unless you could reliably predict the number / size of the records to size the cluster and are willing to accept any wasted space. (one individual with 500 documents vs another with 30)
If not, perhaps running with the IOT; and deleting / reinserting the record for the document and new owner is a viable option. Pending analysis on the frequency of such transactions.
Thank you for your time and thoughts.
March 31, 2008 - 8:35 am UTC
... Can you / should you use an IOT if you intend to allow the owner of a document
to change from time to time?
...
no, now you might use a b*tree cluster, hash cluster instead - to achieve the clustering of documents by "current owner"
if the owner changes - it is not the primary key at all. So the IOT would not be applicable.
Using IOT for Percentile Calculations
Thiru, April 24, 2008 - 1:10 pm UTC
Tom,
It was a pleasure seeing you for the first time in Bridgewater NJ and gaining valuable insights into 11g.
I am in the process of designing a table the data of which will be used to calculate N-Percentile for the range of data.
a) Will an IOT be a good approach? As the data needs to be sorted for calculating the 95th Percentile for example, I thought we could avoid the big sort by letting Oracle sort in the first place while inserting the records.
b) Is there any Analytic Function to find out the value of the 95th Percentile? So if my sample data is let's say 1000 records and then sorted descending, I would have to ignore the top 50 records ( 5% of 1000) and then return the 6th recod value.
Limitations or downsides to IOT?
Rama Manneri, October 17, 2008 - 6:36 pm UTC
Tom,
Most of the material and oracle documentation is talking about advantages of IOT. I'm trying to find limitations of IOT and not getting any hits..Could you elaborate on the limitations of IOT as compared to regular tables?
October 18, 2008 - 10:01 pm UTC
secondary indexes can be an issue.
rows in an IOT don't have a true rowid. So, if you create, populate and then index an IOT - the secondary index will have in it:
a) the columns you indexed
b) a rowid HINT, a rowid GUESS, the rowid observed when the row was first seen in the index - this is the rowid of the leaf block, it is not a true rowid
c) the logical rowid (in actuality the primary key of the IOT row being indexed)
As you modify the IOT, rows will move due to block splits, the secondary indexes will become less efficient as the rowid GUESS goes stale. When you say 'select * from iot where secondary_indexed_columns = ??' over time we will
a) range scan the secondary index
b) use the rowid guess to see if the row is there
c) upon not finding it - find the logical rowid of the IOT row
d) range scan the IOT to find the row by primary key
So, in a read/write system (eg: not a warehouse), the rowid guess'es go stale and we end up doing N range scans - one to find the entries in the secondary index and then N-1 to retrieve the N-1 rows from the IOT (and maybe even an IO for the rowid hint until we find so many stale we stop using it).
And then you rebuild the secondary index and all is well, for a while - and so on.
So, one consideration is "will I need access by attributes other then the leading edge attributes of the primary key" - if the answer is YES and this is a transactional table - you probably don't want an IOT.
The other consideration is when the row is more than 40% the size of a block - then you are into overflow segments.....
The other consideration is that inserts are in general going to be slower since the rows are fatter than just the primary key, you get less rows per leaf block, the index splits much faster than a primary key index by itself would - and that is the "slow thing" about indexes in general...
Revivo, August 01, 2009 - 7:54 pm UTC
In your book you give examples with primary key indexes,
if those indexes are normal index not a primary key, Will there be any difference in terms of result?
August 04, 2009 - 1:02 pm UTC
I give examples of what?
Not sure what you mean.
A primary key needs an index to be enforced. This index may be unique or non-unique. The index used to enforce a primary key constraint is exactly precisely the same as any other index - it is just an index.
A reader, August 14, 2009 - 9:53 pm UTC
In order to use IOT, majority of the columns should be primary key.
Is it also same if majority of the columns have normal index rather than primary key?
August 24, 2009 - 8:02 am UTC
... In order to use IOT, majority of the columns should be primary key. ...
false premise, not true. why do you believe that?
Scofield
A reader, September 05, 2009 - 2:24 am UTC
Respected sir,
In your book, you mentioned that if the transactions based on primary keys, they may be
good candidate for IOT.
Ex:
where=primary key
and=primary key
Is this scenerio also true, if these columns have not got primary key, but have index instead
September 06, 2009 - 9:20 am UTC
well, I never said anything so simplistic there (if you think I did, please quote it and refer to the exact document - Expert one on one Oracle, Effective Oracle by Design, Expert Oracle Database Architecture)
The use cases I used were:
o a table like stock quotes where the data has a primary key of (symbol,date) - the stock symbol and the date of the stock price. the data arrives "out of order" (we tend to query A SYMBOL given some DATE RANGE, but the data arrives by date, not by symbol, date). We would like all of the ORCL data to be stored together (bonus: sorted by date) and all of the IBM data together and so on - hence an IOT with a primary key of (symbol,date) might be called for. There a query such as "where symbol = ? and date between ? and ?" would be very easy to retrieve the data for.
o lookups, like zip_code to state lookups. Virtually any and all lookups.
o association objects - used to represent many to many relationships - especially if either
a) the relationship is walked in one direction (so no secondary indexes) or
b) the relationship is walked in both directions, but the data is loaded and indexed and then queried over and over - so the secondary index maintenance isn't a problem.
but that said, I don't know what to make of the question:
... Is this scenerio also true, if these columns have not got primary key, but have
index instead ...
IOTs are only about primary keys - without a primary key, you have no IOT.
A reader, September 13, 2009 - 1:39 pm UTC
Tom ,
Per your comment a table can have a virtually unlimited number of rows.
Assuming , if I run into 100 billion records over the next 3 years ( hash partition by key ) . What would be the performance impact , if the records are accessed via key . This is a OLTP system.
What would be better way to benchmark these kind of tests?
We are on 10g Release 2 .Storage is EMC.
Please advise.
Thanks
<quote>A table can have a virtually unlimited number of rows, although you will hit other limits
that prevent this from happening. For example, typically a tablespace can have at most
1,022 files (although there are new BIGFILE tablespaces in Oracle 10g that will get you
beyond these file size limits, too). Say you have 32GB files¿that is to say, 32,704GB
per tablespace. This would be 2,143,289,344 blocks, each of which is 16KB in size. You
might be able to fit 160 rows of between 80 to 100 bytes per block. This would give you
342,926,295,040 rows. If you partition the table, though, you can easily multiply this
number many times. For example, consider a table with 1,024 hash partitions¿that
would be 1024 ¿ 342,926,295,040 rows. There are limits, but you¿ll hit other practical
limitations before even coming close to these figures.</quote>
September 14, 2009 - 1:33 pm UTC
... hat would be the performance impact , if the records are
accessed via key ...
probably - nothing to not much if even measurable.
... What would be better way to benchmark these kind of tests? ...
there is only one way - that is to do it? Not sure what you mean.
A reader, September 14, 2009 - 2:16 pm UTC
Thanks Tom .
Would you be able to share of some of Oracle's customers who have these kinda of data volume and did not expericence any performance impact. I guess Yahoo is one of them .
September 14, 2009 - 2:58 pm UTC
think about the structure please
index gets you to rowid
rowid is address of row
have address of row - you have the row
you control the size of the indexes (you control ultimately the size of any partition, all partitions). The size of the index will tell us if we spend 1, 2, 3 or 4 IO's on the index (index general, an index will be '3 high' even against millions of rows - then 4, maybe 5 - but not TALL, not dozens of IOs to get to the leaf, single digit).
And then once you get to the leaf, everything you need is right there - rowids, it takes no longer to pull a row by rowid from a 1 row table than it does a 1 kabillion row table - file, block, slot on block - immediate access.
You can query around to find many people using large database (winter corp evaluates a lot of this stuff). But first, just think about the STRUCTURES involved here and they they do, how they work. Then you can just close your eyes and visualize the work necessary and realize.....
It'll be around the same, regardless.
index organized tables
Reader, April 06, 2010 - 12:30 am UTC
Hi Tom,
Why is an insert into IOT slow? Can you please elaborate? What about deletes and updates?
Regards
April 12, 2010 - 6:57 pm UTC
It is typically "slower", not "slow".
Data in an index has a location - a place to be. Normally, when you insert into a heap table - the data just goes where ever free space happens to be and then you modify a small primary key index. Putting the table data away is "fast" (don't need to do much work to find free space and put it there), maintaining the index is fast (because it is a small number of columns - typically one - that is in the index, the index doesn't grow very fast).
Compare this to an IOT - you have to find where the row will go (it cannot go just anywhere, it has to go where the primary key says to go). Then you quite possibly and frequently have to MAKE SPACE for it (the index leaf blocks fill up fast - since there are MANY columns in the index now - all of them are). So, the index grows rapidly and index maintenance is hard - the more leaf splits we have to do - the more work we have to do.
Think of it like this - suppose you were given a pile of laundry. You could just take the clothes you got and find a drawer in your chest of drawers with fre space and put them in there. You would be really really fast at putting your clothes away.
Compared to someone that decided "shirts go here, shorts go there, socks over here and so on". They would take longer to put away a random pile of clothes.
However, which one would be able to get together there clothes on a monday morning to get ready for work? Probably the one that said "my shirts go here, in this drawer" - because they can find a shirt immediately, then shorts, then socks and so on. They do not have to look all over the place for their stuff - it is organized.
These structures (IOTs, clusters, partitioned tables, etc) are generally slower to insert into (because we have to place things specifically) than HEAP tables, but are generally faster to retrieve from - because we took the time to put things away neatly and put related items away together.
index organized tables
Reader, April 08, 2010 - 8:15 am UTC
Hi Tom,
Would greatly appreciate if you could answer the above question.
Regards
logical rowid
A reader, October 02, 2010 - 11:45 am UTC
Hi Tom,
I would like to ask few queries about logical rowid.
1) Why in an IOT, its rowid is precomputes the value to be inserted w.r.t primary key. i.e. If I insert primary key as 1111,1112,1115 respectively then I have noticed that the rowid for 1112 and rowid for 1115 keeps a definite gap (much like *...A/g for 1112 and *...D/g for 1115) so that primary keys 1113,1114 can be accommodated and corresponding rowid is assigned. How does it happen?
2) What is *BAEAAOoDQUdT/g" meant for?
3) I read in a document about logical rowid that: Rows in index-organized tables do not have permanent physical addresses—they are stored in the index leaves and can move within the block or to a different block as a result of insertions.
Is this because when the insert take place in an IOT, it stores the data on sorted order of primary key? Can you give an example?
4) Why an "alter table test_iot move" cannot organize the rowid in a particular order which happens in an ordinary table? I have even seen that nothing in rowid is altered.
5) Can you please explain this:"Each logical rowid used in a secondary index can include a physical guess, which identifies the block location of the row in the index-organized table at the time the guess was made"?
October 04, 2010 - 2:00 am UTC
as I wrote on another page for you - I suggest you get a copy of Expert Oracle Database Architecture OR the 11gR2 Server Concepts guide. I go into this stuff in some detail in those books.
1) I don't know what you mean. The rowid in an IOT is a logical rowid - it includes the primary key of the row (so we can find it again) and a rowid guess (the location where the row was first inserted in the index - the IOT). Since rows move in indexes (index leaf splits move rows from block to block) frequently, the rowid guess goes stale often - so we need the primary key to find the row again.
so, when you are looking at 1112, 1115 - and you see that "gap", that is because the rowid for a row in an IOT actually has the primary key of the row in it. It is not precomputing anything - it is just using the primary key value.
2) it is a base64 encoded representation of the rowid guess and the primary key value and other object information
3) it is just normal index structure, in an index - rows have a place they must go - where they must be. In order to insert a row in the right place - we often have to move other rows (this is called index leaf splits). IOTs are no different. if the block a new row must go on is currently full - we have to split it into two blocks - half the rows MOVE to a new block.
4) I don't know what you mean by "cannot organize the rowid in a particular order which happens in an ordinary table? "
But now you know the rowid is primarily "the primary key", you can see why they do not have to change
5) that guess is the location of the row when the row was first inserted. As the row moves because of leaf block splits - the guess goes stale.
Index guess
A reader, October 04, 2010 - 2:56 pm UTC
Hi Tom,
Can you please explain so called physical guess in index? Why do we need it?
October 05, 2010 - 11:44 am UTC
cut and paste from above:
1) I don't know what you mean. The rowid in an IOT is a logical rowid - it includes the primary key of the row (so we can find it again) and a rowid guess (the location where the row was first inserted in the index - the IOT). Since rows move in indexes (index leaf splits move rows from block to block) frequently, the rowid guess goes stale often - so we need the primary key to find the row again.
from that you can deduce that the physical (rowid) guess is:
a) the rowid, the physical address, the location of, the row in the IOT when it was first inserted. It is the actual location of the row upon insertion.
b) rows in IOT's move in indexes (due to block splits). That changes their addresses - their physical rowid changes.
c) since I said they go stale (the physical guess) we must not update them when the row moves due to a block split - so they go "stale", out of date.
The rowid guess is used to provide instant access to the row as long as the row has not moved. If the row has moved, then the guess is "stale" and we cannot use it to find the row - we'll have to use the primary key of the row in the IOT to find the row (another index scan...)
The rowid guess is used to optimize retrieval of the row - if the row hasn't moved, then we can retrieve the row from an IOT as fast as as row from a 'normal' table. If the row has moved- and the guess is stale- then we need to do another index scan in order to find and retrieve the row.
Secondary Index
Adam, January 17, 2012 - 8:55 am UTC
Suppose my secondary index rowid "guess" has gone stale because the row in my IOT has moved.
When the row is found via a range scan on the IOT, will the secondary index be updated with the new rowid?
Or is the entry in my secondary index forever associated with the original rowid?
January 17, 2012 - 3:30 pm UTC
.. will the secondary index be
updated with the new rowid? ..
no
you would need to rebuild/reorg such an index in order to freshen the secondary index.
IOT vs Regular Table
David, April 25, 2012 - 2:34 pm UTC
Tom,
Could IOT be a good choice even if 80% of operations on the object are insert ?
Test Case :
MYTAB - 28.000.000 lines
A DATE
B VARCHAR2(40)
C VARCHAR2(21)
D VARCHAR2(12)
E NUMBER(6,0)
IX1 (PK) : B,A,C
IX2 : A
Size in my production :
MYTAB : 4,7GB
IX1 : 8,2GB (PK)
IX2 : 4,7GB
TP Profile :
78% : insert into MYTAB values(TRUNC(SYSDATE),'VALB','VALC','VALD',VALE)
10% : select D,E from MYTAB where A=TRUNC(SYSDATE) and B='VALB' and C='VALC';
7% : select A,C,D,E from MYTAB where B='VALB';
5% : update
BATCH Profile :
1/night : DELETE FROM MYTAB where A < TRUNC(SYSDATE-30);
As you can see, PK index is twice larger than MYTAB. This is not very good for TP response time.
I wonder me if in this case an IOT could help me.
I know that generally an IOT is not very fine with insert/delete/update (80% insert in our case), but there are always exceptions ;-)
When I see objects size and depth of the PK, I said me that cost of PK index processing would not be so far than an IOT.
Actually, we only use MYTAB to host D and E values.
An other option could be to shrink/rebuild once a week IX1 and IX2 to reduce size of these indexes and hence reduce LIO.
To resume :
1. Transform MYTAB in IOT with secondary index on A + once a month do "ALTER MYTAB MOVE ONLINE" to regorganize IOT.
2. Keep MYTAB in HEAP format and regulary rebuild/shrink IX1 and IX2.
3. Use partitioning (30 partition / 1 partition by day) : (better choice, I think) => Unfortunatly I can't use it because we don't purchase this option.
Could you help me please to figure out ?
Thanks.
April 25, 2012 - 3:11 pm UTC
why do you have an index on A? The delete is going to hit rows all over the table - might as well just full scan it. That index would be used to access 1/30th of the data it seems (or less) - and that data would be scattered all over the place since the data would be sorted by B
also, why not use partitioning to perform the "delete" as a truncate?
I don't know what "TP" is or means....
An IOT can be dandy for insert/update/delete - it is predominantly secondary indexes you have to worry about (the index on A). An IOT does not have rowids, does not have fixed rowids anyway, for the rows in it, rows can and will move around. therefore, a secondary index on an IOT has what we call a rowid hint (the rowid of the row when the rowid was inserted into the IOT) and a logical rowid (basically the primary key of the row stored in a base64 encoded string). You would find your secondary index on A to be about as large as the IOT itself and you'd find the rowid hints becoming stale over time which means that the index range scan/table access by rowid turns into an index range scan/table access by primary key - almost 1,000,000 times (1/30th of your data....)
So, I think you should
a) get rid of the index on A
b) prototype the IOT
c) benchmark it
d) implement partitioning
IOT vs Regular Table
David, April 25, 2012 - 4:56 pm UTC
Thank you for your precious advices.
TP is Transactionnal processing (OLTP).
You've right, index on A is useless.
I am going to do a benchmark to compare the two solutions.
And what about IOT MOVE ONLINE ? Do you think it's a good idea to regularly schedule this operation since we remove 1/30 of table every day.
I understand that partitioning is the best way but it's not a technical problem, it's money problem...
Regards,
David.
April 25, 2012 - 5:36 pm UTC
you'll reuse that 1/30th of the table - so no, I don't think a reorg would be called for. Is the current table growing and growing and growing - even as the number of records remains the same? If not, a reorg wouldn't be called for at all.
about the iot
A reader, July 29, 2012 - 7:52 am UTC
Hi,tom
I have read your book 'expert one on one' ,from the book i know
what an iot table is ,and i did some experience .using the tool 'autotrace' ,i found the iot table can reduce the consistent gets and physical reads,this's very useful for query
Q1:does an iot table has other advantages?
Q2:on your book 'expert one on one' page 271 you said:the fact is that an index is a complex data structure that requires a lot of work to manage and maintain. but ,how do we balance between the extra work and the benefit?
thanks in advance!
July 30, 2012 - 11:54 am UTC
q1) storage, possible reduced storage
q2) benchmark, simulate, evaluate. "testing"... If you understand what it is and how it works, you should be able to guess whether or not it will benefit you in YOUR specific situation. then you can set up a simulation - so you can benchmark the performance - and evaluate the numbers