Learning is not a spectator sport

Complete listing of posts

  • Equi-sized partitions on random data

    Had an interesting AskTom question today about dynamically keeping partition sizes in check. The poster had large table, already partitioned, but since there was no logical column to do a range partition on, it was partitioned on a surrogate primary key value. Due to the volatility of the data, (with a BLOB column on the… Read more

  • Something new learned every day

    One of the reasons I leapt at the chance to be on the AskTom team when we resurrected the site, was that it’s like free training.  You get questions on topics you have not visited before, and you get new angles on things you thought you already knew. Just today, someone posted a question about… Read more

  • On building SQL

    I had a fun question on AskTom over the weekend, that of, how to display a monthly calendar for any provided date using just SQL. You can see the question and the answer here But I thought it might be worth explaining the process.  Of course, the way I approach the problem might be totally… Read more

  • DEFAULT SEQ.NEXTVAL in 12c

    One of the cool things in 12c is that (finally after all these years) a sequence can be assigned as the default value for a column. Of course, some may say “big deal” – after all, it is trivial to create a simple trigger to do the job.  And perhaps, we “crafty” folks at Oracle… Read more

  • Never rely on an assumed order

    We’ve (hopefully) all had it drummed into us enough times – you cannot assume the order of results from queries unless you explicitly include an ORDER BY statement. Here’s another trivial example of this- I was doing a little demo script for an AskTom question. Here’s the script running in 11.2.0.4 SQL> create table T… Read more

  • EXCHANGE PARTITION revisited

    A while back I did a blog post showing that when you have set a column to UNUSED, it still “counts” as a validation step when doing exchange partition.  So if you had a partitioned table that previously had a column set to unused, then the candidate table to be exchanged in also had to… Read more

  • Public / private cursors

    As we all know (hopefully), we should always deploy named program units (procedures/functions) within packages. Its a great method of encapsulation of logic. However, not many are aware that its not just procedures and functions that can have a public (package spec) and private (package body) component. Cursors can also be defined in the same… Read more

  • AskTom–some thoughts on the future

    The structure of an AskTom question contains four elements: The original question Our answer A review, which can be posted by anyone And then we can opt to add a single Followup to any of those reviews We’re thinking of changing AskTom to allow greater flexibility, namely: The original question (unchanged) Our answer (unchanged) A… Read more

  • Bitwise operations

    The long existing BITAND function is now within the documentation, to let you do logical AND on two numbers, and is also available from PL/SQL   If you need other bit operations, a little boolean math should suffice Just make sure you stay within the limits of BINARY_INTEGER CREATE OR replace FUNCTION bitor( x IN… Read more

  • Auto-backups of PLSQL source

    I saw this on an ideas forum today and whilst most people would take care of this with a source code control system, its also true that people might do several compilations / tests with their PLSQL source before checking it in officially to their source repository. So the idea has some merit… and maybe… Read more

  • Forever is a long time

    It’s interesting that we still see lots of code out “in the wild” that leans itself to taking forever. Now I’m not talking here about poorly performing SQL etc…because (at least theoretically) no matter how bad the SQL is, presumably it will finish one day, even if that day is years into the future. What… Read more

  • Common GATHER_PLAN_STATISTIC confusion

    Most people already know about the very cool GATHER_PLAN_STATISTICS hint.  If not, you can see an example here But here’s a common cause of confusion when using it in SQL Plus: SQL> select /*+ gather_plan_statistics*/ count(p) from t where x > sysdate – 30; COUNT(P) ———- 0 SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’)) PLAN_TABLE_OUTPUT ———————————————————————————————-… Read more