Category: Uncategorized

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…

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,…

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…

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…

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…

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…

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…