About Oracle
Indexes
Indexes require rebuilding when deleted leaf
rows appear or when the index has a
suboptimal number of block gets per access.
While it is tempting to write a script that
rebuilds every index in the schema, bear in
mind that your schema may contain many
thousands of indexes, and a complete rebuild
can be very time consuming. Hence, we need
to develop a method to identify those
indexes that will get improved performance
with a rebuild. Let's look at one method for
accomplishing this task.
Prior to Oracle9i, the only way for an
Oracle DBA to monitor the usage of indexes
was to derive the execution plan for all SQL
inside their library cache and manually note
all indexes that are used.
Recent studies have found that an Oracle
database never uses more than a quarter of
all indexes available or doesn't use them in
the way for which they were intended.
Un-used indexes waste space and also
slow-down all DML, especially UPDATE and
INSERT statements.
Regardless, this tool is quite useful
for the Oracle DBA who inherits a database
that was built by a beginner who
over-indexes the tables without regard to
the SQL that accessed the tables.
In sum, the index monitoring feature has
very little overhead and it is quite useful
for locating and dropping unneeded Oracle
indexes.
Find indexes not in the KEEP Pool
A common problem occurs when an Oracle
professional recognizes the benefit of
placing small, frequently referenced tables
in the KEEP pool, but forgets to place the
associated indexes in the KEEP pool.
Just as you want frequently referenced
tables in the KEEP pool, you also want to
cache the indexes to minimize disk I/O.
Compress option - The compress option
is used to repress duplication of keys in
non-unique indexes. For concatenated indexes
(indexes with multiple columns), the
compress option can reduce the size of the
index by more than half. The compress option
allows you to specify the prefix length for
multiple column indexes. In this example we
have a non-unique index on several low
cardinality columns (sex and hair_color),
and a high cardinality column (customer_id):
CREATE INDEX cust_dup_idx
ON customer(sex, hair_color, customer_id)
PARALLEL 35
NOLOGGING
COMPRESS 2;
Create Oracle indexes
This exercise will give you an opportunity
to see how the SQL optimizer accesses
indexes to generate an efficient access
plan.
Your assignment involves creating these
indexes in your pubs schema and noting how
they change the execution plan for your SQL.
Because the pubs database is so small, the
cost-based optimizer will choose full-table
scans because it knows that the tables
reside on only a few database blocks. Hence,
we will force the use of the indexes with an
index hint inside the SQL statement.
For more information on creating Oracle
indexes:
http://remote-dba.net/pl_sql/t_create_oracle_indexes.htm
|
|
and include the URL for the page.
