Uncategorized

Summary

Goal in this post is to layout a methodology that can be implemented to scan the explain plans and identify opportunities to optimize the sql execution with additional indexes.

The missing index opportunities outlined here are

1. Indexes that would avoid full table scan with a predicate filter that filters out most of the returned rows
2. Joins that are missing index on the join field
3. Index range scans that could be made more efficient by addition of a field to existing index

Also for future consideration, but not yet outline here

Fellow Perth techie Scott Wesley sent me this interesting puzzle recently. He was using the long awaited feature of being (finally) able to assign a sequence value via the DEFAULT clause in a table definition.

The problem was … the sequence was NOT being assigned. And since that column was defined as NOT NULL, his application was breaking. We had some to-and-fro and finally I managed to reduce it down a very small test case, which I’ll build from scratch here.

Most customers I encounter nowadays are generally sticking to the defaults when creating their databases from 12c onwards. This means that they’ll be using the UTF8 class of charactersets when configuring their database. As a quick segue from the topic at hand, I think this is a good thing. Having a UTF8 characterset avoids a lot of hassles later when applications encounter a need to store extended characters, and let’s face it, the moment you need to store someone’s name, then at some stage you are going to hit this.

But back to large objects. It is important to realise that a UTF8 characterset has implications with how LOBs will be stored in the database. In a single byte characterset database, then if you need to store a 1 megabyte text file in a CLOB, then it will consume approximately 1 megabyte. But lets look at what happens when you are using a multi-byte database.

MySQL is simpler than the Oracle example  because MySQL only has Nested Loops and doesn’t have Hash Join.

Here is the query, like on the Oracle example from the last blog post

select max(t1.data) 
from  t1, t2  
where t1.id = t2.id 
and t1.clus  = 1
;

So there are only 4 examples below which all do NL joins

1. full table scan on T1 and T2
   1. 7.83 secs
2. index on T1 predicate filter column
   1. 7.39 secs
3. index on T2 join column
   1. 0.49 secs
4. index on both T2 join column and T1 predicate filter column
   1. 0.06 secs

There isn’t an idea of “explain analyze” on MySQL until MySQL 8.0.18 and I did my testing on 8.0.17, so the explain plans costs are estimates not actual values, but the elasped time of the query is an actual value.

If you cannot wait for a fully autonomous offering, and you’ve jumped into the 20c preview release on Oracle Cloud, obviously the first thing you will probably be installing is Oracle Application Express.

Unlike autonomous, you’ll be installing it manually, which is a quick and easy process, and either in that installation or when adding ORDS later, you’ll be wanting to set the passwords for the public access accounts (typically APEX_PUBLIC_USER and APEX_REST_PUBLIC_USER).

Here’s what that looks like in Oracle Database 19c

Looking at ways to programmatically analyze explain plans to see if adding indexes would help.

The analysis is straight forward for for full table scans with strong predicate filters. If there is a strong predicate filter on an explain plan node with a full table scan then it’s a good candidate for an index.

Now a similar situation exists for a join.

Again if the join has a strong predicate filter on a full table scan, that is a candidate.

On the other hand when the actual join fields look like they will benefit from an index, it’s less obvious.

In the queries below T1 and T2 have the same data.

Here is the query

select  max(t1.data) from  t1, t2 
where 
      t1.id = t2.id  
   and t1.clus   = 1  ;

First we try with no indexes.

Then we try with an index on the predicate filter on T1.

Then we add a index on the join column on T2.

Let me start by saying that by no means am I a working from home expert; I do not have a degree in ergonomics or human resources or time management. I am just your normal IT worker that happens to have been working from home for a number of years now.

If you have just had that email from your manager or your Human Resources Department that says “Guess what! Time to grab a laptop and set up shop from home”, I thought I would share a couple of my experiences with you to ease the transition, and who knows? Maybe this will be the launch pad for you to pursue a remote working position full time with your current employer (or even a new one )

Today’s video demonstrates how to configure Kata Containers on Oracle Linux 7 (OL7), allowing you to run containers inside lightweight virtual machines (VMs).

This video is is based on an article of the same name, but relates to a bunch of other articles and videos on the subject of containers.

Outlook for the months ahead >>

I decided to put together some information on COVID-19 purely for my own interest – but then decided I might as well put it on a blog post. I’m only going to link to what I feel are reputable sources, nothing from tabloid papers or people promoting conspiracy theories.

If you know of a good site I should include or there is an area I have not touched on that you would like more information on, please feel free to let me know.

This question got posed on Twitter today

I answered briefly on Twitter, but I thought I should give it some “meat” with a blog post.

Firstly, lets explore the commonly understood mechanism where in a conventional index, nulls are not stored and hence you can have multiple index entries (so to speak) where the value is null.

Here is my simple table T with a standard (ascending) unique index, and we can see immediately that there is no limit to the number of null values, where by “null” I mean that all indexed columns are null, that you can have in the table.