12cR1からパーティションインデックスに対して実装
CREATE TABLE orders (
order_id NUMBER(12),
order_date DATE CONSTRAINT order_date_nn NOT NULL,
order_mode VARCHAR2(8),
customer_id NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
order_status NUMBER(2),
order_total NUMBER(8,2),
sales_rep_id NUMBER(6),
promotion_id NUMBER(6),
CONSTRAINT order_mode_lov CHECK (order_mode in ('direct','online')),
CONSTRAINT order_total_min CHECK (order_total >= 0))
INDEXING OFF
PARTITION BY RANGE (ORDER_DATE)
(PARTITION ord_p1 VALUES LESS THAN (TO_DATE('01-01-1999','DD-MM-YYYY'))
INDEXING ON,
PARTITION ord_p2 VALUES LESS THAN (TO_DATE('01-02-1999','DD-MM-YYYY'))
INDEXING OFF,
PARTITION ord_p3 VALUES LESS THAN (TO_DATE('01-03-1999','DD-MM-YYYY'))
INDEXING ON,
PARTITION ord_p4 VALUES LESS THAN (TO_DATE('01-04-2000','DD-MM-YYYY')),
PARTITION ord_p5 VALUES LESS THAN (TO_DATE('01-05-2010','DD-MM-YYYY')));
CREATE INDEX ORDERS_ORDER_TOTAL_GIDX ON ORDERS (ORDER_TOTAL)
GLOBAL INDEXING PARTIAL;
未対応の模様
create table tab2(col1 int,col2 int);
create index ind21 on tab2(col1) where col1 != 0;
create table tab22(col1 int,col2 int);
go
create index ind221 on tab22(col1) where col2 = 0;
go
