SQL

One of the very cool features we’ve been talking about for 20c is SQL Macros. But you no longer need to wait for a future release of the database to get access to all the goodness of SQL Macros.

Why? Because much of the functionality has now been backported to 19c, and is also now officially in the documentation so there’s no ambiguity as to whether you are supported to use them or not. They’ll be coming soon to an RU near you

• 19c
• Autonomous
• livesql
• Macros
• SQL
• Uncategorized

Any database application that stores information about people will very likely store a person’s date of birth as part of that data. Whether it be for marketing purposes, or part of the security checks for a password reset, or part of the authentication when the inevitable call centre calls you with their “latest, greatest offer”, the date of birth is common.

But when it comes to reporting, typically it is not the date of birth that we present on our screen or hard-copy, but the person’s age. Clearly we cannot store a persons age in the database because it changes every day, month or year depending on the granularity with which you present it. Which leads to the ponderance – Surely it cannot be that hard to calculate someone’s age from their date of birth?

The Developer Live event for database has concluded. Thank you to the (almost) 2000 people that attended my talk across the USA, Europe and APAC timezones! I very much appreciate you giving up your time to attend the session.

Whilst we were doing some Q&A I tried to add as many useful links into the chat line as I could, to help people with onward learning on SQL performance and database performance in general. A few of you then asked if I could publish that chat as to not lose that information, so here it is below

From Me to Everyone:  04:06 PM
Hi everyone,

Its Connor here. Chris Saxon and I are monitoring the Q&A channel, so fire off your questions and we’ll answer them right here.  Anything we can’t answer, we’ll tackle at a later date.

If I gave you a telephone book and asked you to tell me how many distinct street names are present in the book, then the most likely thing you would do is …Wave your mobile phone at me and ask what a “telephone book” is . But assuming you’re old enough to remember telephone books, you’ll probably tackle the task by sorting the entire set in street name order and then work your way down the list and incrementing your count of distinct street names every time it changed. 

In database terms, a little demo of that approach is as below

By Franck Pachot

.
In The myth of NoSQL (vs. RDBMS) “joins dont scale” I explained that joins actually scale very well with an O(logN) on the input tables size, thanks to B*Tree index access, and can even be bounded by hash partitioning with local index, like in DynamoDB single-table design. Jonathan Lewis added a comment that, given the name of the tables (USERS and ORDERS). we should expect an increasing number of rows returned by the join.

In this post I’ll focus on this: how does it scale when index lookup has to read more and more rows. I’ll still use DynamoDB for the NoSQL example, and this time I’ll do the same in Oracle for the RDBMS example.

By Franck Pachot

.
I have written a few blog posts about some NoSQL (vs. RDBMS) myths (“joins dont scale”, “agility: adding attributes” and “simpler API to bound resources”). And I’ll continue on other points that are claimed by some NoSQL vendors and are, in my opinion, misleading by lack of knowledge and facts about RDBMS databases. But here I’m sharing an opposite opinion: SQL being user-friendly is now a myth.

What better way to start a new week than with a cheesy title to my blog post!

But there is method to my unstructured madness today as we talk about the TREAT function, available from 18C for JSON data. As many of us will already know it is possible to dive into JSON data and extract information from its components as if they were relational columns using the very cool dot notation

In order to do so, we need to be a little generous to the database by letting it know that the contents of a unstructured column such as a CLOB is truly correct JSON data.

I will start with a table with a simple CLOB which does indeed contain valid JSON but I have not let the database know about that fact:

Aggregation

SQL is not all just primary key lookups, although some applications sadly see SQL as only useful in that way. As soon as you have data, you will always have the need to aggregate it, and SQL is the perfect vehicle for that. I did a video recently which I will embed below that was discussing the new 19c COUNT DISTINCT facilities which can dramatically speed up query processing that have requirements including the DISTINCT clause. One of the things I covered was the difference between our traditional aggregation facilities of sorting the data versus our new improved hashing facilities.

By Franck Pachot

.

At college/university, the learning path for a computing-related course often includes several coding languages, and occasionally some treatment of database technology as well. But often, it is only once you enter the business world as you embark upon your IT career that you truly start to face the demands of enterprise-level data requirements. The small amount of education on databases, or the small databases themselves sometimes do not prepare you for the realisation that data drives everything in the business world.

And where does one start? The amount of content out there about databases is overwhelming, and often assumes a base level of knowledge or is targeted at solving specific business problems.

If you have faced this obstacle, then I’m pleased to offer a new resource for you. One simple, consolidated, FREE training guide to kick start your database knowledge on developing applications on the Oracle Database.

Enjoy!