My Oracle Support Banner

Count(*) Over () As APEX$TOTAL_ROW_COUNT Is Appearing In The SQL Of Classic Report (Doc ID 2817821.1)

Last updated on AUGUST 04, 2022

Applies to:

Oracle Application Express (APEX) - Version 20.2 and later
Information in this document applies to any platform.


In Apex 20.2 , When doing a performance tuning of an APEX application page in classic report component with faceted search.Issue is when faceted search is linked to classic report the SQL APEX generates this piece of code is injected: count(*) over () as APEX$TOTAL_ROW_COUNT which is, even with all the optimizations, making database to do unnecessary steps.

Turning off disapplying counts / total row numbers in faceted search doesn't not change this behavior - this piece is still getting injected. The goal of tuning is to make first N rows to appear the fastest way possible, but this APEX$TOTAL_ROW_COUNT spoils everything and to me it's purely unneeded extra piece of data which is nowhere used - neither in Classic Report(we turned off pagination there), nor in Faceted Search. Expected behavior is it shouldn't even been generated, when the counts were switched off from displaying on faceted search.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.