create index deptno_idx on emp(to_number(deptno));

''''
from sales s, departments d
where d.deptno=(case when sal_type=1 then sal_dept else agent_no end)
and d.location='BUSAN'
......

create index deptno_idx on sales
(CASE    WHEN sal_type=1    then sal_dept    else agent_no end);

CREATE INDEX ename_upper_ix ON employees (UPPER(ename));
CREATE INDEX ename_upper_ix ON employees (UPPER(REPLACE(ename, ' '));

CREATE INDEX end_date_idx ON account_history(NVL(end_date,'99991231'));

....
where :input_date between start_date and nvl(end_date,'99991231')

create index call_number_idx on call_date
(decode(substr(call_number,1,3),'018','','016')||call_number);

create index order_amount_idx on order_items
(ITEM_CD, (order_price - nvl(order_discount,0)) * order_count));

select /*+    index_desc(x order_amount_idx) */*
from order_items x
where item_cd   = :b1
and rownum <=100;

CREATE INDEX SAL_AMOUNT_IDX ON SALES (LAST_DAY(SAL_DATE),SAL_AMOUNT);

CREATE INDEX PRICE_IDX ON SALES (ROUND(SAL_AMOUNT/SAL_QUANTITY));

CREATE INDEX TERM_IDX ON ACTIVITIES (expire_date - start_date);

CREATE UNIQUE INDEX official_id_idx on customers
    (CASE WHEN cust_type=1    THEN     resident_id    ELSE business_id END);

SELECT * FROM customers
WHERE (CASE WHEN CUST_TYPE=1 THEN resident_id ELSE business_id END) = :B1;

CREATE UNIQUE INDEX CONTRACT_IDX ON INSURANCE
    (CASE WHEN INS_TYPE='A01' THEN CUSTOMER_ID ELSE NULL END,
     CASE WHEN INS_TYPE ='A01' THEN INS_TYPE    ELSE NULL END);

'A01' 만 유일성 채크 나머지는 NULL

CREATE INDEX order_delivery_idx1 ON order_delivery
( order_dept,
    CASE WHEN ORD_TYPE=1 THEN delivery_date ELSE SHIPPING_DATE END,
    ITEM_TYPE);