Home » RDBMS Server » Server Administration » INDEX
INDEX [message #326206] Tue, 10 June 2008 12:11 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
can anybody give the reference url for index from basis
Re: INDEX [message #326208 is a reply to message #326206] Tue, 10 June 2008 12:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts

Regards
Michel
Re: INDEX [message #326610 is a reply to message #326208] Thu, 12 June 2008 02:06 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have created one index with
user_indexes
how can i know the type of the index ( bitmap or btree...) .

or if i am wrong anything on above can anyone tell me how i known one particular index type (bitmap or btree ... )
Re: INDEX [message #326611 is a reply to message #326610] Thu, 12 June 2008 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What about the second column of user_indexes?

Regards
Michel
Re: INDEX [message #335904 is a reply to message #326208] Thu, 24 July 2008 02:36 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
Michel,

According to the database concept references that the oracle uses b-tree to store the indexes

In that references there are two blocks

1. Branch Block
2. Leaf Block

The oracle process will be parallel for table and index at the time of inserting /retriving the data from/to table which is indexed.

My doubt is how internally oracle assign value for the branch block.

for example : i have created index for column staff_name

staff_name values are :

Ashok
krish
michel
rioot
suresh
kannan
sundar


can you please explain in detail how and what data will be stored into branch block and leaf block

Re: INDEX [message #335920 is a reply to message #335904] Thu, 24 July 2008 02:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"Index columns,rowid" for each indexed row (if index is B-tree and not compressed).

Regards
Michel
Re: INDEX [message #335987 is a reply to message #335920] Thu, 24 July 2008 05:43 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have given below the words which is given for explain about the branch block.

Branch blocks store the following:

The minimum key prefix needed to make a branching decision between two keys

The pointer to the child block containing the key



My question is whether all the column value which is indexed in the table will be inserted into the branch block for search.
Re: INDEX [message #335991 is a reply to message #335987] Thu, 24 July 2008 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, all indexed columns are concatenated in order to built the index key.

Regards
Michel
Re: INDEX [message #335997 is a reply to message #335991] Thu, 24 July 2008 06:18 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
You mean for example in my table the indexed column values are

staff_name :
==============
Ashok
krish
Ashok
suresh
kannan
Ashok


and i fire one select query which is given below

select emp_no,statff_name,designation from emp where
             staff_name='Ashok';


According to your explanation the Ashok will be taken as a key value in the branch block and first,third and sixth row of the value and corresponding rowid will be in the leaf block and corresponding rows are displayed as the result set for the query am right
Re: INDEX [message #336000 is a reply to message #335997] Thu, 24 July 2008 06:42 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A branch block points to N leaf blocks. For each block, the branch contains the minimum key value for this block.
A leaf block contains the key value and the rowid for each row.

Regards
Michel
Previous Topic: DB 9i migration to 10g - Network configuration
Next Topic: How to setup a job to identify any trace file which contains "ORA-" in it
Goto Forum:
  


Current Time: Sat Dec 10 03:21:08 CST 2016

Total time taken to generate the page: 0.07560 seconds