SQL

In today‘s video we’ll give a quick demonstration of applying changes to the database using the Liquibase implementation in SQLcl.

The video is based on this article.

• SQLcl : Automating Your SQL and PL/SQL Deployments using the SQLcl implementation of Liquibase

You might also find these useful. The secure external password store is a good way to make connections with SQLcl. If you support a variety of database engines, you may prefer to use the regular Liquibase client.

By Franck Pachot

What I want to show in this blog post is that, as in mathematics where you have to apply some algebra rules to transform an equation to an equivalent one, the database developer must translate the business specification to an equivalent that is optimized (in performance, reliability and readability) for the data model.

By Franck Pachot

.
Let’s say you have a PEOPLE table with FIRST_NAME and LAST_NAME and you want, in many places of your application, to display the full name. Usually my name will be displayed as ‘Franck Pachot’ and I can simply add a virtual column to my table, or view, as: initcap(FIRST_NAME)||’ ‘||initcap(LAST_NAME). Those are simple SQL functions. No need for procedural code there, right? But, one day, the business will come with new requirements. In some countries (I’ve heard about Hungary but there are others), my name may be displayed with last name… first, like: ‘Pachot Franck’. And in some context, it may have a comma like: ‘Pachot, Franck’.

There comes a religious debate between Dev and Ops:

One of the cool things with materialised* views in Oracle is their ability to be kept in sync with the source table(s) from which they are derived from, in real time or near real time. To achieve this, we typically employ mechanisms such as materialised view logs to capture modifications to the source tables, and occasionally we need to change the definition of the materialised view itself when dealing with aggregations and joins. However, some times we know that if DML on the source is incredibly rare and/or the cost of updating the materialised view is very small, we can avoid all that and simply perform a REFRESH COMPLETE whenever a transaction is committed on the source tables. This avoids any issue around the materialised view becoming stale, and also avoids the need for scheduler jobs to keep the materialised view refreshed.

Today’s video takes a look at using Multitable Inserts, which allow an INSERT … SELECT to create rows in multiple destination tables. If you’ve not heard of them, don’t be surprised as they are really new, having only been introduced in Oracle 9i.

• Multitable Inserts
• Oracle
• SQL
• video

Today’s video is a demonstration of schema only accounts, introduced in Oracle Database 18c.

This is based on the following articles.

(this is an answer to Jeff Potter — “3 Reasons I Hate Booleans In Databases”)

I’ll start with the “benchmark” because I like facts.

Testcase

Here is your test which I ran on less number of rows (because it is not needed and easier to run and share from db<>fiddle) and I’ve run the queries once before in order to warm-up the cache. And I displayed the execution plan to get better understanding about the response time:

(this is an answer to Jeff Potter — “3 Reasons I Hate Booleans In Databases”)

I’ll start with the “benchmark” because I like facts.

Testcase

Here is your test which I ran on less number of rows (because it is not needed and easier to run and share from db<>fiddle) and I’ve run the queries once before in order to warm-up the cache. And I displayed the execution plan to get better understanding about the response time:

https://oracle-base.com/blog/wp-content/uploads/2019/12/sqlcl-295x300.png 295w, https://oracle-base.com/blog/wp-content/uploads/2019/12/sqlcl-57x57.png 57w" sizes="(max-width: 189px) 85vw, 189px" />

You’ll have heard me barking on about automation, but one subject that’s been conspicuous by its absence is the automation of SQL and PL/SQL deployments…

If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections etc, all of which might be useful particularly in terms of auditing security

However, it also is in a fairly loose text format, which means ideally I’d like to utilise the power of SQL to mine the data.