Re: indexes and views

In Oracle, since version 7.1 I suppose, you can create indexes based on functions or expressions. In your case:

SQL> create index i1 on t1 (to_char(c1)||'.'||to_char(c2));

Index created.

SQL> insert into t1 values (1,2);

1 row created.

SQL> insert into t1 values (3,4);

1 row created.

SQL> insert into t1 values (5,6);

1 row created.

SQL> commit;

Commit complete.

SQL> set autotrace traceonly explain;
SQL> analyze table t1 compute statistics;

Table analyzed.

SQL> select * from v1 where c3 = '1.2';

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=4)    1 0 INDEX (RANGE SCAN) OF 'I1' (NON-UNIQUE) (Cost=1 Card=1 Byt

          es=4) Received on Thu Mar 10 2005 - 12:25:57 CST