Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    51

    Unanswered: purpose of index-oracle

    what is the purpose of index.

    I created an index like this.
    create index <idx> on table(field desc);

    But, when i say , select * from tablename, it is not displaying in the reverse order of the field indexed in desc order.

    What's the error.
    sampath gowri

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    The error is that you forgot to specify an ORDER BY clause to say what you wanted:

    select * from tablename order by field desc;

    The purpose of an index is not to make Oracle assume you always want a particular order, but rather to assist Oracle in finding (and ordering) records according to the particular SQL it is processing.

    Think of it like the index of a book. select * from tablename; is like saying "read the whole book"; select * from tablename where field='banana'; is like saying "find the part(s) of the book concerning bananas and read them". You would use the index for the second request, but not for the first.

  3. #3
    Join Date
    Oct 2003
    Posts
    51
    If i want to say order by clause everytime, why should i create an index.
    Is there any other way to display by default in the desc order.
    sampath gowri

  4. #4
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    You're missing the point - an index in a book doesn't sort your requests for you, it just tells you where to find the information you're after. So why should an Oracle index sort your output for you? The index doesn't know what you want - it only knows where the information is stored, so that when you tell Oracle what you want the information is extracted faster than looking through the whole table. Having extracted the information, you can then tell Oracle how you would like that information displayed.
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by sampathgowri
    If i want to say order by clause everytime, why should i create an index.
    Is there any other way to display by default in the desc order.
    No. You don't create an index to avoid writing "ORDER BY", you do it (mainly) so that queries like "select * from table where indexed_col=42" or "select * from table order by indexed_col" may work quicker.

    I suggest you get yourself a database primer so that you can get to understand these concepts better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •