Working with Indexes

Index Creation

CREATE INDEX employee_id ON employee(employee_id) TABLESPACE emp_index_01;

If you are creating an index for a large table with data already populated, you can choose to
collect optimizer statistics at table creation time by specifying the COMPUTE STATISTICS option as
shown in this example:

CREATE INDEX employee_id ON employee(employee_id)
TABLESPACE emp_index_01
COMPUTE STATISTICS;

CREATE UNIQUE INDEX employee_id ON employee(employee_id)
TABLESPACE emp_index_01;

Composite Index

CREATE INDEX employee_id ON employee(employee_id,location_id)
TABLESPACE emp_index_01;

CREATE TABLE dept(
dept_no NUMBER(3),
dept_name VARCHAR2(15),
location VARCHAR2(25),
CONSTRAINT dept_name_ukey UNIQUE(dept_Name,location);

CREATE TABLE employee (
empno NUMBER (5) PRIMARY KEY, age INTEGER)
ENABLE PRIMARY KEY USING INDEX
TABLESPACE users;

The CREATE TABLE statement shown here enables the primary key constraint, which automatically

creates a unique index on the empno column.
You can also specify that the database use an existing index to enforce a new constraint you are
creating, as shown in this example:

ALTER TABLE employee ADD CONSTRAINT test_const1
PRIMARY KEY (pkey1) USING INDEX ind1;

CREATE TABLE employee (
emp_id INT PRIMARY KEY USING INDEX (create index ind1
ON employee (emp_id)));

---------------------------------------------------------------------------------------------
Bitmap Indexes

Bitmap indexes use bitmaps to indicate the value of the column being indexed. This is an ideal index
for a column with a low cardinality and a large table size. These indexes are not usually appropriate
for tables with heavy updates and are well suited for data warehouse applications.

CREATE BITMAP INDEX gender_idx ON employee(gender) TABLESPACE emp_index_05;
--------------------------------------------------------------------------------------------------------------------------------
Reverse-Key Indexes

Reverse-key indexes are fundamentally the same as B-tree indexes, except that the bytes of key
column data are reversed during indexing. The column order is kept intact; only the bytes are
reversed. The biggest advantage to using reverse-key indexes is that they tend to avoid hot spots
when you do sequential insertion of values into the index. Here’s how to create one:

CREATE INDEX reverse_idx ON employee(emp_id) REVERSE;

----------------------------------------------------------------------------------------------------------------------------------
Key-Compressed Indexes

You can save index storage space as well as improve performance by creating indexes using key
compression. Any time the indexed key has a repeatedly occurring component, or you are creating
a unique multicolumn index, you’ll benefit by using key compression. Here is an example:

CREATE INDEX emp_indx1 ON employees(ename)
TABLESPACE users
COMPRESS 1;
---------------------------------------------------------------------------------------------
Function-Based Indexes

Function-based indexes precompute functions on a given column and store the results in an index.
When where clauses include functions, function-based indexes are an ideal way to index the column.
Here’s how to create a function-based index, using the LOWER function:

CREATE INDEX lastname_idx ON employees(LOWER(l_name));

---------------------------------------------------------------------------------------------

Partitioned Indexes

---------------------------------------------------------------------------------------------
Global Indexes
Global indexes on a partitioned table can be either partitioned or nonpartitioned. The globally
nonpartitioned indexes are similar to the regular Oracle indexes for nonpartitioned tables. You just
use the regular CREATE INDEX syntax to create these globally nonpartitioned indexes.
Here’s an example of a global index on the ticket_sales table:

CREATE INDEX ticketsales_idx ON ticket_sales(month)
GLOBAL PARTITION BY range(month)
(PARTITION ticketsales1_idx VALUES LESS THAN (3)
PARTITION ticketsales1_idx VALUES LESS THAN (6)
PARTITION ticketsales2_idx VALUES LESS THAN (9)
PARTITION ticketsales3_idx VALUES LESS THAN (MAXVALUE);


ALTER TABLE ticket_sales
DROP PARTITION sales_quarter01
UPDATE GLOBAL INDEXES;

CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL
PARTITION BY HASH (c1,c2)
(PARTITION p1 TABLESPACE tbs_1,
PARTITION p2 TABLESPACE tbs_2,
PARTITION p3 TABLESPACE tbs_3,
PARTITION p4 TABLESPACE tbs_4);

CREATE INDEX ticket_no_idx ON
ticket_sales(ticket__no) LOCAL
TABLESPACE localidx_01;

---------------------------------------------------------------------------------------------
Creating an Invisible Index

CREATE INDEX test_idx ON test(tname)
TABLESPACE testdata
STORAGE (INITIAL 20K
NEXT 20k
PCTINCREASE 75)
INVISIBLE;

---------------------------------------------------------------------------------------------
ALTER INDEX test_idx INVISIBLE;

To make an invisible index visible again, use the following statement:

ALTER INDEX test_idx VISIBLE;

A query on the DBA_INDEXES view indicates whether an index is visible, as shown here:

SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'INDX1';

---------------------------------------------------------------------------------------------
Monitoring Index Usage

ALTER INDEX p_key_sales MONITORING USAGE;

Index altered.

Now, run some queries on the sales table. End the monitoring by using the following command:

ALTER INDEX p_key_sales NOMONITORING USAGE;

Index altered.

SELECT * FROM v$object_usage 
WHERE index_name='P_KEY_SALES';

----------------------------------------------------------------------------------------------------------
Index Maintenance

ALTER INDEX sales_idx REBUILD;

ALTER INDEX p_key_sales REBUILD ONLINE;


---------------------------------------------------------------------------------------------