sqlplus test/test@pdb11
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
PARTITION BY RANGE (ORDER_TOTAL)
(PARTITION ord_p1 VALUES LESS THAN (200),
PARTITION ord_p2 VALUES LESS THAN (300),
PARTITION ord_p3 VALUES LESS THAN (400),
PARTITION ord_p4 VALUES LESS THAN (500),
PARTITION ord_p5 VALUES LESS THAN (maxvalue))
INDEXING PARTIAL;
CREATE INDEX ORDERS_ORDER_TOTAL_LIDX ON ORDERS (ORDER_DATE,order_id)
LOCAL
INDEXING PARTIAL;
select TABLE_NAME,PARTITION_NAME,INDEXING from user_tab_partitions where TABLE_NAME like 'ORDERS%';
select INDEX_NAME,PARTITION_NAME,STATUS from user_ind_partitions where INDEX_NAME like 'ORDERS%';
