INDEX
An index is an optionally created database object used primarily to increase query performance
Similar to a book index, a database index stores the column value of interest along
with its row identifier (ROWID). The ROWID contains the physical location of the table row on disk that
stores the column value. With the ROWID in hand, Oracle can efficiently retrieve table data with a
minimum of disk reads. In this way, indexes function like a shortcut to the table data. If there is no
available index, then Oracle reads each row in the table to determine if the row contains the desired
information.
Improving Performance with Indexes----
When a SQL select statement executes, the Oracle query optimizer quickly calculates a
step-by-step execution plan detailing how it will retrieve column values specified in the query. In
calculating the plan, the optimizer determines which tables and indexes will be used to retrieve data.
When no index exists, the table itself is the only access path available to satisfy the results of the
query. In this scenario, Oracle has no choice but to inspect every row within every used block in the
table(this is known as a full table scan).
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Ename = 'ALLEN';
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=26)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=
26)
SQL> CREATE INDEX EmpEnameIDX
2 ON Emp(Ename);
Index created.
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Ename = 'ALLEN';
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=26)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car
d=1 Bytes=26)
2 1 INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=1 Card
=1)
SQL> ed
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3* WHERE Empno = 7839 OR Ename = 'KING'
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=39)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=3 Car
d=1 Bytes=39)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UN
IQUE)) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=
1)
SQL> SELECT TABLE_NAME, INDEX_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'EMP';
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'EMP';
SQL> DROP INDEX EMPENAMEIDX;
When the SELECT clause columns are all contained with an index, this is known
as a covering index. These indexes are particularly efficient because only the index blocks need to be
read
• Indexes are optional objects defined on a table and one or more columns.
• Indexes consume resources.
• A B-tree index is the default index type in Oracle.
• A fairly unique column value compared to all other rows in a table results in a
more efficient B-tree index.
• When appropriately created, indexes improve performance
• In some scenarios, the query optimizer will choose not to use an index. In other
words, the query optimizer calculates that the cost of a full table scan is less than
the cost when using an index.
• In some situations, Oracle can retrieve data for a query by only accessing the
index; the table doesn’t have to be accessed
Determining Which Type of Index to Use-----
Guidelines for When to Index
You want to consider indexing columns frequently used in the WHERE clause of SQL statements and
foreign key columns. Oracle automatically creates a unique index to
enforce the primary key constraint and the unique constraint. The following are some general guidelines
when an index is typically useful.
Frequently accessed columns containing highly selective data for B-tree indexes.
Columns frequently accessed with a small range of values for bitmap indexes.
Columns that are frequently accessed and that contain many null values, but the query is looking for the
NOT NULL values.
Frequent queries against large tables retrieving less than 5 to 15 percent of the rows. The percentage
may vary, depending on a number of factors, including the size of the table.
Building an index is often useless if:
The table is small, but you should nevertheless create unique and primary constraints to enforce
business rules.
The query retrieves more than 5 to 15 percent of the rows.
The indexed column is part of an expression. In this case, consider creating a function-based index
instead.
Keep in mind that adding indexes may increase the time required for data manipulation operations, such
as INSERT, UPDATE, and DELETE. If you primarily query the table,
then creating the index may offset the disadvantage of additional time required for DML statements
B-tree Indexes
the default index type in Oracle is a B-tree index. This index type is very efficient for high cardinality column values.
Prerequisites
To create an index in your own schema, one of the following conditions must be true:
The table or cluster to be indexed must be in your own schema.
You must have the INDEX object privilege on the table to be indexed.
You must have the CREATE ANY INDEX system privilege.
Without specifying any options, a B-tree is created with the CREATE INDEX statement; all you need to
provide is the index name, table name, and column(s).
create index cust_idx2
on cust(first_name);
SQL> SELECT TABLE_NAME, INDEX_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'EMP';
There are several subtypes of B-tree indexes.
Index-organized table
Unique
Reverse key
Key compressed
Descending
Index-Organized Table
An index-organized table (IOT) stores the entire contents of the table’s row in a B-tree index structure.
An IOT provides fast access for queries that have exact matches and/or range searches on the primary
key.
create table prod_sku
(prod_sku_id number
,sku varchar2(256),
constraint prod_sku_pk primary key(prod_sku_id, sku)
) organization index;
Unique Indexes
When creating a B-tree index you can define it to be a unique index. In this regard it acts like a unique
key constraint. When inserting into the corresponding table, the unique index will guarantee that any
non-null values inserted into the table are distinct. For this reason, unique indexes are commonly used
in conjunction with primary key and unique key constraints
A unique index is specified via the CREATE UNIQUE INDEX statement.
create unique index cust_uidx1
on cust(last_name, first_name);
Reverse Key Indexes
Reverse key indexes are useful to balance I/O in an index that has many sequential inserts. These
indexes can perform better in scenarios where you need a way to evenly distribute index data that would
otherwise have similar values clustered together. Thus, when using a reverse-key index, you avoid
having I/O concentrated in one physical disk location within the index during large inserts of sequential
values
create index cust_ridx1
on cust(cust_id) reverse;
NULLs and Indexes
NULL values are not stored in a B-tree index, unless it is a composite index where at least the first
column of the index contains a value. The following query does not make use of the single-column index
on the FIRST_NAME column.
SELECT student_id, first_name
FROM student
WHERE first_name IS NULL
Specialized Index Types
Sometimes a B-tree index isn’t enough to provide the desired performance improvement. The following
are indexes that should be used under specialized circumstances:
Bitmap
Function-based
Invisible
Bitmap Index
Bitmap indexes are commonly used in data-warehouse environments. These indexes are recommended
for columns with a relatively low number of distinct values (low cardinality)
efficient for SQL statements that use multiple AND or OR join operators in the WHERE clause (which is
typical in a data warehouse environment).
You should not use bitmap indexes in OLTP databases with high INSERT/UPDATE/DELETE activities.
This is because the structure of the bitmap index results in many locked rows during singular DML
operations (which results in locking problems for high-transaction OLTP systems).
A bitmap index is created using the keyword BITMAP. For completeness, we also show the table
creation script upon which the bitmap index is built.
create table f_sales(
sales_amt number
,d_date_id number
,d_product_id number
,d_customer_id number);
create bitmap index f_sales_fk1
on f_sales(d_date_id);
The following statement creates a bitmap index on the GENDER column of a CUSTOMER table.
CREATE BITMAP INDEX customer_bm_gender_i
ON customer(gender)
Function-Based Indexes
Function-based indexes are created with SQL functions or expressions in their definitions. Functionbased
indexes allow index lookups on columns referenced by SQL functions in the WHERE clause of a
query. Here’s an example of creating a function-based index:
create index cust_fidx1
on cust(upper(last_name));
These types of indexes are necessary because Oracle won’t use a normal B-tree index when a query
references a column with a SQL function applied to it.
SQL> CREATE INDEX EmpTotSalIDX
2 ON Emp(Sal + NVL(Comm, 0));
Index created.
SQL> SELECT Ename, Sal, Comm, Sal + NVL(Comm, 0) TotSal, Deptno, Job
2 FROM Emp
3 WHERE Sal + NVL(Comm, 0) > 2500;
ENAME SAL COMM TOTSAL DEPTNO JOB
---------- ---------- ---------- ---------- ---------- ---------
MARTIN 1250 1400 2650 30 SALESMAN
BLAKE 2850 2850 30 MANAGER
JONES 2975 2975 20 MANAGER
FORD 3000 3000 20 ANALYST
SCOTT 3000 3000 20 ANALYST
KING 5000 5000 10 PRESIDENT
6 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=6 Bytes=312
)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car
d=6 Bytes=312)
2 1 INDEX (RANGE SCAN) OF 'EMPTOTSALIDX' (INDEX) (Cost=1 Car
d=1)
Invisible Index
An invisible index means the optimizer doesn’t use the index when retrieving data for a query. However,
the index structure is still maintained as the underlying table has records inserted, updated, or deleted.
This feature is used when you want to test the viability of an index without impacting existing
application code. Use the INVISIBLE keyword to create an invisible index.
create index cust_iidx1
on cust(last_name) invisible;
Note Invisible indexes are only available in Oracle Database 11g and higher.
Indexes and Constraints
When you create a primary key constraint or a unique constraint, Oracle creates the index automatically
unless a suitable index already exists
The index NEW_TERM_PK is created as part of the CREATE TABLE statement and is associated with the
primary key constraint.
sql>CREATE TABLE new_term
(term_no NUMBER(8) NOT NULL PRIMARY KEY USING INDEX
(CREATE INDEX new_term_pk ON new_term(term_no)
STORAGE (INITIAL 100 K NEXT 100K)),
season_tx VARCHAR2(20),
sequence_no NUMBER(3))
If you already have an existing index and you want to associate a constraint with it, you can use a
statement similar to the following. It assumes an existing index called
SEMESTER_SEMESTER_ID_I, based on the SEMESTER_ID column.
ALTER TABLE semester
ADD CONSTRAINT semester_pk PRIMARY KEY (semester_id)
USING INDEX semester_semester_id_i
The next statement shows an example of a unique constraint that is associated with a unique index.
CREATE TABLE semester
(semester_id NUMBER(8),
semester_name VARCHAR2(8) NOT NULL,
year_no NUMBER(4) NOT NULL,
CONSTRAINT semester_uk UNIQUE (semester_name, year_no)
USING INDEX
(CREATE UNIQUE INDEX semester_sem_yr_uk
ON semester(semester_name, year_no)))
note--When disabling a unique or primary key, you can keep the index if you specify the KEEP INDEX
clause in an ALTER TABLE statement
Indexes and Foreign Keys
You should almost always index foreign keys because they are frequently used in joins.
Dropping an Index
To drop an index, use the DROP INDEX command. You might drop an index if queries in your applications
do not utilize the index. You find out which indexes are used by
querying the V$OBJECT_USAGE data dictionary view.
>DROP INDEX sect_location_i
Index dropped.
---------When you drop a table, all associated indexes are dropped automatically
Loading Large Amounts of Data
When you insert or update large amounts of data, you might want to consider dropping certain indexes
not used for the DML operation’s WHERE clause to improve performance. After the operation is
complete, you can re-create the appropriate indexes.
One fast way to re-create indexes is by using the NOLOGGING option. It avoids writing to the redo log,
which keeps track of all the database changes. If you incur a fatal database error and you need to
recover from the redo log, the index will not be recovered. This may be fine because an index can always
be re-created.
You can also create an index by using the PARALLEL option. This allows parallel scans of the table to
create the index and can make index creation much faster, provided that you have the appropriate
hardware configuration, such as multiple CPUs.
An index is an optionally created database object used primarily to increase query performance
Similar to a book index, a database index stores the column value of interest along
with its row identifier (ROWID). The ROWID contains the physical location of the table row on disk that
stores the column value. With the ROWID in hand, Oracle can efficiently retrieve table data with a
minimum of disk reads. In this way, indexes function like a shortcut to the table data. If there is no
available index, then Oracle reads each row in the table to determine if the row contains the desired
information.
Improving Performance with Indexes----
When a SQL select statement executes, the Oracle query optimizer quickly calculates a
step-by-step execution plan detailing how it will retrieve column values specified in the query. In
calculating the plan, the optimizer determines which tables and indexes will be used to retrieve data.
When no index exists, the table itself is the only access path available to satisfy the results of the
query. In this scenario, Oracle has no choice but to inspect every row within every used block in the
table(this is known as a full table scan).
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Ename = 'ALLEN';
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=26)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=
26)
SQL> CREATE INDEX EmpEnameIDX
2 ON Emp(Ename);
Index created.
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Ename = 'ALLEN';
ENAME SAL JOB
---------- ---------- ---------
ALLEN 1600 SALESMAN
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=26)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car
d=1 Bytes=26)
2 1 INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=1 Card
=1)
SQL> ed
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3* WHERE Empno = 7839 OR Ename = 'KING'
SQL> /
ENAME SAL JOB
---------- ---------- ---------
KING 5000 PRESIDENT
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=39)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=3 Car
d=1 Bytes=39)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP OR
4 3 BITMAP CONVERSION (FROM ROWIDS)
5 4 INDEX (RANGE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UN
IQUE)) (Cost=1)
6 3 BITMAP CONVERSION (FROM ROWIDS)
7 6 INDEX (RANGE SCAN) OF 'EMPENAMEIDX' (INDEX) (Cost=
1)
SQL> SELECT TABLE_NAME, INDEX_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'EMP';
SQL> SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE, SEARCH_CONDITION
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'EMP';
SQL> DROP INDEX EMPENAMEIDX;
When the SELECT clause columns are all contained with an index, this is known
as a covering index. These indexes are particularly efficient because only the index blocks need to be
read
• Indexes are optional objects defined on a table and one or more columns.
• Indexes consume resources.
• A B-tree index is the default index type in Oracle.
• A fairly unique column value compared to all other rows in a table results in a
more efficient B-tree index.
• When appropriately created, indexes improve performance
• In some scenarios, the query optimizer will choose not to use an index. In other
words, the query optimizer calculates that the cost of a full table scan is less than
the cost when using an index.
• In some situations, Oracle can retrieve data for a query by only accessing the
index; the table doesn’t have to be accessed
Determining Which Type of Index to Use-----
Guidelines for When to Index
You want to consider indexing columns frequently used in the WHERE clause of SQL statements and
foreign key columns. Oracle automatically creates a unique index to
enforce the primary key constraint and the unique constraint. The following are some general guidelines
when an index is typically useful.
Frequently accessed columns containing highly selective data for B-tree indexes.
Columns frequently accessed with a small range of values for bitmap indexes.
Columns that are frequently accessed and that contain many null values, but the query is looking for the
NOT NULL values.
Frequent queries against large tables retrieving less than 5 to 15 percent of the rows. The percentage
may vary, depending on a number of factors, including the size of the table.
Building an index is often useless if:
The table is small, but you should nevertheless create unique and primary constraints to enforce
business rules.
The query retrieves more than 5 to 15 percent of the rows.
The indexed column is part of an expression. In this case, consider creating a function-based index
instead.
Keep in mind that adding indexes may increase the time required for data manipulation operations, such
as INSERT, UPDATE, and DELETE. If you primarily query the table,
then creating the index may offset the disadvantage of additional time required for DML statements
B-tree Indexes
the default index type in Oracle is a B-tree index. This index type is very efficient for high cardinality column values.
Prerequisites
To create an index in your own schema, one of the following conditions must be true:
The table or cluster to be indexed must be in your own schema.
You must have the INDEX object privilege on the table to be indexed.
You must have the CREATE ANY INDEX system privilege.
Without specifying any options, a B-tree is created with the CREATE INDEX statement; all you need to
provide is the index name, table name, and column(s).
create index cust_idx2
on cust(first_name);
SQL> SELECT TABLE_NAME, INDEX_NAME
2 FROM USER_INDEXES
3 WHERE TABLE_NAME = 'EMP';
There are several subtypes of B-tree indexes.
Index-organized table
Unique
Reverse key
Key compressed
Descending
Index-Organized Table
An index-organized table (IOT) stores the entire contents of the table’s row in a B-tree index structure.
An IOT provides fast access for queries that have exact matches and/or range searches on the primary
key.
create table prod_sku
(prod_sku_id number
,sku varchar2(256),
constraint prod_sku_pk primary key(prod_sku_id, sku)
) organization index;
Unique Indexes
When creating a B-tree index you can define it to be a unique index. In this regard it acts like a unique
key constraint. When inserting into the corresponding table, the unique index will guarantee that any
non-null values inserted into the table are distinct. For this reason, unique indexes are commonly used
in conjunction with primary key and unique key constraints
A unique index is specified via the CREATE UNIQUE INDEX statement.
create unique index cust_uidx1
on cust(last_name, first_name);
Reverse Key Indexes
Reverse key indexes are useful to balance I/O in an index that has many sequential inserts. These
indexes can perform better in scenarios where you need a way to evenly distribute index data that would
otherwise have similar values clustered together. Thus, when using a reverse-key index, you avoid
having I/O concentrated in one physical disk location within the index during large inserts of sequential
values
create index cust_ridx1
on cust(cust_id) reverse;
NULLs and Indexes
NULL values are not stored in a B-tree index, unless it is a composite index where at least the first
column of the index contains a value. The following query does not make use of the single-column index
on the FIRST_NAME column.
SELECT student_id, first_name
FROM student
WHERE first_name IS NULL
Specialized Index Types
Sometimes a B-tree index isn’t enough to provide the desired performance improvement. The following
are indexes that should be used under specialized circumstances:
Bitmap
Function-based
Invisible
Bitmap Index
Bitmap indexes are commonly used in data-warehouse environments. These indexes are recommended
for columns with a relatively low number of distinct values (low cardinality)
efficient for SQL statements that use multiple AND or OR join operators in the WHERE clause (which is
typical in a data warehouse environment).
You should not use bitmap indexes in OLTP databases with high INSERT/UPDATE/DELETE activities.
This is because the structure of the bitmap index results in many locked rows during singular DML
operations (which results in locking problems for high-transaction OLTP systems).
A bitmap index is created using the keyword BITMAP. For completeness, we also show the table
creation script upon which the bitmap index is built.
create table f_sales(
sales_amt number
,d_date_id number
,d_product_id number
,d_customer_id number);
create bitmap index f_sales_fk1
on f_sales(d_date_id);
The following statement creates a bitmap index on the GENDER column of a CUSTOMER table.
CREATE BITMAP INDEX customer_bm_gender_i
ON customer(gender)
Function-Based Indexes
Function-based indexes are created with SQL functions or expressions in their definitions. Functionbased
indexes allow index lookups on columns referenced by SQL functions in the WHERE clause of a
query. Here’s an example of creating a function-based index:
create index cust_fidx1
on cust(upper(last_name));
These types of indexes are necessary because Oracle won’t use a normal B-tree index when a query
references a column with a SQL function applied to it.
SQL> CREATE INDEX EmpTotSalIDX
2 ON Emp(Sal + NVL(Comm, 0));
Index created.
SQL> SELECT Ename, Sal, Comm, Sal + NVL(Comm, 0) TotSal, Deptno, Job
2 FROM Emp
3 WHERE Sal + NVL(Comm, 0) > 2500;
ENAME SAL COMM TOTSAL DEPTNO JOB
---------- ---------- ---------- ---------- ---------- ---------
MARTIN 1250 1400 2650 30 SALESMAN
BLAKE 2850 2850 30 MANAGER
JONES 2975 2975 20 MANAGER
FORD 3000 3000 20 ANALYST
SCOTT 3000 3000 20 ANALYST
KING 5000 5000 10 PRESIDENT
6 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=6 Bytes=312
)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Car
d=6 Bytes=312)
2 1 INDEX (RANGE SCAN) OF 'EMPTOTSALIDX' (INDEX) (Cost=1 Car
d=1)
Invisible Index
An invisible index means the optimizer doesn’t use the index when retrieving data for a query. However,
the index structure is still maintained as the underlying table has records inserted, updated, or deleted.
This feature is used when you want to test the viability of an index without impacting existing
application code. Use the INVISIBLE keyword to create an invisible index.
create index cust_iidx1
on cust(last_name) invisible;
Note Invisible indexes are only available in Oracle Database 11g and higher.
Indexes and Constraints
When you create a primary key constraint or a unique constraint, Oracle creates the index automatically
unless a suitable index already exists
The index NEW_TERM_PK is created as part of the CREATE TABLE statement and is associated with the
primary key constraint.
sql>CREATE TABLE new_term
(term_no NUMBER(8) NOT NULL PRIMARY KEY USING INDEX
(CREATE INDEX new_term_pk ON new_term(term_no)
STORAGE (INITIAL 100 K NEXT 100K)),
season_tx VARCHAR2(20),
sequence_no NUMBER(3))
If you already have an existing index and you want to associate a constraint with it, you can use a
statement similar to the following. It assumes an existing index called
SEMESTER_SEMESTER_ID_I, based on the SEMESTER_ID column.
ALTER TABLE semester
ADD CONSTRAINT semester_pk PRIMARY KEY (semester_id)
USING INDEX semester_semester_id_i
The next statement shows an example of a unique constraint that is associated with a unique index.
CREATE TABLE semester
(semester_id NUMBER(8),
semester_name VARCHAR2(8) NOT NULL,
year_no NUMBER(4) NOT NULL,
CONSTRAINT semester_uk UNIQUE (semester_name, year_no)
USING INDEX
(CREATE UNIQUE INDEX semester_sem_yr_uk
ON semester(semester_name, year_no)))
note--When disabling a unique or primary key, you can keep the index if you specify the KEEP INDEX
clause in an ALTER TABLE statement
Indexes and Foreign Keys
You should almost always index foreign keys because they are frequently used in joins.
Dropping an Index
To drop an index, use the DROP INDEX command. You might drop an index if queries in your applications
do not utilize the index. You find out which indexes are used by
querying the V$OBJECT_USAGE data dictionary view.
>DROP INDEX sect_location_i
Index dropped.
---------When you drop a table, all associated indexes are dropped automatically
Loading Large Amounts of Data
When you insert or update large amounts of data, you might want to consider dropping certain indexes
not used for the DML operation’s WHERE clause to improve performance. After the operation is
complete, you can re-create the appropriate indexes.
One fast way to re-create indexes is by using the NOLOGGING option. It avoids writing to the redo log,
which keeps track of all the database changes. If you incur a fatal database error and you need to
recover from the redo log, the index will not be recovered. This may be fine because an index can always
be re-created.
You can also create an index by using the PARALLEL option. This allows parallel scans of the table to
create the index and can make index creation much faster, provided that you have the appropriate
hardware configuration, such as multiple CPUs.
No comments:
Post a Comment