-
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
-
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 ....
-
Use HINTS in queries
eg.
select /*+ index(b) */
--------------------------
The Time has come ....
-
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?
-
Yes you can have ..
Normal index/function based index /reverse indexes all on the same column ...
Radhakrishnan.M
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|