Databases Reference
In-Depth Information
32KB when the database block size is 8KB, use the block size 32KB option when creating the tablespace.
Once you do this, create the index by specifying the tablespace with the large block size, as shown here:
SQL> create index cust_idx1 on customer (cust_id)
tablespace large_ts;
Using larger block sizes for indexes may offer some storage benefits because large block sizes
provide more space for storing index keys in the branch node of a B-tree index. This certainly reduces
the height of the index tree. However, the most important reason to use large block sizes is to enhance
the performance of certain types of queries— more specifically, queries that require large scans of an
index. For example, a
select
statement that utilizes a fast full scan of an index will perform much better
with an index using a large block size than with an index that uses a smaller block size. However, most
queries in an OLTP application, where index usage is highly critical, don't seek to retrieve large amounts
of data. These queries are typically designed to retrieve a specific value or a range of values from an
index. For these types of queries, a small block size is the right choice, and a very large block size actually
slows down the response time.
Compressing Indexes
You can avoid the duplication of keys in a non-unique index by specifying the
compress
option when
creating an index. When using a composite index, you can specify the prefix length, as explained in
Chapter 7. Here's an example that shows how to create a non-unique index on a composite index, where
the first two columns have a low cardinality and the third column (
cust_id
) has a high cardinality:
SQL> create index cust_idx1
on customer(sex,state, cust_id)
compress 2;
Using Multiple Options Together
In the previous sections, you learned how specifying various options such as parallel, nologging, and
compress that can help you speed up index creation or reduce index storage. You can specify multiple
options together, as shown in the following example:
SQL> create index a on x(y)
nologging
parallel 12
compress 2;
Generating the DDL for Creating an Index
DBAs often need to recreate an index or create new indexes in a different environment, such as a pre-
production database. There are actually a couple of ways you can do this (if you're not using a third-
party tool such as TOAD or even Oracle's SQL Developer, which can get you the same information
without having to run any script whatsoever).
Generating the DDL for a simple index might seem somewhat of a trivial task. However, if you
consider the fact that many database uses partitioned indexes, and that the these indexes may have a
