Kbase P75565: How does Oracle support desc keyword in index creation?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/9/2004 |
|
Status: Unverified
GOAL:
How does Oracle support desc keyword in index creation?
GOAL:
When does Oracle start supporting index column in desc order?
GOAL:
How to turn off desc keyword support in 8.1 and above?
FIX:
In the version prior to 8.1.7, where a normal index was created the DESC keyword was simply ignored, oracle just supports the syntax, in 8.1.7 it was actually implemented and the way oracle did that was with function based index generated internally.
So to turn it off would be to omit the DESC keyword.
From Note:120608.1 ( [8.1.7.0] Generic Server README for Oracle8i Release 3) :
6.2 FUNCTION-BASED INDEXES
6.2.1 Turning Off Descending Indexes
Before release 8.1, index columns were allowed to
be marked DESC during index creation, for example:
CREATE INDEX (a1, a2 ASC, a3 DESC);
However, DESC was always ignored. As of Release 8.1 such
index columns are actually in descending order. As of version 8.1.6, you
can revert to the old behavior (ignoring DESC during CREATE INDEX) by issuing:
ALTER SYSTEM SET "_ignore_desc_in_index" = true;
or by setting _ignore_desc_in_index to TRUE in the initialization
parameter file. For any existing indexes whose columns are already
descending (query the DESCEND column of DBA_IND_COLUMNS to check), you must
drop and re-create such columns if you do not want them to be descending.