Search code examples
oraclequery-optimization

Oracle 19: Why IN gets converted to Exist in explain plan and any suggestions around it

Please see image below: enter image description here

As per that the IN query got converted to Exists in explain plan. Any reason for that? does it mean Oracle automatically converts IN to Exists?

Also any suggestion to reduce the cost? this statement is a part of a SP and it receives ~ separated string ('123') for example (63278~63282~63285~63288~63291~63296~63299~63302~63305~63308~63311~63314~63319~63322~63325~63329~63332~63253~63256~63260~63264~63267~63272~63275~63279~63283~63286~63289~63292~63297~63300~63303~63306~63309~63312~63315~63320~63323~63326~63330~63333~63269~63258~63277~63294~63317~63262~63270~63281~63295~63318~63328~63254~63257~63261~63265~63268~63273~63276~63280~63284~63287~63290~63293~63298~63301~63304~63307~63310~63313~63316~63321~63324~63327~63331~63334) in query. It takes around 10 to 15 mins to execute.

How can we generate explain plan for entire stored proc? We are using Oracle 19.

Thank you in advance.

Solution

  • Since you are doing the job in a PL/SQL procedure you could create (out of the procedure) a GLOBAL TEMPORARY TABLE with DELETE ON COMMIT, in the procedure you INSERT in this table the result of the sub select with the CONNECT BY, then your replace the SELECT ... CONNECT BY by a SELECT in the temporary table. The temp table will be emptied at the end of the procedure and this method is session safe. And you have benefit of the index and probably a better plan. You could also compare the UPDATE with 2 ones: splitting the OR condition on 2 statements.