conn / as sysdba
DROP TABLE partial_index_demo;
CL SCR

CREATE TABLE partial_index_demo 
(	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 (UPPER(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-MAR-1999','DD-MON-YYYY')) INDEXING ON,
		PARTITION ord_p2 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')) INDEXING OFF,
		PARTITION ord_p3 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')) INDEXING ON,
		PARTITION ord_p4 VALUES LESS THAN (TO_DATE('01-MAR-2000','DD-MON-YYYY')),
		PARTITION ord_p5 VALUES LESS THAN (TO_DATE('01-MAR-2010','DD-MON-YYYY'))
	);

SELECT	table_name,def_indexing 
FROM 	user_part_tables 
WHERE 	table_name = 'PARTIAL_INDEX_DEMO';

SELECT	partition_name,indexing 
FROM 	user_tab_partitions 
WHERE 	table_name = 'PARTIAL_INDEX_DEMO' 
ORDER BY partition_position;

ALTER TABLE partial_index_demo MODIFY PARTITION ord_p5 INDEXING ON;

SELECT	partition_name,indexing 
FROM 	user_tab_partitions 
WHERE 	table_name = 'PARTIAL_INDEX_DEMO' 
ORDER BY partition_position;

CREATE INDEX partial_index_demo_cust_ix ON partial_index_demo(customer_id) LOCAL INDEXING PARTIAL;

SELECT	partition_name,status 
FROM 	user_ind_partitions 
WHERE 	index_name = 'PARTIAL_INDEX_DEMO_CUST_IX' 
ORDER BY partition_position;
