In the session monitor in the SQL text query is set up in a different way

User_VEBWA

help with query in oracle, in the session monitor in the sql text query is set up in a different way, for example I have the following query that is loaded in APEX in the After Header:

SELECT a.emp_descripcion, a.emp_id
  FROM cbe.employee a
 WHERE a.emp_status = 0
   AND a.rol_id  = 58  
   AND :f_ses_id IN (677,457,1124)

But, in the session monitor from PLSQL, in the SQL text it shows me that the query is built in a different way:

"select * from(select a.*,row_number() over (order by null) apx$rownum from(select i.*, count(*) over () as APEX$TOTAL_ROW_COUNT
 from (select *
from ((select /*+ qb_name(apex$inner) */* from (SELECT a.emp_descripcion, a.emp_id
  FROM cbe.employee a
 WHERE a.emp_status = 0
   AND a.rol_id  = 58  
   AND :f_ses_id IN (677,457,1124)
) d
 )) i 
) i where 1=1 
order by 1 asc nulls last
)a
)where apx$rownum<=:p$_max_rows"

TexasApexDeveloper

APEX is wrapping your sql with code to allow processing in the application.. Is it used in a report or grid?

Thank you!

Tony Miller

White Rock, NM

User_VEBWA

Dear Tony, thanks for your answer,

It is a classic report,

We failed to mention that this issue arises after an upgrade of Oracle and APex from 12c, 5.1 to 19c and 20 respectively.

InoL

We failed to mention that this issue arises after an upgrade of Oracle and APex from 12c, 5.1 to 19c and 20

Why is that an issue? APEX has always done some query re-write. How exactly depends on the APEX and database version.

E.g. APEX has to determine X, Y and Z in "rows X to Y of Z". APEX has to determine that no more than p$_max_rows are returned. When using columns with an LOV lookup, the LOV query has to be merged into the query.

User_VEBWA

Thanks for your answers, I understand that internally APEX executes these querys in this way, however, we are in a process of migration of the APEX version and we saw that APEX is building the querys in this way, we are concerned about the "ORDER BY NULL" that is It is there because it could be the cause of the performance problems that we are currently having, perhaps it is losing some reference to execute that "ORDER BY NULL". Thank you very much