Ranges with NULLs 07: Swiss Army Knife

I came up with this name a few years ago for a problem that I couldn't solve then: analyse a series of ranges and say whether and how they overlap. It turns out the solution is not that hard.

Lab Correction

Anyone using the August 2018 Fedora image should note that I neglected to put the right transaction_upload2.csv file in the /u01/app/oracle/upload directory. You can fix that by navigating to the Lab 12 Instructions web page and click on the zip file link to download the correct file. You will see the following dialog asking whether you want to open the file with the Ark utility, click OK to continue:

After clicking OK to open (more...)

Ranges with NULLs 06: Overlaps with Conflicting Data

In 2014 I attacked the problem of "Overlapping ranges with priorities". This time I'll deal with NULLs and propose an improved solution.

ITNEXT NEXT100 CIO – 2018

Hey All, Faced some really proud moments last week. I was one of the only 100 winners of NEXT100 CIO 2018 award. NEXT100 is an annual awards program instituted by IT Next magazine and the 9.9 Group that aims to identify 100 experienced IT managers who have the skills, talent and spirit to become CIOs. The … Continue reading "ITNEXT NEXT100 CIO – 2018"

Verhalten von Index Splits

Wieder mal bin ich spät dran und exzerpiere relevante Artikel, kurz bevor sie aus dem 30-Tage-Korridor meines Blog Aggregators fallen. Und wieder mal ist es der zur Zeit ungeheuer produktive Jonathan Lewis, dessen Beiträge ich zusammenfasse. In den fraglichen Artikel beschäftigt sich der Herr Lewis mit dem Verhalten von "Index Splits", die auftreten, wenn ein neuer Index-Eintrag nicht mehr in den vorgesehenen Block der Index-Struktur passt:

Ranges with NULLs 05: Segments

Take two overlapping ranges: "1 to 3" and "2 to 4". We can divide them into three smaller ranges: "1 to 2", "2 to 3" and "3 to 4". I'll call these smaller ranges segments. We sometimes need these segments to solve advanced range problems.

Logging Triggers

Oracle Logging Trigger Results

This article demonstrates how you can write log files from triggers on different tables to the same logging table. This approach leverages Oracle’s object types and column substitutability features. It also eliminates the requirement to create a unique logging table for each logging trigger. The trick to accomplishing this requires mastering two skills.

The first skill requires you to learn how to create user-defined types (UDTs) and subtypes. The UDT stores (more...)

Critical Triggers

Oracle Critical and Non-critical Triggers

This article demonstrates how you can write critical and non-critical row-level triggers. You may ask yourself, what are critical and non-critical triggers? That’s a great question. A critical trigger stops processing and raises an exception within the scope of an Application Programming Interface (API). An API is typically a series of end-user forms that help you solve business problems. A non-critical trigger either allows users to perform undesired behavior or (more...)

Schema-on-what? How to model JSON

How do you make sense out of schema-on-read? This post shows you how to turn a JSON document into a relational data model that any modeler or relational database person could understand.

Oracle Trigger Basics

Oracle Trigger Basics

Once you master the basics of inserting, updating, and deleting data from the database, you typically learn about database triggers. Database triggers are coding elements that let you manage events beyond the limit of database constraints.

Before you can appreciate the power of database triggers, you need to understand what database constraints can and can’t do. Then, together we’ll explore how you can implement database triggers.
Database constraints let you manage events. (more...)

Types & Column Substitutability

Object Types and Column Substitutability

This article shows you how to use extend parent (or superclass) objects. You extend parent classes when you implement specialized behaviors (or methods) in subtypes. That’s because SQL statements can’t work with specialized methods when a table’s column stores subclasses in a superclass column type.

Substitutability is the process of storing subtypes in a super type column. It is a powerful feature of the Oracle database. The “type evolution” feature (more...)

Types & Subtypes

Object Types and Subtypes

This article teaches you how to use subtypes or subclasses. You can define an object type with or without dependencies. Object types can have two types of dependencies. The simplest case occurs when you define an object attribute with an object type instead of a data type. The more complex case occurs when you define an object subtype because it inherits the behavior of the base object type. The base object (more...)

Type Getters & Setters

Object Types with Getters and Setters

This article is for you when you know the basics about how you work Oracle’s object types. It teaches you how to write effective getters, setters, comparators, and static methods. Please read my Object Types & Bodies Basic article if you’re not sure how to work with object types.

Getters access an object instance and return values from an instance variable. Along with getters, you have setters. Setters let (more...)

Column Groups und NULL Werte

Bereits vor einigen Wochen hat Jonathan Lewis auf eine Problem hingewiesen, das sich bei Verwendung von Column Groups ergeben kann: wenn der Anteil der NULL-Werte in einer Spalte sehr groß ist, dann ist das für den Optimizer über die NUM_NULLS-Information erkennbar. Da Column Groups intern über eine virtuelle Spalte abgebildet werden, die die Informationen zu den in der Gruppe enthaltenen Spalten zusammenfasst, verliert die NUM_NULLS-Information auf dieser Ebene ihre Aussagekraft, da die Spaltenkombination insgesamt nicht (more...)

Polymorphic Table Functions

I have been working on a presentation on Polymorphic Table Functions. During this time I was looking for a real use case for Polymorphic Table Functions. I came up with an example which is not very useful in real life, but very useful to explain the technique.
At my current job I came across a piece of code that I had to copy and adjust to fit the needs for that specific case. The idea (more...)

Automatic Clustering, Materialized Views and Automatic Maintenance in Snowflake 

Boy are things going bananas at Snowflake these days. The really big news a few weeks back was another round of funding! This week we announced two new major features.

Join Cardinality und Histogramme

Jonathan Lewis hat zuletzt eine Artikelserie veröffentlicht, die sich damit beschäftigen, wie sich die Existenz unterschiedlicher Histogramm-Typen auf die Bestimmung der Join-Cardinality auswirkt. Seine Überlegungen basieren dabei auf einem umfangreicheren Artikel von Chinar Aliyev aus dem Jahr 2016. Da der Herr Lewis da natürlich sehr viele Details beleuchtet, verzichte ich weitgehend auf die Zusammenfassung und beschränke mich auf die Erfassung der Links, um die Hoffnung zu erhalten, sie bei Bedarf wiederfinden zu können:

DAX Studio für Microsoft BI

Zwar sind die Zeiten, in denen ich mich mit Microsofts BI-Landschaft intensiver beschäftigt habe, schon lange vorbei, aber sollte ich jemals wieder damit anfangen, dann wäre das DAX Studio sicher ein Werkzeug, das zu berücksichtigen wäre. Vincent Rainardi verweist in seinem zugehörigen Artikel auch auf eine nützliche Liste weiterer BI Tools, die unter https://www.sqlbi.com/tools/ gepflegt wird. Ich erinnere mich noch gut daran, wie ich nach der Abwanderung von Mosha Pasumansky auf eine Weiterentwicklung (more...)

Design considerations to replicate Data Lake site for Availability

Why replicate a data lake? Building an enterprise data lake demands heavy weightlifting in terms of cost, planning, technical design, architecture, and operational excellence. Building another infrastructure as a standby will shoot up both capex and opex. Therefore, before proceeding to the planning phase, one must have a strong justification and clear objective behind setting … Continue reading "Design considerations to replicate Data Lake site for Availability"

Lazy Loading Menu

I've been using the lazy loading concept demonstrated in Maxime's post quite a lot recently, I'd love to see this as a declarative feature one day.

I also wondered if I could apply this concept to the badge count in the side menu.


Turns out it wasn't that hard, particularly since I already had the jQuery I needed from a previous requirement.

We first need to add a unique class to the link definition, so (more...)