Databases Reference
In-Depth Information
If you really intend the database to use parallelism when dealing with your index, you're fine.
However, if you don't intend the database to use parallelism during query execution, disable parallelism
on an index after any parallel maintenance operation such as a
create index
or a
alter index rebuild
operation, as shown here:
SQL> alter index text_idx1 noparallel;
Index altered.
SQL>
If you forget to put the degree of parallelism of an index back to its default value of 1 (no
parallelism), you may get bit you when you least expect it, especially in an OLTP application. All of a
sudden you may be confronted with heavy contention and a slowdown in processing due to the totally
unintended use of parallelism.
When you encounter this situation, a check of the tables involved in the query may show that the
degree of parallelism is at the default of 1. However, when you check the parallelism of all the
indexes
involved in the query, you'll find the culprit: the index you've created or rebuilt with a parallel option has
its parallel degree set to greater than 1. Even the presence of a single object in a query with a parallel
degree greater than 1 means that the optimizer may choose to parallelize all operations on the query. So
be careful. Don't leave a degree of parallelism set unless you mean for it to be set.
Avoiding Redo Generation During Index Creation
You can achieve dramatic reductions in index creation times by choosing not to write the index creation
entries to the redo log. Since you can always rebuild an index with the table data, you're not risking
anything by creating indexes with the
nologging
option. The
nologging
option is especially helpful when
creating very large indexes during short windows of time.
Simply specify the
nologging
option when creating an index to speed up the index creation process,
as shown here:
SQL> create index hr.emp_name_idx on hr.employees (last_name, first_name)
2 nologging
3* tablespace example
SQL> /
Index created.
SQL>
When used for creating large indexes, the
nologging
option not only dramatically increase performance,
but also saves space by not filling up several redo log files.
Using Larger Block Sizes
According to Oracle (MOSC Note 46757.1), a large block size can save disk space for indexes. As an
example, Oracle states that by moving from a 2KB block size to an 8KB block size, you can save about 4
percent in data storage. In order to create an index with a non-standard block size, first create a
tablespace with the block size you need. For example, if you want to create the index with a block size of
