Check Multiple Values in WHERE Clause SQL Query REGEXP_LIKE Oracle 11g

Question

if using REGEXP_LIKE in complex query it take very long time to execute, Can you provide alternate solution for that.

get List of values from one query and should use only those values to another query where clause

LV_TRANS_TYPES_IDS  VARCHAR2(4000);


  SELECT LISTAGG(TRANSACTION_TYPE_ID, '|') WITHIN GROUP(ORDER BY 1)
    INTO LV_TRANS_TYPES_IDS
    FROM MTL_TRANSACTION_TYPES
   WHERE TRANSACTION_TYPE_NAME IN
         (SELECT MEANING
            FROM FND_LOOKUP_VALUES_VL
           WHERE LOOKUP_TYPE = 'LOOKUPNAME');

-- it will return multiple values like 2|38|45|60

 LV_TRANS_TYPES_IDS := '^(' || LV_TRANS_TYPES_IDS || ')$';

-- assignment ^(2|38|45|60)$ passing this values to the below query

 SELECT COUNT(*)
    INTO LN
    FROM MTL_TRANSACTION_TYPES
   WHERE 1 = 1
     AND REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS);

first execute the query take the output and use those output for second , third queries input. like (select f1,f2 from table1 where REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS) union all union all select f1,f2 from table3 where REGEXP_LIKE(TRANSACTION_TYPE_ID, LV_TRANS_TYPES_IDS))

Answer 1

If you want to get results faster, then dont use LISTAGG and REGEXP_LIKE at all.

Just:

SELECT COUNT(*)
INTO LN
FROM MTL_TRANSACTION_TYPES
WHERE TRANSACTION_TYPE_NAME IN
         (SELECT MEANING
            FROM FND_LOOKUP_VALUES_VL
           WHERE LOOKUP_TYPE = 'LOOKUPNAME');

Answer 2

SELECT COUNT(*)
INTO LN
FROM MTL_TRANSACTION_TYPES mtt
JOIN
FND_LOOKUP_VALUES_VL flvv
ON mtt.TRANSACTION_TYPE_NAME = flvv.MEANING
AND flvv.LOOKUP_TYPE = 'LOOKUPNAME';