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!

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

Srini Chavali-Oracle
It depends - most, if not all, database patches require that the database be shut down. Check the README accompanying each patch.

HTH
Srini
user62721
Hi,

from 11g, some patches are online
$ opatch query -is_online_patch
Regards
Srini Chavali-Oracle
Good stuff ! More details in

Gathering Patch details before applying using Opatch Query option [ID 1510747.1]

HTH
Srini
Osama_Mustafa
including to information posed by other check
RDBMS Online Patching Aka Hot Patching [ID 761111.1
1005886
Thank you guys for helping me in patching
1 - 5
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
675 views