RE: Index on status field?

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Fri, 08 Apr 2005 16:07:28 -0500
Message-id: <0IEN00K9RC0G46@i006.jcpenney.com>

Zoran

	AFAIK, even non-unique btree indices are unique ;-)
	By combining non-unique columns and rowid, Oracle software makes the

entries, unique in the index leaf block. rowid is simply a trailing column in the index leaf block ( of non-unique indices). As you well know, index is an ordered tree sorted by key column values. So, if the key columns have same value, then the index entries will be ordered by rowid.

Hope that makes sense.

In this example, c10b is 10 and you can see that they are ordered by rowid, for the same value of a : 10.

Above applies only to btree non-unique index structures. Now, Separator in the branch block determines which leaf block to use, as explained by Lex. But still orderliness is maintained.

Here is an example of that:
create table rowid_test (a number, b varchar2(100)) ;
declare
  i number;
begin
  for i in 1 .. 100
   loop

        insert into rowid_test values ( i, lpad(i,50, 'X'));
        insert into rowid_test values ( i, lpad(i,50, 'X'));
        insert into rowid_test values ( i, lpad(i,50, 'X'));
        insert into rowid_test values ( i, lpad(i,50, 'X'));

end loop;
end;
/

create index rowid_i1 on rowid_test (a); Index created.

delete from rowid_test where a=10 and rownum <3; commit;
declare
  i number;
begin
  for i in 1 .. 100
   loop

        insert into rowid_test values ( i, lpad(i,50, 'X'));
        insert into rowid_test values ( i, lpad(i,50, 'X'));
        insert into rowid_test values ( i, lpad(i,50, 'X'));
        insert into rowid_test values ( i, lpad(i,50, 'X'));

end loop;
end;
/
Commit;
alter system dump datafile 43 block min 1085827 block max 1085837;

c10b is 10.
row#108[5464] flag: -----, lock: 0
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 0a d0 8d ce 00 26
row#109[5476] flag: -----, lock: 0
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 0a d0 8d ce 00 27
row#110[5488] flag: -----, lock: 0
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 0a d0 8d d1 00 43 <-- In a nonunique btree index, rowid is a column and so ORDERED.
row#111[5500] flag: -----, lock: 0
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 0a d0 8d d1 00 44
row#112[5512] flag: -----, lock: 0
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 0a d0 8d d1 00 45
row#113[5524] flag: -----, lock: 0
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 0a d0 8d d1 00 46
row#114[4156] flag: -----, lock: 2
col 0; len 2; (2): c1 0b

Thanks

Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA (ver 7.0 - 9i)
Allocation & Assortment planning systems JCPenney

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lex de Haan
Sent: Friday, April 08, 2005 3:23 PM
To: zoran_martic_at_yahoo.com; Oracle-L_at_freelists.org Subject: RE: Index on status field?

Hi Zoran,
this depends on the separator values in the branch blocks just above the leaf blocks -- these separator values decide in which leaf block an entry goes.

kind regards,

Lex.

Visit my website at http://www.naturaljoin.nl

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Martic Zoran
Sent: Friday, April 08, 2005 22:05
To: Oracle-L_at_freelists.org
Subject: Re: Index on status field?

Christo,

I understand the general mechanism about indexes and tables. What is left is:
> Remember i said the ordering was index key, rowid.
How is that working? To say that you have 10 blocks where there are 2 slots left in each.
When and how is that going to be reused exactly? Which algorithm is that? Some ORacle specific?

That is what I am searching if anybody knows.

I did not get that from your answer, but it is good point. Where did you find out it depends on speific rowid when the value is the same? Probably some doc/note?

Regards,
Zoran

--
http://www.freelists.org/webpage/oracle-l



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


--
http://www.freelists.org/webpage/oracle-l

Received on Fri Apr 08 2005 - 17:11:46 CDT