RE: Index on status field?
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: lex.de.haan@xxxxxxxxxxxxxx, zoran_martic@xxxxxxxxx, Oracle-L@xxxxxxxxxxxxx
- Date: Fri, 08 Apr 2005 16:07:28 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Lex de Haan
Sent: Friday, April 08, 2005 3:23 PM
To: zoran_martic@xxxxxxxxx; Oracle-L@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Martic Zoran
Sent: Friday, April 08, 2005 22:05
To: Oracle-L@xxxxxxxxxxxxx
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
--
//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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
