Writing

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

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what Advanced Index Compression is and how its used.Note: Advanced Index Compression is part of the Advanced Compression option. Make sure you have the appropriate Oracle Database Enterprise Edition license with the Advanced Compression option before implementing this feature.The Storage ChallengeDatabase indexes can be "storage hungry". Indexes can consume more space than the actual data tables.

Invisible indexes are a simple Oracle Database feature that let you test index changes safely and manage new/specialized indexing needs without impacting the normal database operations. This feature, added in Oracle 11g, gives you another way to manage indexes and improve database performance tuning.What Are Invisible Indexes?As stated above, Invisible indexes are regular database indexes that Oracle maintains but don't use during normal query optimization. They stay current with your data chang

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what the DBMS_CLOUD package is and how it's used.The DBMS_CLOUD package is what lets your Oracle database connect with cloud object storage services like Amazon S3, Azure Blob Storage, and Oracle Cloud Infrastructure (OCI) Object StorageWhat is DBMS_CLOUD? DBMS_CLOUD is a package introduced in Oracle Autonomous Database that lets you work with files stored in cloud storage services directly from

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what Hybrid Partitioned Tables are and how to use them.Have you ever had a bunch of data that you wish you could offload from your database but still access if needed? That's where hybrid partitioned tables come in.Before we look at hybrid partitioned tables, let's be sure to understand what partitioning is. Partitioning is a technique that breaks up a large table into smaller, more manageable pa

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what SQL Macros are and gives some practical examples.SQL Macros were made available in version 21c and have been back ported to 19.6. The demo below was setup and run on my free 23ai Autonomous Database.What are SQL MacrosI'll start with an example. You're working on a sales reporting system and have to calculate employees commission in a bunch of different queries. Maybe they are simple queries

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what Private Temporary Tables are and gives some practical examples.What are Private Temporary Tables?Private Temporary Tables (PTTs) are session-specific tables that automatically clean themselves up when you're done with them. Like a private notepad - they're only visible to your session, and they disappear when your session ends. Introduced in Oracle 18c, they are a cleaner and more secure alt

Last week, during a regular chat with my boss, we talked about a few Oracle Database features from the past.That conversation sparked the idea for this blog. While I usually focus on what’s new and upcoming in the Oracle Database, this blog highlights 10 features that might fly under your radar. I will update each of the features to include a link to a more in-depth blog with a full code demo.. 1. Flashback Time Travel (11g): Full Blog Link Have you ever accidentally updated some data and didn

What are Invisible Columns? Invisible columns are exactly what they sound like: columns that exist in your table but don’t show up in a...

Online Table Move lets you to move a table, its partitions, and sub-partitions without downtime. Online table move also has the ability to filter and compress data as part of a move. This means that you don't need to take an outage to support the reorganization of tables and the table move / reorganization can happen while transactions are running against it. Why Use Online Table Move in Oracle?Imagine you’ve got a big, active table, and you need to make some changes. Maybe you want to move it t

This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what Flashback Time Travel is and gives a small demo example.What it is:Flashback Time Travel is a feature of Oracle Flashback Technology that lets you store and track all changes to the data in your table(s) for a decided amount of time including across DDL changes on tracked tables. This could be useful for a handful of reasons • Compliance - you want to keep a historical record of your data or

Imagine asking natural language questions against the database and receiving relevant, personalized answers. Oracle Database 23ai’s AI Vector Search makes it possible. In this blog, we’ll take a high level look at how AI Vector Search works and how you can use it to build smarter, context-aware applications.How does it work?At its core, this feature brings together your business data with Large Language Models (LLMs) and allows you to do things like Retrieval Augmented Generation (RAG). RAG comb

In a previous blog, we built a simple Coffee Brew Tracker application. Now, it’s time to extend that application and explore one of the 300+ new features in Oracle Database 23ai: JSON Relational Duality Views (or just Duality Views from here on out).This post will briefly explain what Duality Views are and walk you through how to easily add them in your applications. Before you begin:This blog assumes you’ve already built the Coffee Brew Tracker application from the previous post. If not, grab

Coffee has always been a passion of mine. Last night while watching the Pat Mcafee Show I decided to channel that passion into something productive—a simple coffee brew tracking app. This blog will walk you through how to build a Node.js app and easily store your data using a free Oracle Cloud Database.Whether you’re a fan of coffee, code, or just someone curious about blending the two, this tutorial will guide you step-by-step. By the end, you’ll have a working app to track your brews and a fre

Yes, you read that right. We’re setting up an Oracle Database in less time than it takes to make a latte. We’ll be using Oracle’s free tier on their cloud, which means no cost and a decent amount of compute resources.You can create your own account and follow along here: https://www.oracle.com/cloud/free/And if you’re a hands-on type who likes to work locally, you have a few options too. Here’s how you can set up Oracle db on your machine: • Docker/Podman – for container lovers. • Oracle Virtual

If you're building a python app and need a database, you can connect to a free cloud hosted Oracle Autonomous Database with 5 lines of...

I’ve often found myself on a journey of being presented with a project or problem and feeling like, “wow, I have so much to learn,” to then solving the problem and thinking, “I feel comfortable now and I know everything I need to,” only to go back to the original feeling immediately upon starting my next project.Frank Besch introduced me to the concept of the Trough of Disillusionment, and although it’s about new technologies:I often compare it internally to how I feel about learning. Rather, it

Question: Wouldn't it be nice if you could easily shrink your tablespace? Well, now you can! In Oracle Database 23ai, we introduced a new procedure to make the process as easy as possible. The shrink_tablespace procedure offers a simple way to reclaim unused or free space in your tablespace.The next 5 minutes will show you how to use the new shrink_tablespace procedure.Wait, why would you want to shrink a tablespace?​Before diving into the "how," it's nice to understand why you might want to shr

Hey 👋🏼, I'm Killian.Pictured: My two brothers and I (in the middle) at the Gap of Dunloe, Ireland, circa March 2021I’ll share a bit about me, what I’ll be writing about, and why I’m starting this blog.​Ready? Cool.Who is “Killian Lynch?” ​I’m a 26-year-old Texas native, currently living in Austin, Texas, but I’ll be moving to London at the end of October 2023 (and I’ll probably write a blog about it once I get settled). I’m a Product Manager for the tech company Oracle, and my greatest aspira