Post reply

index usage help

  • July 2, 2019 at 7:01 am

    #3659200

    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 5 years, 4 months ago by  vsamantha35. Reason: typo

  • July 2, 2019 at 8:31 am

    #3659209

    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

  • July 2, 2019 at 9:01 am

    #3659216

    Thanks Andrew.

  • July 2, 2019 at 11:19 am

    #3659223

    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

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • July 2, 2019 at 1:07 pm

    #3659270

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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