CODEWITHSUNDEEP
sqloracle-databaseconcatenationsqlperformance
davidlimpo
davidlimpo

Reputation: 73

Alternative to multiple concats in WHERE clause?

I have a huge query like this:

SELECT 'Tipification', COUNT(*) TOTAL, to_char(INITDATE,'YYYY-MM-DD')
FROM (
    SELECT (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM ) Triplet, INITDATE 
    FROM TABLE 
    WHERE USER IS NOT null 
        AND (INITDATE >= TO_DATE('2016-12-01 00:00', 'YYYY-MM-DD HH24:MI:SS')) 
        AND (INITDATE <= TO_DATE('2016-12-31 23:59', 'YYYY-MM-DD HH24:MI:SS')) 

        AND ( (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = 'aaa \ bbb \ ccc' 
            OR (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = 'aaa \ bbb \ ddd' 
            OR (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = 'xxx \ yyy \ zzz' 
            OR (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = 'qqq \ www \ eee'
            ... etc ...
)
GROUP BY to_char(INITDATE,'YYYY-MM-DD')

Where it go to more 300 string concatenations of this: (TYPE || ' \ ' || SUBTYPE || ' \ ' || PROBLEM) = '... \ ... \ ...'.

When performing this query, I'm getting some core dumps since it is too expensive to DB to execute.

Is there any alternative way to perform a query like this without having the DB running out of memory?

Upvotes: 3

Views: 324

Answers (2)

Kunal Pagare
Kunal Pagare

Reputation: 11

SAP HANA DB alternative for concate query:

select * from table_name
  WHERE (col_name1,col_name2,col_name3)
  IN (('value1','value2','value3'),.....);

Similarly, one use this in an update and delete query as well.

Upvotes: -1

JohnHC
JohnHC

Reputation: 11205

Oracle has a nice in function that allows you to put in multiple column combos.

select to_char(INITDATE,'YYYY-MM-DD'), count(*)
from table
where user is not null
AND (INITDATE >= TO_DATE('2016-12-01 00:00', 'YYYY-MM-DD HH24:MI:SS')) 
AND (INITDATE <= TO_DATE('2016-12-31 23:59', 'YYYY-MM-DD HH24:MI:SS')) 
and (type, subtype, problem) in (
    ('aaa','bbb','ccc'),
    ('aaa','bbb','ddd')) --  ... etc
group by to_char(INITDATE,'YYYY-MM-DD')

Upvotes: 7

Related Questions