Database Reference
In-Depth Information
, SALES_ID NUMBER);
CREATE INDEX XSALESCLU ON CLUSTER SALESCLU;
CREATE TABLE JOIN_SALESCLU CLUSTER SALESCLU
(CONTINENT_ID, COUNTRY_ID, CUSTOMER_ID, SALES_ID)
AS SELECT S.CONTINENT_ID AS CONTINENT_ID
, S.COUNTRY_ID AS COUNTRY_ID
, S.CUSTOMER_ID AS CUSTOMER_ID
, S.SALES_ID AS SALES_ID
FROM CONTINENT CT, COUNTRY CY, CUSTOMER C, SALES S
WHERE CT.CONTINENT_ID = S.CONTINENT_ID
AND CY.COUNTRY_ID = S.COUNTRY_ID
AND C.CUSTOMER_ID = S.CUSTOMER_ID;
Note:
Note how not all columns in all tables are added into the cluster from
the join. A cluster is intended to physically group the most frequently
accessed data and sorted orders.
That's enough about clusters as far as Oracle SQL is concerned.
21.3
Metadata Views
This section simply describes metadata views applicable to indexes and
clusters. Chapter 19 describes the basis and detail of Oracle Database meta-
data views.
USER_INDEXES
. Structure of indexes.
USER_IND_COLUMNS
. Column structure of indexes.
USER_IND_EXPRESSIONS
. Contains function-based index
expressions.
USER_JOIN_IND_COLUMNS
. Join indexes such as bitmap join
indexes.
USER_PART_INDEXES
. Index information at the partition level.
USER_IND_PARTITIONS
. Partition-level indexing details.
USER_IND_SUBPARTITIONS
. Subpartition-level indexing
details.
USER_CLUSTERS
. Structure of constraints such as who owns it, its
type, the table it is attached to, and states, among other details.
