CODEWITHSUNDEEP
sqloracle-databasepartitioningindexing
Yaroze
Yaroze

Reputation: 131

Oracle Partitioning and Indexes

I'm having some trouble understanding this. Maybe someone can help, please.

Someone asked me to create a table that should be partitioned as two levels:

1 - Time 2 - Location.

It should have a global inverted index by customer - This is my biggest problem. What is a global inverted index?

Here's the table structure (it has already been created):

CREATE TABLE MY_TABLE
(
TIME          DATE,
LOCATION      NUMBER,
CUSTOMER_ID   NUMBER,
CUSTOMER_NAME VARCHAR2(200)
);

(and some other columns...)

How should I do this? Thank you in advance.

Upvotes: 0

Views: 144

Answers (1)

the_slk
the_slk

Reputation: 2182

CREATE TABLE my_table
(
    a_time        DATE,
    a_location    NUMBER,
    customer_id   NUMBER,
    customer_name VARCHAR2(200)
)
PARTITION BY RANGE (a_time)
SUBPARTITION BY LIST (a_location)
(
    PARTITION p_20130101 VALUES LESS THAN (TO_DATE('2013-01-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION a_location_1_1 VALUES (1),
        SUBPARTITION a_location_1_2 VALUES (2),
        SUBPARTITION a_location_1_3 VALUES (3)
    ),
    PARTITION p_20130201 VALUES LESS THAN (TO_DATE('2013-02-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION a_location_2_1 VALUES (1),
        SUBPARTITION a_location_2_2 VALUES (2),
        SUBPARTITION a_location_2_3 VALUES (3)
    ),
    PARTITION p_20130301 VALUES LESS THAN (TO_DATE('2013-03-01', 'YYYY-MM-DD'))
    (
        SUBPARTITION a_location_3_1 VALUES (1),
        SUBPARTITION a_location_3_2 VALUES (2),
        SUBPARTITION a_location_3_3 VALUES (3)
    )
)
ENABLE ROW MOVEMENT;

Oracle inverted indexes are intended for strings and column in your table in as number (my_table_idx). I would recommend regular index.

CREATE INDEX my_table_idx ON my_table(customer_id);

Upvotes: 1

Related Questions