Using a simple SQL Demo to build my Annual Report

Before coming up the Spring Festival of China on 2020 I finally finish building a simple SQL Demo as my Annual Report 2019. Here I use two tables (one is parent table “work_category”, another is child table “daily_work”) to build my funny demo, their ERD (Entity Relationship Diagram) is as follows.

At the same time I use another two SEQUENCES to build AutoNumber where there has a separate ID column (“WC_ID” and “DW_ID”) on respective those two tables.

Next I will use several SQL statements to create those two tables mentioned previously. In other words it briefly contains the following 4 portions.

• dropping table (“work_category”, “daily_work”) if they exist
• dropping sequence (“work_cgy_seq”, “daily_work_seq”) if they exist
• creating sequence (“work_cgy_seq”, “daily_work_seq”)
• creating table (“work_category”, “daily_work”) and inserting real data

Optimizing the SQL Query of Null Value

A few days ago I found this pretty nice blog note “Sparse Indexing” published by David Kurtz on Jan 06, 2020 when I had glanced at my Twitter recent messages. Hence I once took some time reading his article and spent some other time running all those SQL code snippets cited on that post so that I am able to learn and deeply comprehend them.

Here I’ll respectively use 4 type of indexes to progressively optimize the SQL query of nullable value. They are below:

• Single Column Index
• Composite Index
• Funcion-Based Index
• Virtual Column Index

The following is my entire SQL script (tested on both Oracle version 18.3 and 19.3).

Occupied Percentage

Occupied percentage of the value of a certain column of a table is very common on Oracle OLAP scenario, so this post I’ll introduce three number of methods about how to write these SQL statements to achieve the same intention. They’re as follows:

• traditional usage
• ratio_to_report (analytic function)
• vivid character graph

Let me give you an example of SQL query that is a demo of “Spending Time of Querying COUNT table” I once built on my Github and Live SQL.

Real Execution Plan of Querying COUNT table

ACOUG Annual Meeting of this year I once shared an interesting session comparing query performance on COUNT() table. In fact I intend to demonstrate total 4 Demos but the speech time of every speaker is limited to approximate 30 minutes, eventually I decided to share only one Demo (the first one amongst 4).

As usual a blog note should have been published before publicly speaking the corresponding session, due to taking a large amount of time writing this Demo, initially which is only one and finally (as a result) it derives out the rest 3 in the process of testing it again and again. Of course, a big and special thanks my ODC (Oracle Developer Community) friends to give me much more help during the period of time I wrote my demo, you can read here and here perhaps you can also benefit a lot from me.

If you didn’t get an opportunity to listen to my session, please don’t worry about it. This note I’ll talk about all of my 4 Demos which have been tested on Oracle 11.2, 18.3 and 19.5 (Live SQL). Here I am not going to discuss the stuff of my Demos which you can read from my Github (inlcuding the running result on 18.3 and 11.2). Unfortunately it has not generated 10053 trc file on Live SQL now but which is able to display a friendly interface where there has a piece of SQL or PL/SQL code and relevant running output, and where I’ve also published my Demos in public, they’re separate LS_Demo1, LS_Demo2, LS_Demo3 and LS_Demo4.

I can absolutely tell you the running result of my 4 Demos is all the same on Oracle three versions I mentiond previously.

Dynamically creating Test tables

Sometimes we need to create several test tables and then write a series of SQL statements, next to check the corresponding execution plan or analyze its performance problem related. On one hand, it’s certainly making no sense that we’re going to operate real table on Oracle production system. On the other hand, in many cases in order to simulate an odd (weird) and knotty oracle problem we must manually create test table by ourselves on which data should have not potential security risk and are all random value.

So this note is aim to use total 4 methods to dynamically create our test table.

ACOUG Annual Meeting

A couple of days ago I’m pretty grateful to be invited by organizer ACOUG (All China Oracle User Group) as a speaker to present my session. To be honest I have been an audience or attendee to participate similar events and meetings from ACOUG more than once in the past few years.

ACOUG annual meeting of this year is coming soon, looking forward to seeing some old and new friends, guys, peers and my fellow DBAs/Devs at 14:00 – 18:00 on Dec 21, 2019. BTW it is (same as usual) located here (the 21th floor of Oracle (China) Software System Co., Ltd).

About the agenda of this meeting you can read this, see you there!

Update (Dec 17, 2019)

The previous stuff of my post is an advance notice from ACOUG meeting organizer. The day before yesterday he formally announced the sharing topic detailed of every session speaker. You can take a look at this.

Security Message

On generic Linux OS we’re able to add some stuff in file “/etc/motd” in order to give you a security WARNING or a FUNNY logo. Of course you can read my this post to find out something. Then in Oracle how could I archive the same effect when logging on to the command line window of SQL*Plus? After reading David Fitzjarrell‘s article “Setting A Logon Security Message In Oracle” I know the answer.

David mentioned a parameter “SEC_USER_AUDIT_ACTION_BANNER” which has been added in file “sqlnet.ora” that is located in the “$ORACLE_HOME/network/admin” directory on his post. Next I search that parameter on Oracle Docs and have understood it has existed from version 11g and later. The following 2 screenshots can also indicate it.