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

Shailesh wrote:
> 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

You may be able to use something like the following, which should be able to make use of the index if the MOVEMENTDATE is the leading column in the composite index - skip scans in 10g could still permit the use of the index if MOVEMENTDATE is not the leading column: SELECT
  *
FROM
  TBLMOVEMENT
WHERE
  MOVEMENTDATE < TO_DATE ('2006-08-31', 'YYYY-MM-DD')   AND STORE IS NULL
UNION ALL
SELECT
  *
FROM
  TBLMOVEMENT
WHERE
  MOVEMENTDATE = TO_DATE ('2006-08-31', 'YYYY-MM-DD')   AND (MOVEMENTTIME IS NULL
    OR TO_CHAR(MOVEMENTTIME, 'HH24:MI:SS') <= '00:00:00')   AND STORE IS NULL
ORDER BY
  MOVEMENTDATE DESC,
  MOVEMENTTIME DESC; The above makes the assumption that if you specify that a date must be <= to '2006-08-31', 'YYYY-MM-DD' at midnight, the acceptable date is either less than '2006-08-31', 'YYYY-MM-DD' or is equal to '2006-08-31', 'YYYY-MM-DD' with the time column either null or at '00:00:00'. I used TO_CHAR(MOVEMENTTIME) to strip out the date component of this column. If the date portion of this column shows '1899-12-30', 'YYYY-MM-DD', you may be able to further improve performance by changing this portion of the SQL statement to show:   AND (MOVEMENTTIME IS NULL
    OR MOVEMENTTIME <= TO_DATE('1899-12-30 00:00:00','YYYY-MM-DD HH24:MI:SS'))

You may find that it is sufficient to completely remove everything from the UNION ALL to the end of the SQL statement.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Nov 01 2006 - 06:50:28 CST