0034258: Not possible to apply two indexes on the same column but with different operator class in Oracle

Anonymous | Login

Project:

News | My View | View Issues | Roadmap | Summary

View Revisions: Issue #34258		[ Back to Issue ]
Summary	0034258: Not possible to apply two indexes on the same column but with different operator class in Oracle

Revision	2016-10-20 19:09 by caristu
Description	Is not possible to apply two indexes on the same column but with different operator class in Oracle. This model definition is valid in PostgreSQL but currently not possible to apply in Oracle. Oracle does not allow to have more that one index defined for a particular set of columns, and as the operator class definition is not taken into account in Oracle, eventually two indexes for the same column, one with operator class and the other one without it, are considered the same in Oracle.

Revision	2016-10-20 19:02 by caristu
Description	Not possible to apply two indexes on the same column but with different operator class in Oracle. This model definition is valid in PostgreSQL but currently not possible to apply in Oracle.

Revision	2016-10-20 19:00 by caristu
Steps To Reproduce	1) In an Oracle based environment, apply the attached patch which contains two index definitions. They both apply to the same column, but one of them make use of an operator class. 2) Run ant update.database -Dforce=yes. The following error is thrown by the task and it does not end correctly: [java] 263567 WARN - SQL Command failed with: ORA-01408: such column list already indexed [java] [java] 263583 WARN - -- END [java] CREATE INDEX A_AMORTIZATION_UPPER_OPC ON A_AMORTIZATION (NAME) [java] 281824 INFO - Executed 604 SQL command(s) with 1 error(s) 3) Repeat steps 1) and 2) in a PostgreSQL based environment. Both indexes are created properly.

Revision	2016-10-20 18:59 by caristu
Steps To Reproduce	1) In an Oracle based environment, apply the attached patch which contains two index definitions. They both apply to the same column, but one of them make use of an operator class. 2) Run ant update.database -Dforce=yes. The following error is thrown by the task: [java] 263567 WARN - SQL Command failed with: ORA-01408: such column list already indexed [java] [java] 263583 WARN - -- END [java] CREATE INDEX A_AMORTIZATION_UPPER_OPC ON A_AMORTIZATION (NAME) [java] 281824 INFO - Executed 604 SQL command(s) with 1 error(s) 3) Repeat steps 1) and 2) in a PostgreSQL based environment. Both indexes are created properly.