Database Reference
In-Depth Information
3.
Creating several
invisible
indexes is supported (notice the differences in every index):
SQL> CREATE
UNIQUE
INDEX i_ui ON t (n1)
INVISIBLE
;
SQL> CREATE
BITMAP
INDEX i_bi ON t (n1)
INVISIBLE
;
SQL> CREATE INDEX i_hpi ON t (n1)
INVISIBLE
2
GLOBAL PARTITION BY HASH
(n1) PARTITIONS 4;
SQL> CREATE INDEX i_rpi ON t (n1) INVISIBLE
2
GLOBAL PARTITION BY RANGE
(n1) (
3 PARTITION VALUES LESS THAN (10),
4 PARTITION VALUES LESS THAN (MAXVALUE)
5 );
4.
Switch between two indexes by making the older one invisible and the new one visible:
SQL> ALTER INDEX i_i INVISIBLE;
SQL> ALTER INDEX i_ui VISIBLE;
Partial Indexes
For performance purposes, it's sometimes
not
necessary to index all data stored in a table. That's especially true for
huge, range partitioned tables containing a long history of specific events like orders or phone calls. For example,
it might only be necessary to index the data from the last day, or from the most recent week, and to leave any older
data unindexed. Such indexes are called
partial indexes
. Using them in the right situations can save a lot of disk space
that would be unnecessarily allocated.
Even though in versions up to and including 11.2, some kind of partial indexes are supported by implementing
particular tricks, it's only from version 12.1 onward that Oracle Database provides a formal syntax to support partial
indexes. The basic idea of the syntax introduced in version 12.1 is that an indexing property specifying whether data
has to be indexed can be set at the table and at the partition level.
The following example, based on the
partial_index.sql
script, shows how to specify that indexing be disabled
for all partitions except for the one that explicitly sets the
INDEXING ON
property (obviously you can also set
INDEXING
ON
at the table level and
INDEXING OFF
for specific partitions only):
CREATE TABLE t (
id NUMBER NOT NULL,
d DATE NOT NULL,
n NUMBER NOT NULL,
pad VARCHAR2(4000) NOT NULL
)
INDEXING OFF
PARTITION BY RANGE (d) (
PARTITION t_jan_2014 VALUES LESS THAN (to_date('2014-02-01','yyyy-mm-dd')),
PARTITION t_feb_2014 VALUES LESS THAN (to_date('2014-03-01','yyyy-mm-dd')),
PARTITION t_mar_2014 VALUES LESS THAN (to_date('2014-04-01','yyyy-mm-dd')),
PARTITION t_apr_2014 VALUES LESS THAN (to_date('2014-05-01','yyyy-mm-dd')),
PARTITION t_may_2014 VALUES LESS THAN (to_date('2014-06-01','yyyy-mm-dd')),
PARTITION t_jun_2014 VALUES LESS THAN (to_date('2014-07-01','yyyy-mm-dd')),
