Database/PLSQL

073 - [Oracle PL/SQL] DETERMINISTIC hint

unsungIT 2024. 4. 10. 23:34

 

 

  • DETERMINISTIC 힌트와 result_cache 힌트의 캐쉬에서 결과를 가져온다는 유사한 점이 있디만, 중요한 차이점은 DETERMINISTIC(동일 세션에서만 캐쉬 공유), result_cache(다른 세션간에도 캐쉬 공유) 라는 점이다.
  • 특별한 경우가 아니면, result_cache 가 좀 더 유용함
  • If another session runs the same code with the same parameters the code is still executed. The cache is not shared between sessions.
  1. You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE.
  2. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function.
  3. If you subsequently change the semantics of the function, you must manually rebuild all dependent function-based indexes and materialized views.