I am having a tough time as my query is getting aborted due to abnormal PJI values.
The query in question tries to create a volatile table from another volatile table using a subquery
basically its a create table using a subquery , both these table have the same set of PI defined
The select is kind of complex and invloves numerous case staments and about 20-30 columns and derived columns are calculated
however it all comes from a single voaltile table in the from clause there are no joins invovled at all.
I checked on the explain plan from the view point even there it doesnt show any joins let alone product joins but i am still getting
PJI in excess of 30-40 .
Additionaly i tried collecting stats on the base volatile table reduced the volume of data invovled still it shows high PJI
I am failing to understand why its showing a high PJI index when there are joins invovled at all
Does it have to do something with the select ? and why is that the explain is clean ?
Please guide me in overcoming this
PJI is CPU intensive operations indicator.
As you say, you have a lot of CASEs, which may be at the botom of your problems.
Complex operations may also be CPU intensive and show as high PJI.
Others causes may be:
Duplicate row checks in SET tables,
Many duplicate row hashes in NUPIs.
Agregations, functions (SUBSTR, INDEX...) arithmetic operations...