Learning is not a spectator sport

Complete listing of posts

  • 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

  • The Fractional Part of a TIMESTAMP. Be careful.

    The Fractional Part of a TIMESTAMP. Be careful.

    This discovery came in from an AskTom customer but I figured it’s worth passing on to any PL/SQL programmers out there. The FF element of the format mask is for the fractional part of a second, and thus only applies to the TIMESTAMP series of data types. A timestamp can contain up to 9 digits… Read more

  • Implementing dynamic partitions AND subpartitions

    Implementing dynamic partitions AND subpartitions

    One of the cool things to come out of the partitioning option many moons ago was the feature known as interval partitioning. As a quick reprise/refresher, in times gone by, many a DBA has had that fateful call in the middle of the night because someone had tried to insert a row in a partitioned… Read more

  • DBMS_OUTPUT … A (formatting) love story

    DBMS_OUTPUT … A (formatting) love story

    What happens when you want to serenade your partner with a romantic ditty, but you’re a database professional as well? Well obviously you would put those lyrics to paper in the only way you know how to as a true database geek…and that of course is DBMS_OUTPUT! But needless to say, your hopes of impressing… Read more

  • My poor developers! PLSQL is going to be so hard on them!

    My poor developers! PLSQL is going to be so hard on them!

    So I started all of this by talking about how PLSQL is going to give you a performance boost, just by moving some of that SQL code into the database and out of your Java (Rust, Go, Javascript, Python, insert any app tier language here). People then called me out on my benchmark setup, so… Read more

  • A little bit of PLSQL and a little bit of network

    A little bit of PLSQL and a little bit of network

    So after my post yesterday about PL/SQL being a no-brainer to get an easy boost to application performance, there was an interesting collection of responses on social media from both sides of the fence. From the “the database is the universe” camp, there was criticism that the 30% benefit of PL/SQL was in fact understating… Read more