Learning is not a spectator sport

Complete listing of posts

  • 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

  • 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