Problem: The Oracle DB uses some optimization and doesn't execute WITH
component which isn't used in the final query. So no execution of unused queries.
Question:
Is it possible to disable Oracle's optimization and execute all queries in WITH
clause? Are there any special hints for that?
Details:
I want to use Oracle Database Context mechanism inside my queries executed from Java program.
I decided to use Database session-based application context initialized locally
context type.
So in one database session I can set some context variables and only in that session it will be visible.
I have an idea to execute function (which sets necessary context variables) just before real SQL query. So the best place will be bind both things together. It would be the best option for me.
Minimal example:
If we execute (or look at explain plan) this query, we notice that EXEC_CTX
subquery is NOT executed.
Only the final query from real_table
is actually executed by Oracle.
Can we force somehow Oracle to execute both of them (whole WITH
query)?
WITH EXEC_CTX AS (
select set_my_ctx(42) from dual
)
SELECT *
FROM real_table rt
WHERE rt.col_x = SYS_CONTEXT('CTX_NAME', 'PARAM_ID')
;
I'm using Oracle 12c database.