CREATE INDEX

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);