Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Index clarification

kparthiApr 27 2016 — edited Apr 27 2016

Hi All ,

I am having clarification on how index are stored .

I keep on reading that B-tree unique index are stored in sorted order( default ascending )

Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column or columns. For example, no two employees can have the same employee ID. Thus, in a unique index, one rowid exists for each data value. The data in the leaf blocks is sorted only by key.


Question:

User "A" insert into a table with 1,3,5( My understanding is B-tree index is created with Root Block--->Branch Block-->Leaf Block for storing 1,3,5 in aceding order )

User "B" insert into a table with 2,3,6

(

-- Now the questin is how the index are stored in asceding order

  --is there any operation happening inisde the b-tree index so that the Leaf block is rebuilded

  -- or is there any different way the leaf block is created

)



create table test_index (a number primary key );

desc desc test_index;

desc test_index

Name Null     Type 

---- -------- ------

A    NOT NULL NUMBER

INDEX_NAME                     UNIQUENESS

------------------------------ ----------

SYS_C00413788                  UNIQUE

-- User A tries to insert 1 ,3 , 5

insert into test_index values (1);

insert into test_index values (3);

insert into test_index values (5);

commit;

-- My understanding is B-tree index is created with Root Block--->Branch Block-->Leaf Block for storing 1,3,5 in aceding order

-- User B tries to insert 2 ,4 , 6

insert into test_index values (2);

insert into test_index values (4);

insert into test_index values (6);

commit;

-- Now the questin is how the index are stored in asceding order

  --is there any operation happening inisde the b-tree index so that the Leaf block is rebuilded

  -- or is there any different way the leaf block is created

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on May 25 2016
Added on Apr 27 2016
16 comments
668 views