SQL

By Franck Pachot

.
I’ve recently looked at Grafana. Pros: it is really nice, I mean aesthetically. If you want to build a dashboard for your boss, or put it on a large screen in the open space, that’s beautiful. Cons: not mature. I’m working for years with technology that works. When I have a problem, I find the solution (blog posts, forums, mailing lists, support notes)… But with modern software, the first problems I encounter have a dead end in unresolved git issues. And I’m actually spending more time in finding workarounds than solving problems. Yes, Grafana is nice but is very far from what I can do with Excel in 5 minutes. However, when I find a workaround, I publish it to help others and hopefully find someone who has a better solution, so please leave a comment if what I do there is completely silly.

There has always been that odd conflict between the language you use for SQL versus the language you use for PL/SQL when it comes to expressing what appears to the be the same thing.

PL/SQL: Do want a datatype that contains two elements? Use a RECORD

SQL> declare
  2    type MY_EMPS is record (
  3     empno number,
  4     ename varchar2(10)
  5     );
  6  begin
  7    null;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL: Do want a datatype that contains two elements? Use an OBJECT TYPE

One of the most commonly hit questions on AskTom is how to count the number of work days (Mon => Fri) between a start and end date range.

This is not a particular tough problem to solve since we have easy access to the day of the week via TO_CHAR, thus simply cycling between the start and end date looking for weekdays gives us the answer

In today’s video we demonstrate partial indexes for partitioned tables, introduced in Oracle database 12.1.

The video is based on this article.

• Partial Indexes for Partitioned Tables in Oracle Database 12c Release 1

The star of today’s video is Carry Millsap, who is being lead astray by a very naughty James Morle.

Cheers

Tim…

Slow down there….Take a breath. Because if you’ve read the title of this post and you’re already logging on to your 19c database, I need to you STOP.  Before you go anywhere, please read this post first.

OK … lets proceed, now that you’re not going to end up with a table you can’t drop!

There’s been a lot of interest in blockchain technology and rather than having to go out and purchase a completely bespoke solution, there has naturally been a lot of interest in being able to simply utilise blockchain technology in the same place where your other business critical data is located, namely, an Oracle database!

As I write this, the moment you do a browser search for “blockchain”, the last thing you will get a match on is the technology elements. Instead, my results are flooded with hedge funds, short selling, Gamestop and bitcoin. A friend has been sending me messages along the lines of “Is it true Satoshi Nakamoto lives in a mountain cave surrounded by computers and wild yaks?”.

By Franck Pachot

.

Perhaps the most famous (or infamous) performance tuning problem you’ll find spanning decades of blog posts when it comes to Oracle and SQL is the “mystery” of why Oracle is not using an index when it is totally obvious to us as the developer that it should be.  The demo code is always along the following lines

In a world where databases are now the norm, whereas distributing data via a CSV file used to be commonplace, nowadays we often see the ubiquitous INSERT script being offered as a means to seed data. This is perfectly fine for those requirements where we are populating a finite list of reference data that is often required by an application to run for the first time. Things like list of valid genders, list of valid states or counties, list of valid post/zip codes, etc. They are all typically sourced from an owning authority, don’t change frequently over time, and even when they do, it is typically sufficient to manually make a correction to your database.

By Franck Pachot

.
I have posted, a long time ago, about Google Spanner (inserting data and no decimal numeric data types) but many things have changed in this area. There is now a NUMERIC data type and many things have improved in this distributed SQL database, improving a bit the SQL compatibility.