Learning is not a spectator sport

Complete listing of posts

  • Kris Kringle the Database – Know your ABC

    Kris Kringle the Database – Know your ABC

    If you open up the SQL reference manual and scroll down through the commands… …you will start at ADMINISTER and eventually you will land on UPDATE, the last SQL statement in the documentation. But here’s a pop quiz for you. Is there a command that comes after UPDATE that didn’t make it into the docs? Read more

  • Kris Kringle the Database – Get the HINT

    Kris Kringle the Database – Get the HINT

    One of my favourite quotes from Maria Colgan is: “Optimizer hints should only be used with extreme care” Let me give you an example of why that is the case. As anyone would know, one of the key aims of anyone building an application running on a database is the minimize risk. We have things Read more

  • Kris Kringle the Database – Getting the BAND together

    Kris Kringle the Database – Getting the BAND together

    Ever heard of a BAND join? It’s not actually new, it has been around since 12.2. Jonathan Lewis wrote up a nice post on it was back in 2017 but in a nutshell MERGE join done without a band join SQL> select 2 e1.last_name|| ‘ has salary roughly between ‘|| e2.last_name comparison 3 from 4 Read more

  • Kris Kringle the Database – Sequence Values

    Kris Kringle the Database – Sequence Values

    It is commonplace to use a sequence to populate a surrogate key in your table. Whether its a manually created sequence or an IDENTITY column (which is still backed by a sequence), most of us know that we can use the RETURNING clause to collect the value that was used on INSERT. SQL> insert into Read more

  • Kris Kringle the Database – Why NVL is smart

    Kris Kringle the Database – Why NVL is smart

    NVL smarts A common issue for reports and any query where users can pass parameters is how to handle the “optional” parameter. Here’s a typical example: “Table CP can be queried where column X is equal to optional parameter P.” Should we code: select * from CP where ( X = 😛 or 😛 is Read more

  • Kris Kringle the Database – A Foreign Foreign Key Option

    Kris Kringle the Database – A Foreign Foreign Key Option

    You’re probably familiar with the ON DELETE CASCADE option in a foreign key. For example, if I have DEPT and EMP as per below SQL> select * from dept; DEPTNO DNAME LOC ———- ————– ————- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON SQL> select * from emp order by Read more

  • Kris Kringle the Database – The BEST error

    Kris Kringle the Database – The BEST error

    Normally, whenever you get an error it can be cause for concern. But not this one. Let me present to you – the best error message in the entire Oracle documentation library! Tragically, someone evidently didn’t like this little bit of humour because it is now missing from the more recent documentation references Bah humbug Read more

  • Kris Kringle the Database – The Lazy Date

    Kris Kringle the Database – The Lazy Date

    In the life of a developer, keystrokes matter, and “he/she who dies with the least keystrokes wins”.😃 So it can be frustrating when every time you want to enter a date and you want to make sure you are not beholden to the whim of the session NLS settings , then you need to specify Read more

  • Kris Kringle the Database – Magic Dates

    Kris Kringle the Database – Magic Dates

    As we all know, when using TO_DATE, you need to make sure that your literal string matches the format mask, otherwise you get an error. For example SQL> select to_date(’01/01/2000′) from dual; select to_date(’01/01/2000′) from dual * ERROR at line 1: ORA-01843: not a valid month SQL> select to_date(‘January 1st 2020′,’DD-MON-YY’) from dual; select to_date(‘January Read more

  • Kris Kringle the Database – GROUP BY out of sorts?

    Kris Kringle the Database – GROUP BY out of sorts?

    Work colleague Chris Saxon published a post about GROUP BY not guaranteeing that a sort would occur, and concluded (correctly) that the only way to ensure that a query result will be sorted is to have an ORDER BY. Modern versions of the database will use a hashing mechanism to perform a GROUP BY. If Read more

  • Kris Kringle the Database – Go higher with hierarchies

    Kris Kringle the Database – Go higher with hierarchies

    Most of us are familiar with the CONNECT BY syntax to navigate a hierarchy and the SYS_CONNECT_BY_PATH to see all of the nodes from root to leaf. SQL> select SYS_CONNECT_BY_PATH(ename,’-‘) full_tree 2 from emp 3 start with mgr is null 4 connect by prior empno = mgr FULL_TREE ————————— -KING -KING-BLAKE -KING-BLAKE-JAMES -KING-BLAKE-ALLEN -KING-BLAKE-WARD -KING-CLARK Read more

  • Kris Kringle the Database – Index Compression

    Kris Kringle the Database – Index Compression

    There is an assortment of compression options available with the Advanced Compression option, but perhaps you are not in the position to invest in that additional license. However, did you know that you can use basic index compression in any version of the database, create index emp_ix on emp ( deptno, empno) compress 1; would Read more