Databases Reference
In-Depth Information
If you then insert a row in the table that is outside the existing partition boundaries, a new local
index partition will be generated in the desired tablespace.
SQL> insert into testtab values (1,'2009-05-01');
SQL> select partition_name, tablespace_name from user_ind_partitions;
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P74 TEST08INDEX_S
TEST307_11P TEST08INDEX_S
TEST307_12P TEST08INDEX_S
SYS_P75 TEST08INDEX_S
SYS_P76 TEST09INDEX_S
Making Older Data Read-Only
Especially within the data warehouse environment, it is important to be able to make older data read-
only because it speeds up query time. It can also assist in reducing the backup times on very large
databases (VLDBs), as data and indexes that reside in read-only tablespaces only have to be backed up
occasionally.
If you are using date- or timestamp-based partitioning on your tables and indexes, it is important to
segregate both the data and index partitions into tablespaces based on that date-based interval. Once
data becomes static and no longer updated, you can then make those tablespaces read-only. So, during
physical database design, the DBA needs to consider whether making data read-only is a necessity for
the environment based on the size of the database. If so, it is important to isolate local data and index
partitions into date-based tablespaces.
Reporting on Partitioned Indexes
There are many things you can glean from the data dictionary regarding index portions, including the
following:
•
Partition names
•
Type of index
•
Status of index partitions (need to query appropriate view)
•
Size of the index partitions
