Home » Other » Training & Certification » How indexes in oracle work  () 1 Vote
How indexes in oracle work [message #261652] Thu, 23 August 2007 04:07 Go to next message
sasnrock
Messages: 19
Registered: August 2007
Junior Member
Can any one tell me how exactly the indexes work. It will be better if you take an example(say create index on a column) and explain.
Re: How indexes in oracle work [message #261653 is a reply to message #261652] Thu, 23 August 2007 04:12 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member

See this link ORACLE INDEX.

Cheers
Sanka
Re: How indexes in oracle work [message #261655 is a reply to message #261652] Thu, 23 August 2007 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Concepts
Chapter 5 Schema Objects
Section Overview of Indexes

Regards
Michel
Re: How indexes in oracle work [message #261659 is a reply to message #261653] Thu, 23 August 2007 04:38 Go to previous messageGo to next message
sasnrock
Messages: 19
Registered: August 2007
Junior Member
Hi Sanka,

Please let me know if my understanding is correct.

Say there is a customer table and has columns (customer ID, name, DOB, place). Now we have created an index (say B-tree index) xyz on customer (customerID)

Now oracle will create a B-tree structure based on customer ID and the data will be retrieved accordingly.

One ques here Smile
So will the B-tree structure created immediately after creating the index or will it be created each time when we are trying to retrieve the data (select query is executed).

Re: How indexes in oracle work [message #261696 is a reply to message #261659] Thu, 23 August 2007 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read the link I posted?

Regards
Michel
Re: How indexes in oracle work [message #262603 is a reply to message #261696] Mon, 27 August 2007 17:38 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Is this not a fair question? The original poster (by the way, I have seen you critique others for IM speak yet you use acronymns such as BTW and OP in your posts) asked the forum to explain indices in Oracle. Following a couple of responses that specify where to go for documentation, the poster replies back with his/her understanding of the way indices work with a follow on question. Obviously by the explanation, the individual has made some progress in his/her understanding. Does the documentation you specified even directly answer the question? Perhaps it does, but after a cursory glance, I did not see it.
Quote:
will the B-tree structure created immediately after creating the index or will it be created each time

I see the documentation specifically addresses the allocation of space when an index is created. You and I understand that means the index is created once, but that understanding comes from previous experience and knowledge. There are references in the documentation that indicate the index is automatically maintained by Oracle as the data change. Could this not mean to a novice that the entire index is rebuilt upon the execution of a query to ensure it is up-to-date? That is just one example of how, even after reading the documentation, someone could be unclear about how it works.

Bottom line, you spent the time to look at and reply to the thread, why not just respond with the answer that the index is built immediately?
Re: How indexes in oracle work [message #262637 is a reply to message #262603] Mon, 27 August 2007 23:52 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this not a fair question?

Maybe, isn't mine fair? I wanted to be dure he read it, so I know what he knows and where I have to start to explain.
Quote:
I have seen you critique others for IM speak

OraFAQ Forum Guide, section Posting guidelines.
Quote:
yet you use acronymns such as BTW and OP in your posts

These existed far before IM speak existed, I use them for more than 15 years. I try to avoid them (except OP) but from time to time they raise. I will try better.
By the way, I use "btw" at the beginning of a sentence, so it can be remove without changing the meaning. Not the case of the posts where I made the remark.
Quote:
Following a couple of responses that specify where to go for documentation, the poster replies back with his/her understanding of the way indices work with a follow on question.

Most of the time, the question raises a couple of minutes just after the post meaning the OP (sorry) receives the post and read dozen of pages in this time; doubtful.
Quote:
Obviously by the explanation, the individual has made some progress in his/her understanding.

No so obvious for me (otherwise I posted other thing). Maybe (and Im' pretty sure this is the case) OP (sorry again) just precises his question, what he first had in mind.

In addition, why didn't he just answer "yes" if it was the case?

Regards
Michel

Previous Topic: difference b/w Sql & Sql * Plus commands
Next Topic: Certification
Goto Forum:
  


Current Time: Wed Dec 07 22:20:13 CST 2016

Total time taken to generate the page: 0.16340 seconds