Multiple indexes on the same column
Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.
Here’s an the example:
SQL> create table t3 (col1 number, col2 number);
Table created.
SQL> create index in_t3 on t3(col1);
Index created.
SQL> create index in_t3_02 on t3(col1);
create index in_t3_02 on t3(col1)
*
ERROR at line 1:
ORA-01408: such column list already indexed
SQL> create bitmap index in_t3_02 on t3(col1) invisible;
Index created.
Rules
- Different types: b-tree/bitmap
- Unique/NonUnique
- Only one is visible at a time
There was no direction option available to log the DDL action in the previous releases. In 12cR1, you can now log the DDL action into xml and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature.
The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an xml and a log file under the ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. An xml file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered.
To enable DDL logging
SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
The following DDL statements are likely to be recorded in the xml/log file:
- CREATE|ALTER|DROP|TRUNCATE TABLE
- DROP USER
- CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
<msg time='2013-08-30T20:29:36.635-04:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='STARUPNANT420B' host_addr='fe80::58b8:d0b2:f7c9:3147%27'
version='1'>
<txt> create table t11 (col1 number)
</txt>
</msg>
<msg time='2013-08-30T20:32:56.719-04:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4181:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='STARUPNANT420B' host_addr='fe80::58b8:d0b2:f7c9:3147%27'>
<txt>drop table t11
</txt>
