Learning is not a spectator sport

Complete listing of posts

  • The DBMS_REDEFINITION trap

    The DBMS_REDEFINITION trap

    Here’s something to be careful with when it comes to using DBMS_REDEFINITION. Let’s start with the following scenario. I have a table called REDEF1 and the primary key is the OBJECT_ID column. SQL> create table redef1 as 2 select 3 owner 4 ,object_name 5 ,subobject_name 6 ,object_id 7 ,data_object_id 8 ,object_type 9 from dba_objects 10 Read more

  • Natural Keys versus Surrogate Keys….Here we are again!

    Natural Keys versus Surrogate Keys….Here we are again!

    Well….that blew up! 😀 Last week I published a video (which you can watch below if you haven’t seen it) about the evolution of the ROWID. One particular demo in the video shows how the ROWID for a given row might change simply by issuing an UPDATE statement. Unsurprisingly, Twitter (as Twitter often does) totally Read more

  • A simple benchmark harness

    A simple benchmark harness

    At my physical design talk at this year’s Yatra tour in India, one of the demos I do is multiple SQL*Plus sessions all smashing transactions at the database in order to simulate a high volume, high concurrency environment. After the session, one of the attendees reached out and asked for some details on how I Read more

  • Powerpoint hack for tech presenters

    Powerpoint hack for tech presenters

    Often when you are building a slides for a presentation you need to animate something on screen to show its transition from one state to another. For example, for a presentation I recently gave in India, I needed to show the state of rows in a block when that block was sparsely populated but I Read more

  • Juice up your JSON on Autonomous

    Juice up your JSON on Autonomous

    As many readers will be aware, we introduced a cool new JSON datatype in 21c which offers more features and better performance for JSON related features. Of course, whilst a new datatype is all well and good for 21c, many of us are not planning on moving to 21c because 19c is the long term Read more

  • Digging deeper into SQL under APEX

    Digging deeper into SQL under APEX

    For the many cases, simply having an EXPLAIN PLAN function will do the job when it comes to tuning SQL. However, from time to time, you want to dig a little deeper and with APEX, that takes a little more care. Because APEX is a metadata-driven low code environment, the query you wrote to build Read more

  • PL/SQL – choosing the BEST data type

    PL/SQL – choosing the BEST data type

    Just a little bit of Friday fun with PL/SQL data types for this post. Let’s explore the performance differences when it comes to NUMBER, PLS_INTEGER, INTEGER, BINARY_INTEGER and SIMPLE_INTEGER. With each data type, I’ll create a simple procedure and execute it a few times (this done on 19.15) so we can see the cost of Read more

  • Kscope 22 – the joy of in-person conferences

    Kscope 22 – the joy of in-person conferences

    Rather than bore you with a lengthy tome about the event, I’d thought I would summarise the week with pictures and some brief commentary. TL;DR: There was a conference-wide joy and euphoria about finally being back to meeting people face to face. First was the 45 hours door to door to get to Dallas. This Read more

  • Comparing two database objects for differences

    Comparing two database objects for differences

    Ask an Oracle practitioner what DBMS_METADATA is used for, and they’ll (correctly) tell you it used to retrieve the DDL for an object. In fact, if you’re using any of the standard Oracle tools – SQL Developer, SQLcl, DataPump etc, you’ll quickly recognise the structure and format of the output. Just about every tool I Read more

  • Recycling saves the planet but maybe not your database

    Recycling saves the planet but maybe not your database

    The recyclebin is a very cool feature in the Oracle Database, and a great “Get of our jail free” card to have in your DBA toolkit. I always recommend that customers should go with the defaults and always have it enabled to have that last resort option to bring a segment back to life with Read more

  • Is my table marked for archive?

    Is my table marked for archive?

    In 12c, we introduced a feature call Row Archival, which is a means of integrating typical archival models that we used to build with “home grown” solutions into the database. In effect, you can mark a set of rows in a table as “archived” and even though they are retained in the table, they are Read more

  • Version 7 was so much better…

    Version 7 was so much better…

    Full disclosure: This is a rant post. If you’re not into ranting and pontificating, perhaps best to move along now So it’s just another typical day on the interwebs, because there’s always a post, or a comment, or a tweet, where someone chips in with: “You know, Oracle 7 was the best version of the Read more