CODEWITHSUNDEEP
sqlteradata
moons
moons

Reputation: 209

Is there a way to rewrite the SQL query efficiently?

I know, this is probably a very basic and simple SQL question, but I'm looking for a way to improve the SQL statement below. 

SELECT 
    ANY_COLUMN
FROM 
    MY_TABLE
WHERE 
    ATTR1='ABC'
AND 
    ATR2 = 'DEF'
AND 
    CAST(STRTOK((
        SELECT 
            ANY_COLUMN 
        FROM 
            MY_TABLE
        WHERE 
            ATTR1='ABC'
        AND 
            ATR2 ='DEF'
        ), '.', 4) AS DATE FORMAT 'YYYYMMDD') 
    > 
    CAST(STRTOK('sometext.sometext.20100101.txt', '.', 4) AS DATE FORMAT 'YYYYMMDD');

The query gives me the result, I'm looking for, but I doubt, that it is very performant. As you can see, I'm filterting twice for the same attributes on the same table.

Is there a way to improve the performance of this query? Maybe with a self join?

Upvotes: 0

Views: 75

Answers (1)

Radim Bača
Radim Bača

Reputation: 10701

I believe that subquery is not necessary

SELECT 
    ANY_COLUMN
FROM 
    MY_TABLE
WHERE 
    ATTR1='ABC'
AND 
    ATR2 = 'DEF'
AND 
    TO_NUMBER(STRTOK(ANY_COLUMN, '.', 4)) > 
    TO_NUMBER(STRTOK('sometext.sometext.20100101.txt', '.', 4));

Moreover, to process the query efficiently index on (attr1, atr2) is needed:

create index ix_mytable_attr1_atr2(attr1, atr2) on MY_TABLE

Upvotes: 2

Related Questions