1

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.

jsosnowski
  • 1,560
  • 3
  • 26
  • 56
  • 3
    Why don't you just call the `set_my_ctx` function (should that be a procedure really?) explicitly from Java at the point you obtain the connection/session? Otherwise you'd have to do something similar to this on every call you make. To actually respond to the question though, would it be acceptable to add `and exists (select * from exec_ctx)` to the main query? – Alex Poole Jan 23 '19 at 13:25
  • Did you try referencing the CTE in the outer select: `select rt.* FROM real_table rt, exec_ctx WHERE...`? As it only returns a single row, it won't change the result –  Jan 23 '19 at 13:36
  • @Alex Poole Of course I can explicitly call it from Java - but still not once but before every query because Java process use connection pool (so each query may execute in different session). And in the Java process there are several parallel threads which share the connection pool - so each of them must set context value for its session (the session it is using at the moment). – jsosnowski Jan 23 '19 at 13:53
  • Using `exec_ctx` in main query slows down whole query. Subquery will be executed for every row. Join (as proposed by @a_horse_with_no_name) will join 1 row to millions rows. – jsosnowski Jan 23 '19 at 13:55
  • An explicit call still seems simpler and more manageable to me (even with the overhead of an extra call as you grab the connection from the pool) than modifying all the real queries. And for the subquery now being evaluated too often, is the function deterministic - and if so has it been declared as such? – Alex Poole Jan 23 '19 at 14:52
  • @AlexPoole I can add `deterministic` to the function - will it decrease number of evaluation `exists` clause? – jsosnowski Jan 23 '19 at 15:38
  • I would have thought `exists` would only evaluate it once anyway as there's no correlation, so... possibly. Worth a try anyway. (Make sure it's actually valid for the function to be declared as deterministic though...) – Alex Poole Jan 23 '19 at 15:41
  • I really doesn't make any sense to execute something that isn't used at all, if you want the statement to be executed include it in the used select . – deFreitas Jan 30 '23 at 19:11

0 Answers0