Re: Composite index is not used for concatnated Date & Time fields?

Shailesh wrote on 01.11.2006 11:22:
> We have two columns in a table (MovementDate, Movementtime) with data
> type DATE, one stores DATE and another stores Time. (Database is
> migrated from Sqlbase to Oracle that is why like this else in Oracle
> date time can be stored in same field and now we have inherited some of
> these practices) Composite index exist on these columns. Problem occurs
> when in condition we extract date part from first column and time part
> from another column using concatenation. Also sometimes time part is
> null, to take care of this issue we use NVL(). All this adds to "INDEX
> NOT USED" full table scan.
>
> How can we avoid this? Is there any way, please let me know.
>

[...]

> SELECT *
> FROM TblMovement
> WHERE TO_DATE (TO_CHAR (MovementDate, 'yyyy-MM-dd') || TO_CHAR
> (Movementtime, 'hh24:mi:ss'), 'YYYY-MM-DD HH24:MI:SS') <= TO_DATE
> ('2006-08-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
> AND Store IS NULL
> ORDER BY MovementDate DESC, TO_DATE (TO_CHAR (Movementtime,
> 'HH24:MI:SS'), 'HH24:MI:SS') DESC;
The presence of to_char() and || prevents the usage of the index. You will have to create a function based index on that column:

create index idx_combined on tblmovement ( TO_DATE (TO_CHAR (MovementDate, 'yyyy-MM-dd') || TO_CHAR (Movementtime, 'hh24:mi:ss'), 'YYYY-MM-DD HH24:MI:SS') );

You will need to run explain plan to verify that this really helps.

Most probably the order by to_date(to_char()) will also result in a FTS, but I'm not sure about that (--> explain plan is your friend). It could be that an index on that expression also helps to avoid FTS

Thomas Received on Wed Nov 01 2006 - 04:44:03 CST