Home » SQL & PL/SQL » SQL & PL/SQL » Indexes
Indexes [message #239463] Tue, 22 May 2007 08:15 Go to next message
akkumar81
Messages: 144
Registered: February 2007
Location: india
Senior Member

hi friends,

In a table 1000 of records.first 100 records are null records no values are there .so that situation i choose in which index?
Can i use iot? what is the use of IOT?

Please ..expecting your replies.

Arun..

[Updated on: Tue, 22 May 2007 08:16]

Report message to a moderator

Re: Indexes [message #239465 is a reply to message #239463] Tue, 22 May 2007 08:19 Go to previous messageGo to next message
Bill B
Messages: 1482
Registered: December 2004
Senior Member
null values are not indexed, to find a row with null values in the indexed column, the database has to perform a full table scan.
Re: Indexes [message #239466 is a reply to message #239463] Tue, 22 May 2007 08:19 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Null values will not be included in the index. As to whether it makes a good index, that would depend on the distribution of data in the index rather than whether there are lots of null values. Does it have a high number of distinct values ?

An IOT would only be useful if you perform a query such as nvl(column, 'X') = 'X' on a frequent basis.
Re: Indexes [message #239469 is a reply to message #239463] Tue, 22 May 2007 08:21 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
akkumar81 wrote on Tue, 22 May 2007 15:15
In a table 1000 of records.first 100 records ...
There is no first/last in an Oracle table.

MHE
Re: Indexes [message #239479 is a reply to message #239469] Tue, 22 May 2007 08:40 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
There is no first/last in an Oracle table.

There is no first/last in a heap organized table, there is definite first/last in index organized table.
Re: Indexes [message #239489 is a reply to message #239479] Tue, 22 May 2007 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For IOT this the same thing, no first, no last.

Regards
Michel
Re: Indexes [message #239499 is a reply to message #239489] Tue, 22 May 2007 09:10 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
do you not agree, that IOTs have data which is physically colocated? Are you implying that the organization of heap and IOT are same? It can be showed that in IOTs data are colocated physically
Re: Indexes [message #239504 is a reply to message #239499] Tue, 22 May 2007 09:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I imply nothing like.
I just said there is no first or last unless you give an order.
YOU imply the order is the one of primary key, I don't.

In my building, people are physically colocated by family, which person is first, which person is last?

Regards
Michel
Re: Indexes [message #239510 is a reply to message #239504] Tue, 22 May 2007 09:38 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

colocated ? Isnt it collocated
Had to look it up Very Happy
Re: Indexes [message #239511 is a reply to message #239504] Tue, 22 May 2007 09:41 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
My way of looking at it is that an index is always sorted(i.e ordered) unless you use NOSORT. And in case of an IOT, I would put it as the index is the table, and the table is the index
Re: Indexes [message #239512 is a reply to message #239510] Tue, 22 May 2007 09:46 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
no,it is COLOCATED i.e. located together
Re: Indexes [message #239517 is a reply to message #239512] Tue, 22 May 2007 10:09 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

saibal wrote on Tue, 22 May 2007 16:46
no,it is COLOCATED i.e. located together


colocated
Word not found in the Dictionary and Encyclopedia. Did you mean:
collocated

Useless Dictionary you have another ? Razz

[Updated on: Tue, 22 May 2007 10:10]

Report message to a moderator

Re: Indexes [message #239519 is a reply to message #239511] Tue, 22 May 2007 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ordered?
SQL> create table t (
  2    owner varchar2(30),
  3    object_name varchar2(128),
  4    subobject_name varchar2(30),
  5    object_id integer primary key,
  6    fill char(2000)
  7  )
  8  organization index
  9  /

Table created.

SQL> insert into t  
  2  select owner,object_name,subobject_name,-1*object_id,cast('a' as char(2000)) 
  3  from dba_objects 
  4  where object_id is not null and rownum<100 order by dbms_random.value;

99 rows created.

SQL> exec dbms_stats.set_table_stats (user, 'T', numrows => 1000000);

PL/SQL procedure successfully completed.

SQL> select object_id from t;
 OBJECT_ID
----------
       -38
       -37
       -36
        -8
        -7
       -27
       -26
      -100
       -30
       -29
       -28
       -71
       -70
       -69
       -83
       -82
       -81
       -45
       -44
       -48
       -47
       -46
        -2
       -59
       -58
       -57
       -90
       -89
...

Regards
Michel
Re: Indexes [message #239528 is a reply to message #239519] Tue, 22 May 2007 10:40 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Frankly speaking Michel, I just couldn't see the point of your example. What did you actually show?
Let me again reiterate what I am trying to say:
DATA IN AN IOT IS ACTUALLY STORED SORTED ON PRIMARY KEY.
With all due respect, and if you don't take it otherwise, can you prove the above statement wrong
Re: Indexes [message #239530 is a reply to message #239517] Tue, 22 May 2007 10:44 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
tahpush,
You are right and I am wrong. Indeed it is collocated in the dictionary, but the Oracle books that I study all have 'colocated'
in it. Thanks for pointing it out.
Re: Indexes [message #239536 is a reply to message #239528] Tue, 22 May 2007 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What did my exemple show?
Data is not sorted on primary key, doesn't it?
SQL> set autotrace traceonly explain
SQL> select object_id from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 4135001881

------------------------------------------------------------------------------------------
| Id  | Operation            | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                   |  1000K|    12M|    19  (64)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| SYS_IOT_TOP_50919 |  1000K|    12M|    19  (64)| 00:00:01 |
------------------------------------------------------------------------------------------

My select just read the index and the output/data are not sorted on primary key.
That is my point.

Now prove it if I'm wrong.

Regards
Michel
Re: Indexes [message #239551 is a reply to message #239536] Tue, 22 May 2007 11:17 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Thanks Michel for your 'proof' by showing the explain plan and not the TKPROF. That apart, before I construct any examples to show what i am trying to say, let me tell you the quote 'data is actually stored sorted by primary key' is a verbatim quote from somebody who knows a thing or two about Oracle and has written a couple of books on Oracle. I could have copied his example and given it to you, but that would have been stupid. let me also check it out--and not from explain plan, but from tkprof. If you are correct-and you could be, I am not going to blow that away--Then I will need to change my concept of IOT, but for the moment, I will stick with what Howard J.Rogers, Cary Millsap and Thomas Kyte are saying--you could do well to check Thomas Kyte's Effective Oracle by Design page 404 in my edition.
Regards
Re: Indexes [message #239556 is a reply to message #239551] Tue, 22 May 2007 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
TKPROF will give you the same thing. Just try it, you have all the code to do it. Nothing hidden, all is there.

Naming people is not proving.
T. Kyte always said that data are not stored in any order whatever is the table organization.
I didn't remember that H.J. Rogers says what you said but I didn't read all what he wrote.
It does not seem to me Cary Millsap will ever write something about the subject.
But post clear reference instead of "someone somewhere says...".

Once again, a counter-exemple like the one I posted PROVE the concept is false. Nothing more to say. You can build all the exemples you want, mine will prove forever that this is false.

Now, if you follow the index tree then you get the index value in the order, of course. But is this not "I follow a route in order to get it in the order and say what, I get them in the order"?

Regards
Michel
Re: Indexes [message #239563 is a reply to message #239556] Tue, 22 May 2007 12:09 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Quote:
TKPROF will give you the same thing. Just try it, you have all the code to do it. Nothing hidden, all is there.

Naming people is not proving.
T. Kyte always said that data are not stored in any order whatever is the table organization.
I didn't remember that H.J. Rogers says what you said but I didn't read all what he wrote.
It does not seem to me Cary Millsap will ever write something about the subject.
But post clear reference instead of "someone somewhere says...".

Michel,
I told in my earlier post, that I will definitely check out your example, and yes, I will use TKPROF to do it-no questions about it. That is why I didn't want to 'manufacture' a counter-proof gleaned from my books and materials. I want to actually 'test' it.
As far as naming people are is concerned, the idea was to tell you that much of what I have learned about IOT (and other topics) are from these people. And I learn from you too-all of the time! So, if what you say is demonstratively true, then these guys must be wrong, especially the first name that you mention-Tom Kyte--it is his book that I am quoting from page 404 under the heading 'IOT Wrap Up'. Believe me Michel, I am not making this up.
And indeed, if i further post on this topic, I will post with proofs and clear references--as you say
Re: Indexes [message #239576 is a reply to message #239563] Tue, 22 May 2007 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, I just read again "IOT wrap-up" section in both "Expert One on One" and "Expert Oracle Database Architecture", there is nothing about the order of data and primary key (actually the section is the same in both books). He only talks about OVERFLOW, PCTFREE and PCTUSED.

Regards
Michel
Re: Indexes [message #239582 is a reply to message #239576] Tue, 22 May 2007 12:56 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Please read 'Effective Oracle by Design'-that is the name I have been mentioning all along, there is no ambiguity about that, and then get back to me.You have really piqued my interest--so I am going to spend the night reading up everything about IOT, that I can lay my hands upon. And tomorrow will be a new
day......

[Updated on: Tue, 22 May 2007 12:58]

Report message to a moderator

Re: Indexes [message #239635 is a reply to message #239576] Tue, 22 May 2007 22:02 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Ok Michel,
I read and re-read the documentation,the books and materials that I have, searched on google, asked a couple of guys who know a thing or two about IOTs, but nowhere, repeat nowhere, did I get a shred of evidence that IOTs are stored not sorted on primary key.
The 'proof' that you gave in your post is dubious. It shows the order in which rows are being returned, not the order in which rows are being stored, and in that respect you are very much in the correct, rows are returned in no particular order, unless you are using a order by clause. But, did you ever try to dump IOT data blocks? if you dumped a block of data from an IOT, it would contain entries like AAAA, BBBB, CCCC, DDDD, EEEE in that order. An index structure can do nothing else. Besides I will point you to Oracle's own white paper, which you can read here
That should settle the issue.
Regards

[Updated on: Tue, 22 May 2007 22:04]

Report message to a moderator

Re: Indexes [message #239637 is a reply to message #239463] Tue, 22 May 2007 22:14 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
>if you dumped a block of data from an IOT, it would contain entries like AAAA, BBBB,
Please post a reproducible example that substantiates the statement above.
Re: Indexes [message #239639 is a reply to message #239637] Tue, 22 May 2007 22:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
IOTs are stored ORGANISED by the PK. But that is not to say that they are SORTED by the PK.

Say you have an IOT that is actually stored in sorted order (just pretend for now - dont say its impossible) and ALL of the leaf blocks are full, but the branch blocks are not.

The next insert will cause a leaf block to split. The data in the split block will be split across that block and a new one allocated at the end of the IOT. Oracle does not shuffle the entire right-hand-side of the data up one block just to make room for the new block. It just splits the data, and adds the address of the new leaf-block to the branch-block parent of the original leaf-block.

So, even though we started with a sorted IOT, we now have data belonging to the 'middle' sitting at the end. It is NOT sorted.

The evidence of this is in Michel's example. A FAST FULL SCAN reads the blocks in physical order. This is not the same as a FULL SCAN, which reads the rows in indexed order, and would return the results in a sorted sequence.

The FULL SCAN starts with the head of the b-tree and performs an "Inorder" scan of the tree, reading a single block at a time, returning rows attached to leaf blocks as it goes in binary sorted order of the primary key.

A FAST FULL SCAN reads many blocks at a time (DB_MULTIBLOCK_READ_COUNT), discards branch blocks, and returns rows attached to leaf blocks in the order in which it finds them on disk. Within each leaf block, the rows will be sorted by the primary key, but the order of blocks will be mixed.

Ross Leishman
Re: Indexes [message #239641 is a reply to message #239639] Tue, 22 May 2007 22:59 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
See, my contention has always been that logically data is always stored sorted on primary key. That is beyond any shadow of doubt. If you are arguing against that, then you are arguing against the official Oracle documentation, may be you should consider filing a TAR. Physically due to RAID, ASSM, multiple disks, etc. I admit, there may be a possibility that data may end up being not stored sorted on primary key at a physical level. and, by the way did you read this?
Re: Indexes [message #239644 is a reply to message #239463] Tue, 22 May 2007 23:16 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
I'll stipulate that within a single block, the PKs are an ordered list.
But this agreement does NOT extend to what happens going from block N to block N+1.
In what order must the blocks/extents/segments need to be read to return all PKs in an IOT in 100% sorted order?
Unsubstantiated claims do NOT constitute reproducible proof.
Re: Indexes [message #239696 is a reply to message #239644] Wed, 23 May 2007 02:14 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Quote:
Unsubstantiated claims do NOT constitute reproducible proof

Absolutely. I couldn't agree with you more. Now, you will have to tell me what unsubstantiated claims I have been making? On the point of whether or not IOTs store data sorted by primary key or not, I have pointed people to the relevant documentation, white papers, quotes from various authorities in the field--I don't think there is anything that I said in that context that I have not backed by available published material. If the material that I have pointed you to, is factually incorrect, then the onus is on you to show where and how it is incorrect.
Let me quote Tom Kyte from his book Expert Oracle Database Architecture... under the head Index Organized Tables he writes
"Index organized tables(IOTs) are, quite simply, tables stored in an index structure. Whereas a table stored in a heap is unorganized(i.e., data goes wherever there is available space), data in an IOT is stored and sorted by primary key. IOTs behave just like "regular" tables do as far as your application is concerned; you use SQL to access them as normal. They are especially useful for information retrieval, spatial and OLAP applications."
So, should I disbelieve what he writes? I will expect an answer on this:
additionally, have a look at the following:

system@DBASE-SQL>>create table s(
  2  x int primary key,
  3  y varchar2(30)
  4  )
  5  organization index;

Table created.

system@DBASE-SQL>>insert into s
 select object_id, object_name  from dba_objects;

48374 rows created.

system@DBASE-SQL>>analyze table s
  2  compute statistics
  3  for table
  4  for all indexed columns;

Table analyzed.
system@DBASE-SQL>>set pause on
system@DBASE-SQL>>set pause 'more to follow...'
system@DBASE-SQL>>select x, y, dump(x, 16) from s where rownum<=50;
more to follow...

         X Y                              DUMP(X,16)
---------- ------------------------------ ---------------------------
         2 C_OBJ#                         Typ=2 Len=2: c1,3
         3 I_OBJ#                         Typ=2 Len=2: c1,4
         4 TAB$                           Typ=2 Len=2: c1,5
         5 CLU$                           Typ=2 Len=2: c1,6
         6 C_TS#                          Typ=2 Len=2: c1,7
         7 I_TS#                          Typ=2 Len=2: c1,8
         8 C_FILE#_BLOCK#                 Typ=2 Len=2: c1,9
         9 I_FILE#_BLOCK#                 Typ=2 Len=2: c1,a
        10 C_USER#                        Typ=2 Len=2: c1,b
        11 I_USER#                        Typ=2 Len=2: c1,c
        12 FET$                           Typ=2 Len=2: c1,d
        13 UET$                           Typ=2 Len=2: c1,e
        14 SEG$                           Typ=2 Len=2: c1,f
        15 UNDO$                          Typ=2 Len=2: c1,10
        16 TS$                            Typ=2 Len=2: c1,11
        17 FILE$                          Typ=2 Len=2: c1,12
        18 OBJ$                           Typ=2 Len=2: c1,13
more to follow...

         X Y                              DUMP(X,16)
---------- ------------------------------ ---------------------------
        19 IND$                           Typ=2 Len=2: c1,14
        20 ICOL$                          Typ=2 Len=2: c1,15
        21 COL$                           Typ=2 Len=2: c1,16
        22 USER$                          Typ=2 Len=2: c1,17
        23 PROXY_DATA$                    Typ=2 Len=2: c1,18
        24 I_PROXY_DATA$                  Typ=2 Len=2: c1,19
        25 PROXY_ROLE_DATA$               Typ=2 Len=2: c1,1a
        26 I_PROXY_ROLE_DATA$_1           Typ=2 Len=2: c1,1b
        27 I_PROXY_ROLE_DATA$_2           Typ=2 Len=2: c1,1c
        28 CON$                           Typ=2 Len=2: c1,1d
        29 C_COBJ#                        Typ=2 Len=2: c1,1e
        30 I_COBJ#                        Typ=2 Len=2: c1,1f
        31 CDEF$                          Typ=2 Len=2: c1,20
        32 CCOL$                          Typ=2 Len=2: c1,21
        33 I_TAB1                         Typ=2 Len=2: c1,22
        34 I_UNDO1                        Typ=2 Len=2: c1,23
        35 I_UNDO2                        Typ=2 Len=2: c1,24
more to follow...

         X Y                              DUMP(X,16)
---------- ------------------------------ ---------------------------
        36 I_OBJ1                         Typ=2 Len=2: c1,25
        37 I_OBJ2                         Typ=2 Len=2: c1,26
        38 I_OBJ3                         Typ=2 Len=2: c1,27
        39 I_IND1                         Typ=2 Len=2: c1,28
        40 I_ICOL1                        Typ=2 Len=2: c1,29
        41 I_FILE1                        Typ=2 Len=2: c1,2a
        42 I_FILE2                        Typ=2 Len=2: c1,2b
        43 I_TS1                          Typ=2 Len=2: c1,2c
        44 I_USER1                        Typ=2 Len=2: c1,2d
        45 I_COL1                         Typ=2 Len=2: c1,2e
        46 I_COL2                         Typ=2 Len=2: c1,2f
        47 I_COL3                         Typ=2 Len=2: c1,30
        48 I_CON1                         Typ=2 Len=2: c1,31
        49 I_CON2                         Typ=2 Len=2: c1,32
        50 I_CDEF1                        Typ=2 Len=2: c1,33
        51 I_CDEF2                        Typ=2 Len=2: c1,34

50 rows selected.

system@DBASE-SQL>>

You can dump the entire table, and then see for yourself
whether, in the internal representation of the index structure, the data would end up next to each other or not?
Regards
Re: Indexes [message #239729 is a reply to message #239696] Wed, 23 May 2007 03:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
@saibal, one of two things (or both) is happening:
  • You are wrong
  • You do not understand what we are trying to explain


Please, read this and think before you respond again. Consider an IOT containing 1,000,000,000 rows with a PK using the numbers from 1 to 1 billion. What would happen if we inserted - say - 9999 rows (ie. more than can fit in a single database block) with decimal PK values 500,000,000.0001 to 500,000,000.9999

These rows will be stored smack in the middle of the IOT. What would Oracle need to do to maintain sorted order? It would need to migrate 500 Million rows to make space in the middle for the new 10,000 rows. Clearly this does not happen - IOTs would come to a screaming halt if they did.

In fact, IOTs function exactly like b-tree indexes whose funtionality on data update is relatively well documented. I have summarised it in my earlier post. You can also read about it in the Oracle Concepts manual or this article (granted: I wrote it so it's not an independent source).

I am not saying Tom Kyte is wrong - God forbid. Tom just dumbed it down for the mass audience and inadvertently made a potentially misleading statement. I know for a fact that Tom understands exactly how indexes (and IOTs work).

The reason your example does what it does is because the 50 rows you displayed all reside in the same block. As stipulated earlier, rows stored in the same block will be in PK order. Expand your SQL to select 10,000 rows and you will see what we mean.

In summary:
  • IOTs are stored in an ORGANISED order, not a sorted order. Hence the name: Index ORGANIZED Table.
  • The nature of the organisation is that rows within a single block will be sorted on the primary key, but the order of blocks in the extent are not ordered based on the PK.
  • When you SELECT from an IOT using a RANGE or FULL scan on the PK, rows will be returned in a binary sorted order on the primary key.
  • When you SELECT from an IOT using a FAST FULL SCAN on the PK, rows will most probably be returned in physical order on disk - each block sorted on the PK, but blocks not necessarily in PK order. Parallel query could cause rows to be returned in an alternate order though.


Now by all means reply to this post telling me what a patronising twerp I am. Also feel free to explain to me that I (and all of the other respondents) have misunderstood your assertion that "data is always stored sorted on primary key" and confirm that you did not mean the entire table is physically organised in sorted order. However, if you are going to say that I'm wrong (along with the other respondents), then take a moment to think about it:
  • Have you read the concepts manual on b-tree indexes? If not, you're just guessing.
  • Are you going on the advice of others rather than first-hand experience? If not, you're just guessing.
  • Have you read my counter-example above (where millions or rows would need to be migrated) and can explain how Oracle maintains this sorted order so efficiently? If not, you're just guessing.

Just take the time to retest your logic. Alter your test case to make sure you are inserting the rows in random order, and that you select more than a block's worth in the output. Think how satisfying it wil be to demonstrate what a twit I am when you're right.

But also think about how you will look if you don't make the effort and you're proven wrong.

Ross Leishman
Re: Indexes [message #239731 is a reply to message #239729] Wed, 23 May 2007 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ross,

This and your previous post were great ones to explain the case.
Thanks for them.

Regards
Michel
Re: Indexes [message #239774 is a reply to message #239729] Wed, 23 May 2007 04:56 Go to previous messageGo to next message
dizwell
Messages: 3
Registered: May 2007
Junior Member
I don't know about patronizing or twerp, but I'll say this is one of the sillier arguments I've seen!

Physically, god knows where anything ends up in Oracle. The blocks of an extent aren't even necessarily contiguous on disk. So much was known years ago, and I would have thought it was end of discussion on that point.

Point is, if someone says 'rows in an IOT are stored in primary key order', I think most people know what that means: rows within a block will be ordered, and different blocks aren't linked to randomly. There is order, organisation, structure -whereas in a heap table there is none.

Personally, I have no trouble saying 'rows in an IOT are stored in PK order', and neither does Tom Kyte, nor Oracle Corporation generally. It's just common sense, and it happens to encapsulate the truth of the matter without resort to nitpicking and pedantry.

I'm not going to preface everything I say about the way Oracle stores data with the words "logically...", either: because when you look at RAID, SAN, LVM and everything else that happens in the storage world these days, it's just obvious that one's comments about the order of events in a segment cannot extend to the physical storage layer.

Meanwhile, one of the important things for a newbie to know about an IOT is that its contents are indeed in primary key order. Why? Because that's why you can't build an IOT without a primary key constraint being declared; it's why searching an IOT can be quick (once you've found one key value, you know there can't be any more so you can stop looking); and it's therefore one of the reasons why you might want to deploy them in the first place.

Saying an IOT's rows are stored in PK order, in short, has validity and (above all) utility. Saying that physically, the blocks are all over the place has no utility that I can see and only the validity of the bleedin' obvious.

I will add that if the point of the original statement is to reinforce the message: rows don't get returned in a known order unless you say ORDER BY, fair enough. That's indeed a message that has utility.

[Updated on: Wed, 23 May 2007 05:05]

Report message to a moderator

Re: Indexes [message #239778 is a reply to message #239774] Wed, 23 May 2007 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks Howard for this clear summary, I think this will end up the discussion.

In this case, what did you answer to the OP?

Regards
Michel
Re: Indexes [message #239781 is a reply to message #239778] Wed, 23 May 2007 05:09 Go to previous messageGo to next message
dizwell
Messages: 3
Registered: May 2007
Junior Member
That rows in an index are certainly stored in key column order; that all index structures rely on their contents being stored in key column order; and that an IOT is an index structure... so its contents are of course also stored in (primary) key order.

But that none of that guarantees the order in which rows are returned when you query them, and that ORDER BY is still important (as it is even when you query a heap table via an index, of course).

http://www.dizwell.com/prod/node/767 is the entire exchange.
Re: Indexes [message #239819 is a reply to message #239729] Wed, 23 May 2007 06:35 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Ross,
In your post you were getting personal. As a senior member of the forum you better watch it. I read you article in detail,and I think
I get what you are trying to say. I also inserted 10000 rows, as you instructed me to do--the results weren't very different from when I inserted 50 rows, you can try it for yourself.
Hopefully, you read what Howard just said here. I hate to say it but his first line kind of bust your chops. Perhaps, you yourself need to rethink how you say what you say.
Regards
Re: Indexes [message #239856 is a reply to message #239729] Wed, 23 May 2007 08:20 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Wed, 23 May 2007 18:39
Alter your test case to make sure you are inserting the rows in random order, and that you select more than a block's worth in the output.


@saibal, your own example with alterations to mess up the rows before they are inserted:

create table s(
x int primary key,
y varchar2(30)
)
organization index;


insert into s
select *
from (
    select object_id, object_name  from all_objects
    where rownum < 10000
    order by dbms_random.value()
);

analyze table s
compute statistics
for table
for all indexed columns;

select x, y, dump(x, 16) from s;

./fa/2511/0/

dizwell wrote on Wed, 23 May 2007 19:56
Point is, if someone says 'rows in an IOT are stored in primary key order', I think most people know what that means: rows within a block will be ordered, and different blocks aren't linked to randomly. There is order, organisation, structure -whereas in a heap table there is none.

I disagree. I don't think most people know what that means. In fact, I doubt 10% of the members of this forum know what it means. The newbies forum is littered with posts from people who do not understand the meaninglessness of 'order' in a relational table.

This post is a perfect example, not only did the OP demonstrate no appreciation of the concept, a clearly experienced expert was even fooled by a combination of imprecise terminology and an inadequate test case.

saibal wrote on Wed, 23 May 2007 21:35
Ross,
In your post you were getting personal. As a senior member of the forum you better watch it.

@saibal, I certainly meant no offence. There was a conversation in the Community Hangout forum a while ago where some experienced posters were cringing at stupid things they did or said online years ago that could still be visible today. I was trying to save you that discomfort; in vain it seems. Read it again, I wasn't rude, I wasn't jeering. I clearly expended a lot of personal effort to document what you yourself said was poorly documented on the web.
saibal wrote on Wed, 23 May 2007 21:35
I hate to say it but his first line kind of bust your chops.

Cut and paste the test case above. Also run the following query afterwards:
select *
from (
    select x, rn, row_number() over (order by x) as seq
    from (
      select x, rownum as rn
      from s
    )
)
where rn <> seq

My chops are still feeling fine, thanks.

Ross Leishman
  • Attachment: proof.JPG
    (Size: 79.77KB, Downloaded 641 times)
Re: Indexes [message #240896 is a reply to message #239856] Sun, 27 May 2007 07:16 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
@Ross,
Thanks for your test case. Indeed you have shown that data may end up not being next to each other. But does your test case prove the exception or the rule of co-location in an IOT? If what you are showing me is an exception to the general rule of IOT enforcing co-location, I must admit that you are very much in the correct and I am not. I learned something new. However, if you are saying that what you demonstrated in your case study is the general rule of IOT, then I am afraid I will have to disagree there, because IMHO co-location of data is something IOTs are all about. But please feel free to correct me on that point in your own inimitable style.
And please have a look at what an acknowledged oracle expert(not Tom Kyte), who shall remain nameless here has to say on this:

Quote:
"The description is correct - the rows in an IOT are not absolutely
ordered according to block address within file. But making too
much of a fuss about whether you should say they are 'sorted by'
or 'organised by' primary key is not really going to make much
difference. Either way the expression has to be qualified if you
want to give a proper impression of what happens, and different
people will happily (and pointlessly) argue that one term is more
appropriate than the other.

Ultimately, the critical condition is that an IOT is a unique index
where every entry carries (some of) the non-index values. If
people understand how B-tree indexes are built in Oracle, they
understand how IOTs are built, and will be aware of the fact
that they get a significant improvement in colocation, but not
perfect colocation. Changing from a heap to an IOT is something
you do to get an order of magnitude improvement in certain parts
of your system - the fact that the performance is 9.9 times better,
rather than 10 times better is then of less concern.

Bear in mind that even if two leaf blocks appear to be adjacent
in the data file, they might be on different discs."


Quote:
@saibal, I certainly meant no offence. There was a conversation in the Community Hangout forum a while ago where some experienced posters were cringing at stupid things they did or said online years ago that could still be visible today. I was trying to save you that discomfort; in vain it seems. Read it again, I wasn't rude, I wasn't jeering. I clearly expended a lot of personal effort to document what you yourself said was poorly documented on the web.

Thanks Ross for your magnanimous gesture. I really appreciate it.
However, if what you were saying was so unfailingly true, so blatantly obvious, and I was so horribly and utterly wrong, then there wouldn't have been any controversy at all, nor would have HJR written what he did write here. Neither would the acknowledged expert, I refer to above, say what he does. I will remain ever grateful to you that by impugning my knowledge of b-tree indexes and the manuals, you made look into indexes, it's internals and IOTs in a way I would not have done otherwise. Maybe I can now consider writing an article on IOT and get it published here!
Regards











Re: Indexes [message #241001 is a reply to message #240896] Mon, 28 May 2007 03:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Ouch. I've been told. Sarcasm meter off the scale here.

For what its worth, I'm glad the OP finally has an answer we can all in agree on: Is there a first and last row in any table - even an IOT? No there is not.

My final word on this thread (sure, there's been too many already, but really, what damage can it possibly do now)

A. Bouché-Leclerq (1879)
...it is almost tempting to admire the cunning way in which an unshakeable belief can transform into proof the very objections which threaten to destroy it, and nothing better demonstrates the psychological history of humanity than the irresistable prestige of the preconceived idea.


Ross Leishman
Re: Indexes [message #241721 is a reply to message #241001] Wed, 30 May 2007 08:40 Go to previous messageGo to next message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
My last word on this thread:
There is a first/last row in all tables Smile we just don't really have any control over it.
Re: Indexes [message #241728 is a reply to message #241721] Wed, 30 May 2007 08:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Mine is: There is a first/last row in all tables and they can be any ones of their rows. Cool

Regards
Michel

[Updated on: Thu, 31 May 2007 02:55]

Report message to a moderator

Re: Indexes [message #241946 is a reply to message #241721] Thu, 31 May 2007 02:53 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
saibal wrote on Wed, 30 May 2007 23:40
There is a first/last row in all tables Smile we just don't really have any control over it.


Did I last word? I must have meant second-last....

Damnation! Caught in my own web of pedantry! To say there is no first or last is wrong for exactly the same reasons it is wrong to say IOTs are sorted. It conveys only a dumbed-down version of the truth.

Yes there are physically first and last rows in all tables over which we have no control - they are even deterministic in a static-state. But when these physical tables are overlayed with the axioms of a relational database, they become non-deterministic.

A mathematical metaphor could be: if we start with a number (say 5) and apply a deterministic function (say, add 2), then we MUST get a deterministic result (in this case, 7).

In a relational database, if we have a known begin-state (known rows in a table) and apply a deterministic transaction (say, insert a row), then we should have a deterministic end-state (all the same data I had before plus another row of known content).

This is true when we talk about rows and thier column values, but NOT true when we talk about physical locations in the table. In order to fully understand the end-state in this way, we need to re-query the data. This is a violation of Codd's 3rd axiom of relational databases, which in turn violates his 8th rule: "Physical data independence".

So - in furious agreement with the two previous posts - I'm forced now to qualify my rash statement that there is no first and last row in a relational table:

The first and last rows of a table may be known, but only through non-relational capabilities (eg. rowids) of the database - that knowlege therefore lacks meaning in the context of the relational database.

Ross Leishman

Did we go off-topic at all there?
Re: Indexes [message #241955 is a reply to message #241946] Thu, 31 May 2007 03:21 Go to previous messageGo to previous message
saibal
Messages: 111
Registered: March 2007
Location: India
Senior Member
Summarizing, as I see it:
In principle
select * from IOT where rownum = 1
could return the first row in the index (index full scan),which it __probably__ always would do, but it might be possible to engineer a case where it doesn't
OR
the first row physically in the data segment (index fast full scan)
which might be 'out of order' because of a block split that had
moved the first leaf block of the index away from the "first" block
in the data segment.
But, of course,
select where non-pk column = constant and rownum = 1
could EASILY decide to follow the index order, or the segment
order depending on the size of the data set, patterns of data etc.
Previous Topic: multipole row return function - this is definitely NOT urgent, so answer me in the next few weeks
Next Topic: Make SQl purposefully RUN SLOW
Goto Forum:
  


Current Time: Sat Dec 03 03:58:56 CST 2016

Total time taken to generate the page: 0.38369 seconds