Learning is not a spectator sport

Complete listing of posts

  • FIRST_ROWS vs FIRST_ROWS_n

    You might be thinking that FIRST_ROWS is pretty much the same as FIRST_ROWS_1, since they are both after the first row. However, there are some important distinctions to make here. The traditional ‘FIRST_ROWS’ parameter is not a genuine cost-based option, more a hybrid between the rule and cost. This can be observed via the following… Read more

  • Histograms on data (not the optimizer)

    I’ve been doing a series on Analytics here but if you need to group data into ranges, you don’t necessarily need a fancy analytic. The function WIDTH_BUCKET can easily determine histogram groupings within a table. SQL> SELECT CUST_ID, CUST_LAST_NAME, CUST_CREDIT_LIMIT, 2 WIDTH_BUCKET(CUST_CREDIT_LIMIT, 100, 5000, 10) credit_bucket 3 FROM sh.customers 4 WHERE country_id = 52787 5… Read more

  • Automatic date formats

    Notice in all of the examples below that the date string does not match the format mask. When a date conversion fails, Oracle tries some similar formats to try succeed. This is actually documented as well here   Original Format Element Additional Format Elements to Try if Original fails ‘MM’ ‘MON’ and ‘MONTH’ ‘MON’ ‘MONTH’… Read more

  • SQL*Plus quick tip

    SQL Plus does not appear to be bothered by the direction of slashes, ie “/” or “\” in its scripts Hence as long as you have a “C:\tmp” folder on your Windows drive (or wherever your SQL Pus binaary is installed/running from), you can do: spool /tmp/blah @/tmp/my_file.sql and it will work on Unix and… Read more

  • SAMPLE costing

    People often think when using the SAMPLE clause, that because they are only dealing with a subset of the data, that immediately it should be a much faster operation. And whilst sometimes this is indeed the case, it is not a guarantee. The optimizer probably gives us a hint ( no pun intended) as to… Read more

  • Literals and VARCHAR2

    We all know that comparing two VARCHAR2 fields of different lengths will be treated by Oracle as being not EQUAL: SQL> drop table T purge; Table dropped. SQL> create table T ( x varchar2(10), y varchar2(10)); Table created. SQL> insert into T values (‘abc’,’abc ‘); 1 row created. SQL> select * from T where x… Read more

  • A old story from the past

    A old story from the past

    My own personal hell story was back in the late 1990’s, when moving database from one server to another.  We were recycling some of the hardware (disks and memory), so it was a unload-to-tape, reload-from-tape job. The servers were in Port Hedland (a rat-infested dusty 110-degrees-plus 98% humidity hell hole… Hi to anyone in Port… Read more

  • 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