SQL

SQL is the Structured Query Language used to define and manipulate data in most of the databases in the world, and the most critical ones (banks, hospitals, airlines, secret services… ). And then, it gives the impression that with SQL you can do whatever you want, bypassing all application control, as if it were a backdoor to your database, wide opened on the network.

Programmers always laugh when seeing ridiculous hacking scenes in movies. In 2016 there was this “use SQL to corrupt their database” line in Jason Bourne (nothing to do with JSON or /bin/sh, by the way, it’s a movie) and recently in StarTrek: discovery series the hacking 'audit' was explained as “The probe used multiple SQL injections”. I’ve put the links at the end of this post.

There are plenty of blog posts about using the pre-processor facility in external tables to get OS level information available from inside the database. Here’s a simple example of getting a directory listing:

It’s a holiday here today in Perth, so a very brief blog post because I’ve been busy doing other things today

Just a quick blog post on MERGE and the “unable to get a stable set of rows” error that often bamboozles people. This is actually just the script output from a pre-existing YouTube video (see below) that I’ve already done on this topic, but I had a few requests for the SQL example end-to-end, so here it is.

Imagine the AskTOM team had a simple table defining the two core members, Chris Saxon and myself. But in the style of my true Aussie laziness, I was very slack about checking the quality of the data I inserted.

I wrote a post a while back call “The Death of UTL_FILE”, and probably because of it’s click-bait title I got lots of feedback, so I’m back to flog that horse . Seriously though, I stand behind my assertion in that post, that the majority of usages of UTL_FILE I’ve seen my career are mimicking the spooling behaviour of a SQL*Plus script. And as that post pointed out, you can now achieve that functionality directly with the scheduler.

That is well and good for writing files from the database, and I added:

It’s a simple requirement. We want to transform this:

SQL> select deptno, ename
  2  from   emp
  3  order by 1,2;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH
        30 ALLEN
        30 BLAKE
        30 JAMES
        30 MARTIN
        30 TURNER
        30 WARD

into this:

Oracle is very well instrumented, for decades, from a time where measuring the memory in bytes was ok. But today, we spend a lot of time converting bytes in KB, GB, TB to read it easily. I would love to see a Human-Readable format for TO_CHAR, but there’s not. Here is a workaround without having to create a new function.

DBMS_XPLAN does that when displaying execution plans and we can access the functions it uses internally. The metrics can be numbers, and then the Kilo, Mega, Giga applies to powers of 1000. Or they can be a size in bytes, and we prefer the powers of 1024. Or they can be a time in seconds, and then we use a base 60. And then we have 3 sets of functions:

Here’s an interesting little oddity (aka bug) with scalar queries.

We’ll start with a simple working example

SQL> create table t1 ( c1 number );

Table created.

SQL> insert into t1 values (1);

1 row created.

SQL> create table t2 ( c1 int, c2 varchar2(10));

Table created.

SQL>
SQL> insert into t2 values(1,'t1');

1 row created.

SQL> insert into t2 values(1,'t01');

1 row created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T1')

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','T2')

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> select a.c1,
  2    ( select max(b.c2) from t2 b where a.c1 = b.c1 )
  3  from t1 a;

        C1 (SELECTMAX
---------- ----------
         1 t1

1 row selected.

That all seems straightforward:

In my previous post about the advantages of index access over full table scans, I mentioned covering indexes. This is when an Index Range Scan can retrieve all columns without going to the table. Adding to an index all the columns used by the SELECT or WHERE clause is an important tuning technique for queries that are around the inflection point between index access and table full scan. But not all RDBMS are equal. What PostgreSQL calls ‘Index Only’ actually reads the table, except for static data with no concurrent modifications.

I’ll show the execution plans for this Index Only access on Oracle, MySQL, PostgreSQL, and MS SQLServer. As my skills on the non-Oracle ones are very limited, do not hesitate to comment if you think something is not correct.

One of the new features in 18c is the ability to ignore any optimizer hints in a session or across the entire database. A motivation for this feature is obviously our own Autonomous Data Warehouse, where we want to optimize queries without the potential “baggage” of user nominated hints strewn throughout the code.

This would seem a fairly easy function to implement, namely, as we parse the SQL, simply rip out anything that is a comment structured as a hint. At the Perth Oracle User Group conference yesterday, I had an interesting question from an attendee – namely, if all optimizer hints are being ignored, then does this mean that every hint will be ignored. In particular, what about the (very useful) QB_NAME hint? If we are just stripping out anything that is in a hint text format, we will lose those as well?

So it’s time for a test!