Menu
How do we perform indexing on a datetime field in oracle. We should be able to search for a specific year
Thanks
305
TIMESTAMP Datatype It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE datatype.
In general, you should create an index on a column in any of the following situations: The column is queried frequently. A referential integrity constraint exists on the column. A UNIQUE key integrity constraint exists on the column.
TIMESTAMP is the same as DATE , except it has added fractional seconds precision. The biggest difference: DATE is accurate to the second and doesn't have fractional seconds. TIMESTAMP has fractional seconds.
Introduction to Oracle TIMESTAMP data type The fractional_seconds_precision specifies the number of digits in the fractional part of the SECOND field. It ranges from 0 to 9, meaning that you can use the TIMESTAMP data type to store up to nanosecond. If you omit the fractional_seconds_precision , it defaults to 6.
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.
128
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
