Internals

Computing Oracle SQL_ID and HASH_VALUE

I needed an hash function, in Java, which is simple, practically less collision and provides a short alphanumeric or an unsigned number as hash for any string or text. I stumbled upon few blog posts that explain how Oracle SQL_ID and HASH_VALUE are calculated by database engine, concisely and elaborately; also implementations in Python and …

Computing Oracle SQL_ID and HASH_VALUERead More »

Execution Plan Operations and Options

The row source tree is the core of the execution plan. It contains the sequence of operations that the database performs to run the statement; each operation may have options associated with it. The operation in execution plan is also known as row source operator.  If you look at the below example, TABLE ACCESS operation …

Execution Plan Operations and OptionsRead More »

OVERLAPS predicate

OVERLAPS predicate is a useful feature, which is not available in most SQL implementations that can help to determine whether the two periods overlap each other. If two periods overlap then the predicate evaluates to TRUE otherwise FALSE. Let us create a test table with three rows – one with overlapping dates, second with non-overlapping …

OVERLAPS predicateRead More »

Oracle Database Statistics Names

Oracle database statistic names in this post is from Oracle Database 11g Release 11.1.0.6.0.  Many of these statistics are tied to the internal implementation of Oracle and therefore are subject to change or deletion without notice, even between patch releases. Application developers should be aware of this and write their code to tolerate missing or …

Oracle Database Statistics NamesRead More »

Oracle Database Undocumented Parameters List

Table x$ksppi contains all documented and undocumented (also know as hidden parameters, which starts with an underscore) parameters; joining with x$ksppcv table on column indx with filter predicate substr(x$ksppi.ksppinm,1,1) = ‘_’ get you parameter name, value and description. A complete list of undocumented parameters below: Oracle 11g (11.1.0.6) Undocumented Parameters Oracle 10g (10.2.0.4) Undocumented Parameters Oracle 9i …

Oracle Database Undocumented Parameters ListRead More »