Learning is not a spectator sport

Complete listing of posts

  • Session level parallelism

    If you’ve got a stack of large I/O operations to perform, you might want to take advantage of the all of the cores on your server. However, if the scripts are already in place, you might not want to be editing them individually to carefully add parallelism. A little known command is that you can Read more

  • Problematic SQL ? PL/SQL is your friend.

    So then… I’ve written a crappy piece of SQL. It wouldn’t be the first time I’ve done it… and it probably won’t be the last time I do it But at least I’ve done one thing right…I’ve encapsulated the SQL inside a PL/SQL procedure. SQL> create or replace procedure P is 2 x int; 3 Read more

  • Not using binds .. banging an obsolete drum ?

    We’ve all seen the mantra – “you should be using binds”.  And this is a demo that’s been rolled out for decades now, showing the performance overhead of not using them: SQL> drop table T purge; Table dropped. SQL> create table T (x primary key) as 2 select rownum x from dual 3 connect by Read more

  • Converting LONG to CLOB

    Some folks still are stuck with LONG columns, and are keen to move to LOB.  Since version 9, we’ve had a nice facility to do that – just with a simple alter command. You can now simply issue “alter table (longcol CLOB)” to perform the conversion. This is a neat tool, but be aware of Read more

  • PL/SQL arrays–the index datatype

    You get some interesting (but perhaps not unexpected) results when playing with the speed of array functions in PL/SQL.  This is a series of tests comparing “BY PLS_INTEGER” arrays with “BY VARCHAR2” arrays.  In all the cases, their speed is pretty much blindingly fast, but the comparison between the two seems to be dependent on Read more

  • Certification exams

    This is an update of a blog item I made nearly 15 years ago … but I think it still holds true Is doing the OCP exams worthwhile ? Yes, but not for the reasons you may be thinking. The OCP exams are a relatively cheap way of identifying possible weaknesses in your knowledge base Read more

  • A little known ORDER BY extension

    Within a CONNECT BY statement, you can order siblings, that is, under a particular branch of the hierarchy, the child entries can be ordered, but you still preserve the hierarchy. SQL> select lpad(‘*’, level, ‘*’ ) || ename ename 2 from emp 3 start with mgr is null 4 connect by prior empno = mgr Read more

  • Multiple partitions

    In 12c, one of the nice changes to come along is that partition maintenance operations can now be done on multiple partitions, for example ALTER TABLE t1 MERGE PARTITIONS p01, p02, p03, p04 INTO p0; ALTER TABLE SPLIT PARTITION p0 INTO (PARTITION p01 VALUES LESS THAN (25), PARTITION p02 VALUES LESS THAN (50), PARTITION p03 Read more

  • AskTom–help us to help you

    On AskTom, obviously you’re are going to be posting code and/or code output.  It’s probably going to be the most vital part of the question’s test case so that we can help you out. So please dont forget the “<code>” and “</code>” tags around your question. Otherwise we get this – it’s all wrapped and Read more

  • Resolutions for 2016

    I remember at one of the first Mathematics lectures I attended at University, the lecturer demonstrated some principle (which I don’t recall, and certainly wouldn’t even comprehend now ), and then turned and said to the class: “Now THAT is a pretty exciting result”. Being fresh out of high school, and like most teenagers, thinking Read more

  • New Years Resolution–test cases

    If the following email came across your desk “I have two tables EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) and DEPT(DEPTNO,DNAME,LOCATION).  When I join them I’m getting multiple rows for the same employee” my question would be – how would you proceed ? What is the first thing you need to do to even begin to assist ? You need data.  Read more

  • Merry Christmas

    To the tune of “Let It Snow” Well, the response of the app is frightful, and users are getting spiteful. They claim the database is slow, “Make it go”, “Make it go”, “Make it go” It doesn’t show signs of locking, Yet the performance it still quite shocking, The CPU is right down low, but Read more