Category: Uncategorized

Automatic sequences not being dropped

One of the nice new things in 12c was the concept of identity columns. In terms of the functionality they provide (an automatic number default) it is really no different from anything we’ve had for years in the database via sequences, but native support…

The phantom tablespace

(Cueing my deep baritone Morpheus voice…) What if I told you that you can reference non-existent tablespaces in your DDL? OK, it sounds like a gimmick but there is a real issue that I’ll get to shortly. But first the gimmick I’ve created a…

IRR function in PLSQL

We had an AskTOM question recently about how to calculate the IRR, ie, the Internal Rate of Return. To be honest, I had not really heard of the function, but readers were quick to point out to me that it was a commonly used…

You need 10 minutes! Not 5, not 15, not 7

What follow is just my opinion….Well duh, it’s a blog, of course it’s just my opinion, but I’ll try back up my rantings and pontificating with some reasoned arguments. Whether at the end of it, you still call my claims total BS is of…

Happy Thanksgiving!

Just a quick blog post from Wellington New Zealand where we have just wrapped up the 2018 APAC Groundbreakers tour. It was a great way to finish the event with a small but enthusiastic crowd here in New Zealand. Obviously our American counterparts in…

18c and the ignoring of hints

  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…

Add ORDER BY to make ANY query faster

Yes it’s SCBT day here in Perth! SCBT = Silly Click Bait Title This post is just a cautionary tale that it is easy to get caught up judging SQL performance solely on a few metrics rather than taking a more common sense approach…

Interval versus Range partitions

One of the nice things about partitioning in the database is that partition pruning can quickly eliminate the requirement to read large amounts of data when the partitioning key(s) are appropriately provided in your queries (and DML). This also extends to queries where the…

The Openworld Mega-Download!

If you are sad and pathetic enthusiastic and interested in content like me, once a large conference like OpenWorld is over, I like to have the content (slides etc ) from not just the sessions I attended, but all of the sessions I could…

The strange place for INHERIT PRIVILEGES

A while back in an Office Hours session, I touched on a relatively new privilege in the database called INHERIT PRIVILEGES which is designed to avoid erroneous privilege escalation via AUTHID CURRENT_USER routines. You can watch the full video below But in a nutshell,…

OpenWorld is done!

The OpenWorld conference is over for another year. It’s always a thrill, albeit a hard-working thrill, to visit San Francisco and be a part of such a huge event. I’ll have a full wrap up post and video soon, but I wanted to get…

18c XE–running locally

There’s already a few blog posts out there, showing people (easily) getting their 18c XE instance downloaded, installed and running, so I won’t rehash that here. For the majority of people, I’d imagine they’ll have their XE instance running on a small box and…