Tuesday, 15 March 2011

oracle - SQL - Is using 2 Dates in my WHERE clause redundant? -


I have a query (which I run on an Oracle DB) that looks like this: < Pre> SELECT Column1, Column2, Column3 from MyDatabase.MyTableNameWhere (DELETED = 0) AND (DATESEEN> TO_DATE ('20 / 03/2013 12:00:00 ',' DD / MM / YYYY H.224: MI: SS ')) and (DATERECORDED> = TO_DATE ('20 / 06/2013 06:00:00', 'DD / MM / YYYYHH 24: MI: SS')) and (Detrocted & lt; TO_DATE ('20 / 06/2013 12:00:00, 'DD / MM / YYYYHH 24: MI: SS'))

query dynamic Form Dynamic values ​​are as follows:

Where DATESEEN is the second row of 90 days ago.
The third line of the line where the query last ran (Usually 12 hours ago)
Where is the time in the last line of this time.

There is no index here, but on date I can not put any index on DATERECORDED for reasons beyond my control.

Is my "Date" line in my WHERE section completely empty? Since Dates is always going to date prior to DATRCRDAD, will it be disregarded? The execution plan shows very little: execution plan

Thank you in advance!

You do not have an index on daycard code . To satisfy the section, the Oracle must scan a full table, read all the records.

An index can stop a full table scan - either an index on an index deleted or date system (because you have The index has been rejected on the date .).

There is no index on these two areas, then the segment is redundant. If so, it can significantly reduce I / O queries. There is not much of an index on Detecorded, but it can be too much - especially if you have 10 or 20 years of data.

However, Oracle is free to use such an index based on collected data about the decision table.

Another idea will be divided. If the table is divided by the date SIN , then it is allowed to select the correct partition (s) to complete the query in the section of the section. It will not be possible to use it.

No comments:

Post a Comment