New Top-N Queries and Pagination Syntax

At the RMOUG Training Days, a couple of weeks, ago @GeraldVenzl and I delivered a session demonstrating what it would take to get a REST enabled, web-based application up and running with Oracle Database 12c. During the session Gerald asked me to write a query to calculate the top 10 customers we had in terms of their total spend. Below is the query I came up with.

SELECT c.c_name, 
       c.c_custid loyaltyCardNo, 
       t.city, 
       t.state,
       SUM(salesAmount) total
FROM   customers c,
       t
WHERE  c.c_custid = t.loyaltyCardNo
GROUP BY c.c_name,
         c.c_custid,
         t.city,
         t.state
ORDER BY total DESC
FETCH FIRST 10 ROWS ONLY;

After the session I got a number of questions regarding the “FETCH FIRST 10 ROWS ONLY” syntax that I used, so I thought it would be worth explaining what it is and what happens under the covers when you execute it.

What is FETCH FIRST X ROWS ONLY?

Continue reading

Posted in Oracle Database 12c New Features, Syntax | Tagged , | 1 Comment

What’s New With Database In-Memory in 12.2?

My favorite marquee feature in Oracle Database 12c is Database In-Memory. With the introduction of Database In-Memory in 12.1.0.2, data can now be populated into memory both in a row format (the buffer cache) and a new in-memory optimized column format, simultaneously.

The database maintains full transactional consistency between the row and columnar formats, just as it maintains consistency between tables and indexes. The Oracle Optimizer is fully aware of what data exists in the column format and automatically routes analytic queries to the column format and OLTP operations to the row format, ensuring both outstanding performance and complete data consistency for all workloads without any application changes.

So, what can you expect from Database In-Memory in 12.2?

Continue reading

Posted in Database In-Memory, Oracle Database 12c New Features | Tagged , | Leave a comment

What To Expect From Oracle Database 12c

I know I promised more blog posts on the new features in Oracle Database 12c but I thought I would cheat on today’s post and point you to an article I wrote in the current issue of UKOUG’s Scene Magazine.

There you will find a 3 page article that outline what you can expect from Oracle Database 12c both in terms of marquee features as well as small but useful enhancements.

I’ll have more technical posts on these and other new features in the coming weeks.

Click on the picture to read the article.

Three page overview of what to expect from Oracle Database 12c

Posted in Oracle Database 12c New Features | Tagged , | Leave a comment

Longer Identifiers in Oracle Database 12c Release 2

Now that Oracle Database 12c Release 2 is available on-prem, I thought it would be a good time to remind folks about what they can expect from the new release. I’m going to divide topics into two categories, Marquee Features and top-tips. Let’s start with a top-tip!

Prior to Oracle Database 12c Release 2, all object names had been limited to just 30 bytes. This limitation lead to some interesting problems, especially if you wanted to use descriptive names for the database objects you were creating.

Take for example the dictionary tables we wanted to create in 12.1 to help manage SQL Plan Directives. The first table was DBA_SQL_PLAN_DIRECTIVES, with 23 characters, which wasn’t problem. However, the second table we wanted was DBA_SQL_PLAN_DIRECTIVES_OBJECTS.

Continue reading

Posted in New Features, Oracle Database 12c New Features | Tagged , | 2 Comments

12.2 and Tim Tams

Yesterday saw Oracle complete the release of Oracle database 12c Release 2 when the software became available for download on Oracle.com for both Linux & SPARC.

Earlier this year I was lucky enough to have an opportunity to grab a coffee and enjoy some Tim Tams (cookies) with Connor McDonald, my fellow askTOM team member, and chat about our favorite new features in 12.2. I thought you might enjoy see what we had to say about the new features and the enhancements that could make your life a lot easier.

Happy viewing!

Posted in AskTom, Oracle Database 12c New Features | Tagged , | 1 Comment

Oracle Database 12c Release 2 Available Everywhere!

Today Oracle Database 12c Release 2 became available for download on Oracle.com for both Linux & SPARC. So anyone who wasn’t ready to try 12.2 in the Cloud can now play around with it, in the comfort of their own environment.

With each new release of the Oracle Database come fundamental architectural changes, driven by new technologies and user requirements. This has never been more evident than with Oracle Database 12c, which has 3 marquee features:
Continue reading

Posted in Database In-Memory, Multitenant, Oracle Database 12c New Features, Sharding | Tagged , , , | 2 Comments

Oracle Storage Index

If you are on Exadata or taking advantage of Database In-Memory it’s possible your queries will benefit for the automatically created and maintained Storage Indexes.

But what exactly are Storage Indexes and why don’t I always see a benefit from them?

Let me start by describing what Storage Indexes are in relation to Database In-Memory but remember they behavior in exactly the same way on the Exadata storage cell.

A Storage Index keeps track of minimum and maximum values for each column in an In-Memory Compression Unit (IMCU) or 1MB chunk on the Exadata storage cells. When a query specifies a WHERE clause predicate, the In-Memory Storage Index on the referenced column(s) is examined to determine if any entries with the specified value exist.

Continue reading

Posted in AskTom, Database In-Memory, Exadata, Oracle Database 12c New Features | Tagged , , , , | 4 Comments

Histogram sample size and Online Statistics Gathering

I got a great follow up question to my earlier blog on Online Statistics Gathering; that I thought might be of interest to other and worthy of a short post of its own.

The question related to sample size used to gather histograms on a table that originally had its statistics gathered via a direct path load operation.

Let’s look at any example:

Continue reading

Posted in AskTom, Optimizer, Oracle Database 12c New Features | Tagged , , | 1 Comment

Oracle releases new Exadata SL6

Earlier this week, Oracle officially released the newest edition in the Exadata family, the Exadata SL6. The SL6 is an Exadata where the x86 database servers have been replaced by SPARC Servers. It runs the same Exadata software and Oracle Linux as the x86 version and it has the identical interfaces and APIs, so an application or end-user won’t actually know the difference.

For me the real benefit of this solution is the chance to run Oracle Database 12c on the SPARC M7 processor.

What’s so great about the SPARC M7 chip?

Continue reading

Posted in Exadata | Tagged , , , | 2 Comments

Upcoming Events : RMOUG 2017

I’m really looking forward to the Rocky Mountain Oracle User Groups Training days next week, at the Colorado Convention Center, as it will bring several new challenges for me.  I am going to be involved in three very different sessions at this year’s conference:

Continue reading

Posted in Events | Tagged , | Leave a comment