Oracle Database 12c R1 New Features - Multiple Indexes on the same column or set of columns
In DB releases prior to Oracle DB 12c, you could not create multiple indexes either on the same column or set of columns in any form.
But now in DB 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.
Example:
SQL> CREATE TABLE EMP_RECORDS
2 (EMP_NO number (5),
3 EMP_NAME varchar2 (30),
4 EMP_SAL number (6) INVISIBLE)
5 /
SQL> alter session set optimizer_use_invisible_indexes = true;
Session altered.
SQL> CREATE INDEX EMP_IND1 ON EMP_RECORDS (EMP_NO, EMP_NAME);
Index created.
SQL> CREATE BITMAP INDEX EMP_IND2 ON EMP_RECORDS (EMP_NO, EMP_NAME) INVISIBLE;
Index created.
Related:
Oracle Database 12c R1 New Features - Invisible columns
Installing Oracle Database 12c R1 (12.1.0.1.0)
Oracle Database 12c R1 New Features - Invisible columns
In Oracle Database 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column does not appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. You can add or modify a column to be invisible and vice versa.
You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.
Example:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
SQL> CREATE TABLE EMP_RECORDS
2 (EMP_NO number (5),
3 EMP_NAME varchar2 (30),
4 EMP_SAL number (6) INVISIBLE)
5 /
Table created.
SQL> desc EMP_RECORDS;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NUMBER(5)
EMP_NAME VARCHAR2(30)
SQL> alter table emp_records
2 modify (emp_sal visible)
3 /
Table altered.
SQL> desc emp_records
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NUMBER(5)
EMP_NAME VARCHAR2(30)
EMP_SAL NUMBER(6)
Related:
Installing Oracle Database 12c R1 (12.1.0.1.0)
Oracle Database 12c R1 New Features - Multiple Indexes on the same column or set of columns
Installation of Oracle Database 12c R1 (12.1.0.1.0)
Run setup.exe from installer directory (This is on windows, similarly invoke installer on Linux environment)
Oracle Database 12c Release 1 Installer - Installing Database
Step 1: Configure security updates
Enter My Oracle Support Username / Password to receive security updates via My Oracle Support (optional)
Step 2: Download Software updates
Enter My Oracle Support credentials to download software updates for this installation (optional)
Step 3: Select Installation Option
i) Create and configure a database (Select this option)
ii) Install database software only
iii)Upgrade an existing database
Step 4: System Class
i) Desktop class (Choose this option)
ii)Server class
Step 5: Specify Oracle Home User
i) Use Existing Windows User
ii) Create New Windows USer
iii)Use Windows Built-in account (Option chosen - No need to specify username or password)
Step 6: Typical Install Configuration
Enter Administrative password: Simple4u
Step 7: Prerequisite checks
Step 8: Summary
Step 9: Install
At the end of installation, you will see on screen
The installation of Oracle Database was successful.
Your database configuration files have been installed in C:\app\ while other components selected for installation have been installed in C:\app\product\12.1.0\dbhome_1. Be cautious not to accidentally delete these configuration files.
SQL> select instance_name, status, version, database_status from v$instance;
INSTANCE_NAME STATUS VERSION DATABASE_STATUS
---------------- ------------ ----------------- -----------------
newdb12c OPEN 12.1.0.1.0 ACTIVE
Deinstalling Oracle Database 12c:
Go to $DB_HOME/deinstall and execute deinstall script.
For eg:
C:\app\db12c\product\12.1.0\dbhome_1\deinstall>deinstall
Related:
Oracle Database 12c R1 New Features - Invisible columns
