CODEWITHSUNDEEP
oracle-databaseindexing
Milee
Milee

Reputation: 223

oracle datetime field indexing

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

Thanks

Upvotes: 11

Views: 31856

Answers (4)

Nolesh
Nolesh

Reputation: 7018

Just create index like shown above. DO NOT USE TRUNC FUNCTION, because it ignores any indexes. For instance, my datecreate field has next format 03.12.2009 16:55:52 So I used to use

trunc(datecreate, 'dd')=to_date(to_char(sysdate,'dd.mm.yyyy'),'dd.mm.yyyy')

and it worked very slowly(about 5 sec)!!! Now I use next expression:

datecreate>=to_date(to_char(sysdate,'dd.mm.yyyy'),'dd.mm.yyyy') and sw.datecreate<to_date(to_char(sysdate+1,'dd.mm.yyyy'),'dd.mm.yyyy')

and my query executes in 0,01 sec

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332581

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.

Upvotes: 10

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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.

Upvotes: 3

Robert Giesecke
Robert Giesecke

Reputation: 4314

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.

Upvotes: 0

Related Questions