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

Hello,

We are using Oracle Versions 8.1.7.4, 10.2.0.2 on Windoes 2000/2003.

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.

Sample script given below.

CREATE TABLE TblMovement
(

FID NUMBER(38),
columns.....,
MovementDate DATE,
Movementtime DATE,
STORE VARCHAR2(30)
);

CREATE INDEX X1TblMovement ON TblMovement
(FID);
CREATE INDEX X2TblMovement ON TblMovement
(MovementDate, Movementtime);

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'; 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; Thanks & Regards,

Shailesh Received on Wed Nov 01 2006 - 04:22:24 CST