About

Martin Klier

usn-it.de

How NLS settings can affect Oracle’s join behavior – v$ example

Recently, I had a problem with accessing Active Session History being not fast enough on Oracle Database 11.2 and 12.1. Looking at the explain plan, wow, no doubt why: Accessing two fixed tables with TABLE ACCESS FULL and joining them with NESTED LOOP. This couldn’t have been “works as designed”, it would render gv$active_session_history nearly […]

read more

Oracle SQL: Optimizing a WHERE predicate inequality with a Function Based Index

Intro Recently, on Oracle 11.2.0.3, I saw a join of three tables, using three times TABLE ACCESS FULL and HASH JOIN, without an obvious reason. During separating and understanding its components, the following scenario turned out to be the culprit. select * from CARDTEST where OBJECT_ID<DATA_OBJECT_ID; The optimizer is unable to calculate the cardinality of […]

read more