Tuesday, August 07, 2012

WIDTH_BUCKET


This example was tested on Oracle 11.2. It shows the use of the WIDTH_BUCKET function, which I believe was introduced in Oracle 9. First I created a table and put some numbers in it:

SQL> create table andrew (col1 number)
  2  /

Table created.

SQL> insert into andrew values (999)
  2  /

1 row created.

SQL> insert into andrew values (1000)
  2  /

1 row created.

SQL> insert into andrew values (1999)
  2  /

1 row created.

SQL> insert into andrew values (2000)
  2  /

1 row created.

SQL> insert into andrew values (2999)
  2  /

1 row created.

SQL> insert into andrew values (3000)
  2  /

1 row created.

SQL> insert into andrew values (3999)
  2  /

1 row created.

SQL> insert into andrew values (4000)
  2  /

1 row created.

SQL> insert into andrew values (4999)
  2  /

1 row created.

SQL> insert into andrew values (5000)
  2  /

1 row created.

SQL>

Then I queried the table using the WIDTH_BUCKET function. In the example, I asked for values between 1000 and 4999 to be split into 4 equal width buckets. So values from 1000 to 1999 (both inclusive) should go into bucket 1, values between 2000 and 2999 (both inclusive) should go into bucket 2 and so on. Oracle then puts values less than 1000 into bucket 0 and values >= 4999 into bucket 5. I believe this is expected behaviour but the Oracle documentation does not make it very clear. For example, the text of the documentation for Oracle 10.1 says:

Underflow Bucket

The function also creates (when needed) an underflow bucket numbered 0 and an overflow bucket numbered num_buckets+1. These buckets handle values less than min_value and more than max_value and are helpful in checking the reasonableness of endpoints.

However, the documentation for Oracle 10.2 includes a worked example. This shows values, which equal the max_value, going into the overflow bucket. My example below shows this too:

SQL> select col1, width_bucket(col1,1000,4999,4)
  2  from andrew
  3  order by col1
  4  /

      COL1 WIDTH_BUCKET(COL1,1000,4999,4)
---------- ------------------------------
       999                              0
      1000                              1
      1999                              1
      2000                              2
      2999                              2
      3000                              3
      3999                              3
      4000                              4
      4999                              5
      5000                              5

10 rows selected.

SQL>

No comments: