Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Query suggestion

Ken18Jan 27 2020 — edited Feb 12 2020

I am looking for suggestions on the below queries, which would be the best option in view of performance.

Please share your inputs and brief analysis.

Query1 and Query 2 timings are same, difference is couple of ms.
Query1 has less cardinality as compared to Query2.

For Query2, can we try the option to have a table to store the data ==> Create another table which will store the data ?

Which would be best option to use among Query 1 and Quer 2.

Also Recently, I have read about UDF Pragma optimization method in Oracle Database 12c.

I'm very interested in how exactly it works. I've only found very short description in the Oracle documentation..

Will this be useful here in this scenario, Can someone explain how the internal mechanism of UDF Pragma works? if you have used it, familiar with it already.

ENV : Oracle 12.2.0.1 (CDB/PDB) on RHEL 7

-- Query1_sp

Excerpt of explain plan --