CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ] index
ON { cluster_index_clause
| table_index_clause
| bitmap_join_index_clause
}
[ UNUSABLE ] ;
example
CREATE INDEX ord_customer_ix_demo ON orders (order_mode) NOSORT NOLOGGING;
CREATE INDEX idx_personnel ON CLUSTER personnel;
CREATE INDEX area_index ON xwarehouses e (EXTRACTVALUE(VALUE(e),'/Warehouse/Area'))
CREATE INDEX upper_ix ON employees (UPPER(last_name));
CREATE INDEX income_ix ON employees(salary + (salary*commission_pct));
CREATE INDEX src_idx ON print_media(text_length(ad_sourcetext));
CREATE TYPE rectangle AS OBJECT
( length NUMBER,
width NUMBER,
MEMBER FUNCTION area RETURN NUMBER DETERMINISTIC
);
CREATE OR REPLACE TYPE BODY rectangle AS
MEMBER FUNCTION area RETURN NUMBER IS
BEGIN
RETURN (length*width);
END;
END;
CREATE TABLE rect_tab OF rectangle;
CREATE INDEX area_idx ON rect_tab x (x.area());
CREATE UNIQUE INDEX promo_ix ON orders
(CASE WHEN promotion_id =2 THEN customer_id ELSE NULL END,
CASE WHEN promotion_id = 2 THEN promotion_id ELSE NULL END);
CREATE INDEX cost_ix ON sales (amount_sold)
GLOBAL PARTITION BY RANGE (amount_sold)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2500),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
CREATE INDEX cust_last_name_ix ON customers (cust_last_name)
GLOBAL PARTITION BY HASH (cust_last_name)
PARTITIONS 4;
CREATE INDEX prod_idx ON hash_products(category_id) LOCAL
STORE IN (tbs_01, tbs_02);
CREATE UNIQUE INDEX nested_tab_ix ON textdocs_nestedtab(NESTED_TABLE_ID, document_typ);
CREATE INDEX salary_i ON books (TREAT(author AS employee_t).salary);