Databases Reference
In-Depth Information
will be represented in
DBA_SEGMENTS
. In other words, for subpartitioned indexes, the subpartition_name
will appear as the column
PARTITION_NAME
in
DBA_SEGMENTS
. In the following example, you want to get the
index subpartitions for your
BILLING_FACT
table that are greater than 8GB in size. You perform a
subquery against
USER_IND_SUBPARTITIONS
, and this demonstrates that the
PARTITION_NAME
column in
DBA_SEGMENTS
actually represents the
SUBPARTITION_NAME
in
USER_IND_SUBPARTITIONS
.
1 select segment_name, partition_name, round(bytes/1048576) meg
2 from dba_segments
3 where (segment_name, partition_name) in
4 (select index_name, subpartition_name
5 from user_ind_subpartitions
6 where index_name in
7 (select index_name from user_indexes
8 where table_name = 'BILLING_FACT'))
9 and bytes > 1048576*8192
10* order by 3 desc;
SEGMENT_NAME PARTITION_NAME MEG
------------------------------ ------------------------------ ----------
BILLING_FACT_PK BILLING_FACT11_08P_EAST 9687
BILLING_FACT_PK BILLING_FACT11_09P_EAST 9591
BILLING_FACT_PK BILLING_FACT11_07P_EAST 8951
For partitioned indexes, the partition names between
DBA_SEGMENTS
and
USER_IND_PARTITIONS
will match.
Summary
As a guideline, partitioned indexes should be used on partitioned tables. More specifically, locally
partitioned indexes should be used whenever possible. Index maintenance on partitioned tables is far
easier when using locally partitioned indexes, as noted in Table 6-1. There are, of course, exceptions
based on application requirements. For instance, application requirements may dictate that the
partitioning column(s) can't be part of a unique index that becomes the primary key. In cases such as
these, you may simply have to implement a non-partitioned index. Alternatively, query performance
using a globally partitioned index may be beneficial to warrant their use in certain circumstances.
As with many aspects of software development, the trade-offs between the different types of indexes
need to be analyzed before making a final determination. During application and database design, it is
important to understand all the ramifications and impacts of using each type of index. This includes
weighing the following factors:
•
Data model requirements
•
Data access requirements
•
Data volume
•
Time needed to perform index maintenance operations
Available maintenance windows
Once you take all the aforementioned items into consideration, you can make an informed decision as
to what is best for your application.
•
