Yes it is in the book
Doug, July 08, 2002 - 12:15 am UTC
Yes I did find it in the book and am re-reading the tables chapter. You mention that the data being co-located is more significant that it being in order. On page 224 you imply that it really is in order, just like a Sybase clustered index. When starting in your index clustered tables discussion you mention - "It is not storing the data sorted - that is the role of the IOT"
From a book entitled "Sybase Architecture and Administration" by John Kirkwood.
"Sybase uses B-tree to index the tables and has two types: clustered and non-clustered. .... Therefore a Sybase table with a clustered index has the data records maintained in sequence of the index field"
So basically - the 110% you refer to is because Sybase still uses an index on the data even though the data is forced to be ordered by the index? The index is sparse, which is I guess why it isn't 200%?
Also, the data in an IOT *is* in order isn't it? Even if there are block splits etc., won't the structure itself maintain both co-location and sorting order? a,b,c,d,e etc?
Thanks.
July 08, 2002 - 7:14 am UTC
On page 224 - i explicity say a couple of times "the data is not stored in order", "the data is not in order -- that is what an IOT is for". I cannot find the implication myself...
The 110% is DOUBLE (200% would be triple). In the worst degenerate case -- where the primary key is almost the entire row -- the index is no longer "sparse" and because of the overhead of the index structure itself, the data can be doubled.
The data in an IOT is ordered -- yes, I say that and it is true. BUT -- the data is still not physically ordered on disk (neither in Sybase or an in an IOT). That may be considered a matter of semantics -- but the rows can (and will be) very very far apart from eachother. An index is a data structure that is managed a block at a time. The "second" block in the index may be very very far from the first. It gives the ILLUSION of being stored sorted, but on disk -- the data is just a big jumble of stuff waiting to be put back together.
IOT and Primary Key Order
Subhrajyoti Paul, September 11, 2002 - 8:05 am UTC
Tom,
The oracle 8i conecepts manual page 10-40 has a comparison of HEAP table with IOT. It says, in an IOT, a full-index scan returns all rows in primary key order. I tried to check it out by doing the following...
ptsdba@ptsmig.world> create table iot_objects(id, oname, owner, status, primary key(id, owner)) organization index as select rownum*2, object_name, owner, status from dba_objects;
ptsdba@ptsmig.world>insert into iot_objects
select r, object_name, owner, status from
(select rownum r, object_name, owner, status from dba_objects)
where mod(r,2)=1;
ptsdba@ptsmig.world>commit;
ptsdba@ptsmig.world>select id from iot_objects;
Now this query returned me 34852 rows... The first 61 rows came in order
1
.
.
61
458
But the 62nd row was the value 458 and then there was some more contigous nos and then again there was some gap...
I go the value 62 in the 14279th row...
Now this does not seem to be in order. I am selcting the first column in the primary key which is supposed to be most significant in deciding the order.
The Explain Plan is like this...
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=59 Card=1 Bytes=4)
1 0 INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_45935' (UNIQUE) (Co
st=59 Card=1 Bytes=4)
I am confused... I guess I am doing something wrong...But whatever I do, as per the manual, the primary key order should be maintained...
I am using Oracle 8.1.7 on sun solaris... I did not include the output of the select query as it was 34852 lines long...;-)
September 11, 2002 - 8:36 am UTC
It did a FAST FULL SCAN, not an INDEX FULL SCAN.
They are different. In a FAST FULL scan, we read the blocks as they exist on disk -- we do multi-block IO. We read a mixture of root block, branch (interior) blocks, and leaf blocks -- we through out all of the root/branch blocks and just dump the data found in the leaf blocks. We don't process the blocks in "sorted order" but the order they are on the disk.
In an index full scan, we start at the root block, read a branch block -- get to a leaf block and then read each and every leaf block in "sorted order" using single block IO's (read one leaf block at a time).
Bottom line -- just always remember this -- unless you have an ORDER BY on your query, the data is coming back to you in SOME ORDER. Unless you have an ORDER BY, the data will come back in SOME ORDER. It will not be sorted, it will not be the order of insertion, it will be in SOME ORDER and that order can change from run to run of the query even !!!! different plans result in different orders!!!
I did not know they were different...Thanks....
Subhrajyoti Paul, September 11, 2002 - 8:52 am UTC
I guess... I will have to study your chapter on Index ASAP, otherwise I will keep on asking this dumb questions...:))
difference between clustered index and non-clustered index
subash, August 24, 2007 - 12:40 am UTC
Could you please tell me the differences between the clustered and non-clustered indexes?.My understanding is that we don't have the concept of non-clustered indexes in oracle. Please correct me, if it's wrong
August 24, 2007 - 2:51 pm UTC
we have b*tree clusters that rely on a b*tree clustered index.
we have index organized tables - indexes that store the entire table data (index, no table)
we have b*tree indexes
we have bitmap indexes
and it would take far too long to re-type in everything I've written about them (and compare them to sybase/ms sqlserver).
Expert Oracle Database Architecture does that (link on asktom home page)
The concepts guide is freely available and a great source of this information as well:
http://www.oracle.com/pls/db102/portal.portal_db?selected=3#index-CON