Home » SQL & PL/SQL » SQL & PL/SQL » Question / Problem with Descening Indexes on Date-Fields
Question / Problem with Descening Indexes on Date-Fields [message #210490] Thu, 21 December 2006 01:33 Go to next message
Uwe
Messages: 260
Registered: February 2003
Location: Z├╝rich, Switzerland
Senior Member
Hi All,
we have a strange behaviour by creating Indexex on date-fields within an Oracle 9.2.0.7 on AIX. Does anyone have some information why this happens ?

Problem description:
When trying to create an index which contains a date field set to be in descending order, the system creates it but 1) the index is described in DBA_INDEXES as of "function-type index" and 2)the name of the original column in DBA_IND_COLUMNS is replaced by something similar to "SYS_xxx...". This does not happen when the column in the index is defined to be in ascending order (default value).
Detailed description:
1) Normal behaviour - Index containing columns in ascending order
Here we create a table with two normal columns and a normal index:
CREATE TABLE TESTASC (COL1 VARCHAR2(10), COL2 DATE);
CREATE INDEX TESTASC_IDX ON TESTASC (COL1 ASC, COL2 ASC);

The resulting index...
select INDEX_TYPE
from DBA_INDEXES
WHERE TABLE_NAME = 'TESTASC'
AND INDEX_NAME = 'TESTASC_IDX';
...of type "NORMAL" and the columns show up...
select COLUMN_POSITION, COLUMN_NAME from DBA_IND_COLUMNS
WHERE TABLE_NAME = 'TESTASC'
AND INDEX_NAME = 'TESTASC_IDX'
ORDER BY COLUMN_POSITION;
...as follows:
COLUMN_POSITION COLUMN_NAME
1 COL1
2 COL2
So, everything is fine here.

2) Unexpected behaviour - Index containing columns in descending order
We create again a normal table but this time the index has the second column defined as descending:
CREATE TABLE TESTDESC (COL1 VARCHAR2(10), COL2 DATE);
CREATE INDEX TESTDESC_IDX ON TESTDESC (COL1 ASC, COL2 DESC);

This time the resulting index is shown...
select INDEX_TYPE
from DBA_INDEXES
WHERE TABLE_NAME = 'TESTDESC'
AND INDEX_NAME = 'TESTDESC_IDX';
...as of type "FUNCTION-BASED NORMAL" and the second column shows up...
select COLUMN_POSITION, COLUMN_NAME from DBA_IND_COLUMNS
WHERE TABLE_NAME = 'TESTDESC'
AND INDEX_NAME = 'TESTDESC_IDX'
ORDER BY COLUMN_POSITION
...as follows:
COLUMN_POSITION COLUMN_NAME
1 COL1
2 SYS_NC00003$

Why? Why does the second column is not called in this case "COL2" and why is the type of index "FUNCTION-BASED NORMAL" when we didn't specify any function? How can we make the system create in the second example a normal index with a normal column name if we want to keep the order descending?

regards
Uwe
Re: Question / Problem with Descening Indexes on Date-Fields [message #210509 is a reply to message #210490] Thu, 21 December 2006 02:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm confused - why do you care how Oracle handles the indexes internally if it works correctly? I could understand curiosity about why it does it this way, but the insistance on having the index created in a different way is strange.

Everythhing Oracle is doing is correct - From the documentation
Quote:
Oracle treats descending indexes as if they were function-based indexes. You do not need the QUERY REWRITE or GLOBAL QUERY REWRITE privileges to create them, as you do with other function-based indexes. However, as with other function-based indexes, Oracle does not use descending indexes until you first analyze the index and the table on which the index is defined. See the column_expression clause of this statement
Previous Topic: How can I get Client IP Address?senthil
Next Topic: Instead of clause in a trigger
Goto Forum:
  


Current Time: Fri Dec 02 14:20:48 CST 2016

Total time taken to generate the page: 0.07068 seconds