Can I have two indexes on a column?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Can I have two indexes on a column?

  1. #1
    Join Date
    Apr 2001
    Posts
    257
    If I have a column, say, invoice_id and it is based on a sequence, it is generally a good idea to have a Reverse Key Index rather than normal B-Tree index. However, Reverse Key Index is not good for queries based on a range of invoice_ids. So what do I do if I need to support for a query based on a range of invoice_ids? Can I have two indexes on the column? If so, can I specify which index to use in the query?

    Better yet, does anybody have a better solution?

    Thanks

  2. #2
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    U can hv two indexes on a column.

    especially when u want to create one normal index and one
    reverse key index.


    u can specify index in queries...
    --------------------------
    The Time has come ....

  3. #3
    Join Date
    May 2001
    Location
    Delhi
    Posts
    340
    Use HINTS in queries

    eg.

    select /*+ index(b) */
    --------------------------
    The Time has come ....

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can't have two indexes on the same column! Only one index per column (or per same combination of columns in the same order, when dealing with multicolumn indexes) is allowed. Even if you try to create different type of index (REVERSE, BITMAP) it will fail with "ORA-01400: such column list allready indexed".
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2000
    Posts
    384
    Yes you can have ..

    Normal index/function based index /reverse indexes all on the same column ...
    Radhakrishnan.M

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Can you make a simple demonstration that supports your claims? Here is mine, cut&pasted from SQL*Plus:
    Code:
    Connected to:
    Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
    With the Partitioning option
    JServer Release 9.0.1.1.1 - Production
    
    SQL> create table t1 (c1 number, c2 number);
    
    Table created.
    
    SQL> create index x1 on t1(c1);
    
    Index created.
    
    SQL> create index x2 on t1(c1) reverse;
    create index x2 on t1(c1) reverse
                          *
    ERROR at line 1:
    ORA-01408: such column list already indexed
    
    
    SQL> create bitmap index x3 on t1(c1);
    create bitmap index x3 on t1(c1)
                                 *
    ERROR at line 1:
    ORA-01408: such column list already indexed
    Function based indexes are something different in this context, because with function based index you are not indexing a column, you are indexing a function applied to that column.

    Bottom line: you can only have one index per column.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

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


Click Here to Expand Forum to Full Width