Performance Issue with IN operator in SQL

Question

How to fix the performance issue with the in operator.

SELECT * 
FROM Q1 
WHERE Q1.sample IN (SELECT COLUMN_VALUE 
                    FROM TABLE(APEX_STRING.SPLIT(NVL(:P34_ACC,Q1.sample ),',')))

Why should I "tag my RDBMS"? - please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. — marc_s
– marc_s, Commented Jun 3, 2021 at 5:19

Thorsten Kettner · Accepted Answer · 2021-06-03 05:44:21Z

2

You are using a function to generate rows in your IN clause. Oracle cannot know whether this will result in say, one or two rows or five billion rows. Thus it can only come up with a general execution plan, that may or may not be good.

You can hint Oracle as to the number of expected rows using the undocumented CARDINALITY hint:

SELECT *
FROM Q1 
WHERE Q1.sample IN 
(
  SELECT /*+cardinality(t, 10)*/ COLUMN_VALUE 
  FROM TABLE( APEX_STRING.SPLIT(NVL(:P34_ACC,Q1.sample ),',')) t
);

For the case of few rows, you should have an index on Q1.sample:

CREATE INDEX idx ON q1(sample);

answered Jun 3, 2021 at 5:44

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Performance Issue with IN operator in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related