Can you tell me if the query reruns at the database level when you return to the page every time, or does it just redisplay the current page's data from cache or a temporary table;e, collection, etc?As per my understanding, in Oracle, when you run the same query again and again, then it won't parse the query every time, however Oracle won't cache the SQL results by default. So in theory, it fetches the results every time. But if same data blocks are available in memory, then it may fetch from memory.
IndexesYes, you can't create indexes on all columns, but you should create indexes on some columns which you know users will user for filtering regularly.
I often also cache the resultset in a collection for faster IR pagination when the base query is slow, and the resultsets are no more than a few thousand rows. I've also created some very effective partial-refresh classic reports, utilizing search-as-you-type auto-complete, and dynamic queries constructed based on the specific filters used, that were more usable and much better performing than IR reports, so dont discount them completely.
SELECT apxws_row_pk, "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO", COUNT (*) OVER () AS apxws_row_cnt FROM (SELECT * FROM (SELECT b.ROWID apxws_row_pk, b.* FROM (SELECT * FROM (SELECT "EMPNO", "ENAME", "JOB", "MGR", "HIREDATE", "SAL", "COMM", "DEPTNO" FROM "EMP")) b) r WHERE ( ( INSTR (UPPER ("EMPNO"), UPPER (:APXWS_SEARCH_STRING_1)) > 0 OR INSTR (UPPER ("ENAME"), UPPER (:APXWS_SEARCH_STRING_1)) > 0 OR INSTR (UPPER ("JOB"), UPPER (:APXWS_SEARCH_STRING_1)) > 0 OR INSTR (UPPER ("MGR"), UPPER (:APXWS_SEARCH_STRING_1)) > 0 OR INSTR (UPPER ("SAL"), UPPER (:APXWS_SEARCH_STRING_1)) > 0 OR INSTR (UPPER ("COMM"), UPPER (:APXWS_SEARCH_STRING_1)) > 0 OR INSTR (UPPER ("DEPTNO"), UPPER (:APXWS_SEARCH_STRING_1)) > 0))) r WHERE ROWNUM <= TO_NUMBER (:APXWS_MAX_ROW_CNT) ORDER BY "ENAME", apxws_row_pk
I am also considering having the users review, say a 10 column report, and pick the 2-3 columns they generally filter by, then add them as page items (dropdowns, etc), so that I can refer to them directly, as part of report (resulting inline) sql. Your answer below indicates that shouldnt make any difference, perhaps I need to write a sample query both ways in Toad and review the explain plans.Don't forget that users can save reports if they find that they are running the same or similar reports all the time.
I still would like to know what is actually happening when the user "applies" a new filter, does it rerun the entire query at the database every time.Unfortunately, every time you apply a new filter, the query gets executed. (perhaps an enhancement request couuld be made to allow for multiple filters to be applied in one action) This is because of the architecture of Apex. Apex executes in the database. At its most basic level, Apex is a database application made up of all the usual database objects; users, tables, indexes, packages, procedures, functions, etc. These use Oracle's web application architecture to serve results to a request by a user through a browser as HTML. This means that when a user applies a new filter and submits the request, the IR engine, modifies the query, re-executes it and servers the resulting HTML back to the browser.