Killian Lynch

Today's throwback feature of the week is Read-Only Partitions and Subpartitions. Read-only partitions let you block the inserts, updates, or deletes. Any attempts to change the data will fail with an error. Why Use Read-Only partitions?Read-only partitions are great when you're working with historical / archival data, compliance records, or data intended for reporting and audits. They benefit from consistency, accuracy and database backups. Because read-only partitions don't create redo, RMAN bl

Today's throwback feature of the week is the Real-Time Materialized View.What are Materialized Views and how are they different from Real-Time Materialized Views? A Materialized View stores the pre-calculated results of a given query. This can have huge performance benefits and drastically improve query response time because the database can use the pre-calculated results directly. When using Materialized Views, if the data in the table changes, the pre-calculated query results can become outdat

Today's throwback feature of the week is the Global Temporary Table (GTT). Global Temporary Tables are permanent database objects stored on disk but the content is visible only to the session that inserts the data. The content of the GTTs can exist for a single transaction, or across the lifetime of a session.Using Global Temporary TablesI'll start with an example. You've got an e-commerce site that has periodic 'flash sales' and you want to create reports of user behavior during the sales, but

This week's throwback feature of the week is the virtual column. Virtual Columns let you create columns that aren't stored physically but are calculated on the fly when selected.Virtual Columns have been available in the Oracle Database since 11g, released around 2007.Virtual Columns in ActionSince the NHL playoffs recently started, let's imagine we want to build a small app where we track shots and goals per player, and we want to calculate the shooting percentage without storing it. We'll use

This week's throwback feature of the week is a simple and relatively well known SQL Operator. It's the ability to pivot and unpivot SQL columns inside a table. Pivoting and Unpivoting inside a table is an easy way to change how your data is presented.This feature was released in Oracle Database 11gWhat are PIVOT and UNPIVOT?Simply put, PIVOT rotates rows into columns, while UNPIVOT does the opposite, it turns columns back into rows. This can be really useful for reporting and analytical models

This week's throwback feature of the week is the Identity Column. Identity Columns give you an easy way to create unique numeric identifiers on a column in your table. They are an alternative to manually doing this with sequences and triggers.This feature was released back in Oracle Database 12c (Release 1).This blog walks through a basic example of how to use the Identity Column in the Oracle Database.What Are Identity Columns And Why Use Them?Identity Columns are automated numeric fields that

This week's throwback feature of the week is the PL/SQL Function Results Cache. This feature lets you easily cache commonly calculated function results. Using the PL/SQL Function Results Cache can lead to a huge boost in response time / performance of PL/SQL functions. The results of the functions can be reused by any other sessions calling the same function. This blog walks through a practical example of how to use the feature.Why would you want a results cache?The PL/SQL Function Result Cache

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what the APPROX_COUNT_DISTINCT function is and how its used. This feature isn't as flashy as some of the others in the overall post but is still great and is often overlooked. What is APPROX_COUNT_DISTINCT APPROX_COUNT_DISTINCT was added in oracle 12.1 and gives you approximate distinct counts using some clever algorithms. Instead of checking every single value, it uses statistical methods to est