oracle datetime field indexing

Question

Hi,

How do we perform indexing on a datetime field in oracle. We should be able to search for a specific year

Thanks

Answer 1

Add an index which is not bound to a column, but an expression that extract the year from that column:

create index sample_index on YourTable (extract(year from YourDateColumn)) tablesapce YourIndexSpace;

When you query the table using that expression, Oracle will use the index.

Answer 2

To create an index in Oracle, use:

CREATE INDEX your_index_name ON your_table_name(your_column_name)

For more info about Oracle index creation, read this link.

Correction & Clarification
If you use a function to isolate a component of a date (IE: EXTRACT, or TRUNC), an index on the column will not help. But an index will help if you provide a date range:

WHERE your_date_column BETWEEN TO_DATE('2010-01-01', 'YYYY-MM-DD')
                                            AND TO_DATE('2010-12-31', 'YYYY-MM-DD')

You can however create function based indexes in Oracle:

CREATE INDEX your_index_name 
    ON your_table_name(EXTRACT(YEAR FROM your_column_name))

...which DBAs loath with a passion.

Answer 3

Hi Milee,

You can index a DATE column (which stores date and time in Oracle) directly:

CREATE INDEX ix ON table (column)

Oracle will be able to use this index directly if you build your query so as to perform a RANGE SCAN. For example, if you want to retrieve rows from 2010:

SELECT ...
  FROM table
 WHERE column >= DATE '2010-01-01'
   AND column < DATE '2011-01-01'

This index can also be used to answer queries for a specific month, day or any other range.