Please check your basic syntax and expected behaviour.

Today a colleague of mine found some indexes on a system he was working on that didn't look quite right. He checked out the definition and found something a little surprising!

create index my_index on my_table ('my_column');

The indexes had been defined incorrectly, and hence have never been utilised. For those minds that aren't working on Fridays, the index column should not be defined in quotes.

This issue can be simply demonstrated with a small test. Below I create a table, populated it with a bunch of rows, create this bad version of the index and see if it's utilised.

I've trimmed some of the output for brevity, but before my two select statements, I utilised the SQL*Plus command:
SET AUTOTRACE ON EXPLAIN

10g>  drop table chk_syntax;

Table dropped.

10g>  create table chk_syntax
  2    (col1 number(18)
  3    ,col2 varchar2(200));

Table created.

10g>
10g>  insert into chk_syntax
  2  select rownum, 'col'||rownum
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

10g>
10g>  create index bad_syntax_i on chk_syntax ('col2');

Index created.

10g>
10g>  select * from chk_syntax where col2 = 'col2';

      COL1 COL2
---------- ----------------------------------------------------------------------
         2 col2

1 row selected.

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |   115 |     8   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| CHK_SYNTAX |     1 |   115 |     8   (0)| 00:00:01 |
--------------------------------------------------------------------------------

10g>
10g>  create index good_syntax_i on chk_syntax (col2);

Index created.

10g>
10g>  select * from chk_syntax where col2 = 'col2';

      COL1 COL2
---------- ----------------------------------------------------------------------
         2 col2

1 row selected.

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |   115 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CHK_SYNTAX    |     1 |   115 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | GOOD_SYNTAX_I |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("COL2"='col2')

10g>
10g>  select * from user_ind_columns where table_name = 'CHK_SYNTAX';

INDEX_NAME                     TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ -------------------
BAD_SYNTAX_I                   CHK_SYNTAX                     SYS_NC00003$
GOOD_SYNTAX_I                  CHK_SYNTAX                     COL2

2 rows selected.

10g>
10g>  select * from user_ind_expressions where table_name = 'CHK_SYNTAX';

INDEX_NAME                     TABLE_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------ -------------------
BAD_SYNTAX_I                   CHK_SYNTAX                     'col2'

1 row selected.

The bad index isn't utilised, but the good one is referenced by the optimiser.

Second to this, a look at the data dictionary view user_ind_columns shows the bad index doesn't refer to an actual column - only a generated one.

This is true of function-based indexes, which is what this bad index has done. When I looked in user_ind_expressions I see the index defined as a literal string.

This is where you normally see function-based indexes such as:
CREATED INDEX surname_fn_i ON person (UPPER(surname));

This also reminds me a common open question I ask during training sessions.

What does this return:
SELECT '6 * 6' as "Area" FROM dual;

Often when writing queries, insert statements, data definition - syntax can be valid and safely executed... but it doesn't make it right.

Please check your syntax, and confirm expected behaviour.

10g> SELECT 6 * 6 as "Area" FROM dual;

Area
----------
36

1 row selected.