Forum Stats

  • 3,769,230 Users
  • 2,252,933 Discussions
  • 7,874,951 Comments

Discussions

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

User_VEBWA
User_VEBWA Member Posts: 3 Green Ribbon

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"



Tagged:

Answers

  • TexasApexDeveloper
    TexasApexDeveloper Member Posts: 7,813 Gold Crown

    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

    AndyH
  • User_VEBWA
    User_VEBWA Member Posts: 3 Green Ribbon
    edited Aug 18, 2021 9:54PM

    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
    InoL Member Posts: 9,341 Gold Crown

    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.

    Sasha Gomanuke
  • User_VEBWA
    User_VEBWA Member Posts: 3 Green Ribbon

    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