Some Oracle Query Optimization Tips

Tip 1: Using REGEXP_LIKE (Oracle 11g+)

Instead of chaining multiple LIKE statements with OR:

Before (Inefficient):

SELECT * FROM table_name 
WHERE col LIKE '%x%' OR col LIKE '%y%' OR col LIKE '%z%';

After (Optimized):

SELECT * FROM table_name 
WHERE REGEXP_LIKE(col, 'x|y|z');

Tip 2: Generating In-Memory Row Sets

Instead of chaining multiple UNION ALL statements:

Before (Verbose):

SELECT 'abc' FROM dual
UNION ALL
SELECT 'bcd' FROM dual
UNION ALL
SELECT 'xyz' FROM dual;

After (Compact):

SELECT COLUMN_VALUE 
FROM TABLE(sys.odcivarchar2tolist('abc', 'bcd', 'xyz'));