index usage help

  • vsamantha35

    SSChampion

    Points: 11176

    Hi All,

    Will any index will be used if we use functions on a column referred in the where, what is the best way to re-write this condition?

    SELECT.....

    WHERE to_char(lastUpdatedDate,'YYYY-MM-DD')>=@dt

    Please suggest.

    Thanks,

    Sam

    • This topic was modified 1 year, 3 months ago by  vsamantha35. Reason: typo
  • Andrew P

    SSCarpal Tunnel

    Points: 4645

    Hi vsamantha35,

    This thread appears to be a parallel conversation to this one on TechNet.

    I'm not familiar with the TO_CHAR (...) function, however a Google search has returned that this is a primary way to convert datetimes to string in Oracle SQL.

    If indexing is the same in Oracle as T-SQL (this is highly suspect!), then yes, that function wrapping around the lastUpdatedDate column will prevent SQL from doing an index seek. See the query plans for the three date comparisons below, all of which return 22 rows.

    CREATE TABLE #MyTableWithADate (
    MyID INT IDENTITY(1,1) PRIMARY KEY
    , LastUpdatedDate DATETIME
    )

    CREATE NONCLUSTERED INDEX IX_NC_LASTUPDATEDDATE ON #MyTableWithADate(LastUpdatedDate);

    INSERT INTO #MyTableWithADate (LastUpdatedDate)
    SELECT DATEADD(DAY, SYS.messages.message_id, '20100101')
    FROM SYS.MESSAGES

    DECLARE @pDate VARCHAR(20) = '2019-01-01'

    SELECT LastUpdatedDate
    FROM #MyTableWithADate
    WHERE LastUpdatedDate = @pDate

    SELECT LastUpdatedDate
    FROM #MyTableWithADate
    WHERE CONVERT(VARCHAR(20), LastUpdatedDate, 23) = @pDate

    SELECT LastUpdatedDate
    FROM #MyTableWithADate
    WHERE FORMAT(LastUpdatedDate, 'yyyy-MM-dd') = @pDate

    QueryPlans1

    To workaround this issue in T-SQL, I would convert the variable @dt to date or datetime, and compare the lastUpdatedDate column against that. If I wanted the comparison to ignore any time values (matching to all datetimes on a date),  I would convert both @dt and @lastUpdatedDate to DATE format and compare as below, which removes any TIME values from the comparison. The query plan for that is shown at the bottom.

    SELECT LastUpdatedDate
    FROM #MyTableWithADate
    WHERE CAST(LastUpdatedDate AS DATE) = CAST(@pDate AS DATE)

    QueryPlans

    In Oracle SQL though... I'm not sure.

    Andrew

  • vsamantha35

    SSChampion

    Points: 11176

    Thanks Andrew.

  • Grant Fritchey

    SSC Guru

    Points: 396716

    Just a note, the only reason that CAST is not causing a scan in this scenario is because the column is a DATE column (or maybe DATETIME), so the optimizer ignores the conversion that would normally occur between two different data types. Normally, any function on the column is going to lead to scans.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • ScottPletcher

    SSC Guru

    Points: 98567

    Yes, it would also prevent Oracle doing a seek on an index.

    If lastUpdatedDate is a date or datetime, just compare it directly to a variable of the matching type, or of date type:

    WHERE lastUpdatedDate > =@dt

    Don't use TO_CHAR() just because other code you saw used TO_CHAR().  In fact, always avoid using functions on a table column in WHERE and JOIN conditions if you can.  Thus, you should never use COALESCE nor NVL in a WHERE clause or JOIN condition, since they can be avoided.

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply