They're two ways to handle "bucketing data" in Oracle. In the documentation Oracle describes the two types as either having equiwidth or or equiheight histograms.
Equiwidth (NTILE): Each bucket will have the same number of items in it with some buckets having at most 1 more item than other buckets. An easy way to think of this concept is to order all the items, then divide the data evenly into groups based on the number of buckets. For example, supposed you have 9 values (AAAABCDEF) and wanted to put them into 3 buckets. the buckets would be B1 = AAA, B2 = ABC, B3 = DEF. You'll notice that the value "A" is in both buckets B1 and B2.
When an uneven amount of objects need to go into the buckets, NTILE will fill the first bucket first, second bucket second, etc. For example, suppose you had 10 values (AAAABCDEFG) and wanted to fill them into 3 buckets. (Note this is similar to the previous example with an additional "G"). The buckets would be B1 = AAAA, B2 = BCD, B3 = EFG.
Equiheight (WIDTH_BUCKET): This will take the min and max range, divided by the number of buckets and place each value in it. For example, the salaries in the EMP table range from 801 ~ 5000. If you set the min/max range from 0~5000 3 buckets will be created. All values from 0~1,666 will go into B1, values from 1,6667~3333 into B2, and values 3334 to 5000 into B3. (Note: for simplicity I took out decimals in this split).
An easy way to think of these two bucketing methods is that NTILE divides values based on the number of items. WIDTH_BUCKET divides values based on their values.
Here's an example that highlights the difference between the two functions.
with data as ( -- using this as data input select 3 as num_buckets from dual ) select d.dname, e.ename, e.sal, ntile (data.num_buckets) over (order by sal asc) ntile, width_bucket(sal, 0, max(sal+1) over (), data.num_buckets) width_bucket from emp e, dept d, data where e.deptno = d.deptno order by sal; DNAME ENAME SAL NTILE WIDTH_BUCKET -------------- ---------- ---------- ---------- ------------ RESEARCH SMITH 801 1 1 SALES JAMES 950 1 1 RESEARCH ADAMS 1100 1 1 SALES WARD 1250 1 1 SALES MARTIN 1250 1 1 ACCOUNTING MILLER 1300 2 1 SALES TURNER 1500 2 1 SALES ALLEN 1600 2 1 ACCOUNTING CLARK 2450 2 2 RESEARCH JONES 2975 2 2 RESEARCH SCOTT 3000 3 2 RESEARCH FORD 3000 3 2 SALES BLAKE 3850 3 3 ACCOUNTING KING 5000 3 3
It's important to note that WIDTH_BUCKET is not an analytic function but NTILE is. For more information read the documentation for each function. For WIDTH_BUCKET, the documentation covers what happens with values outside the min/max range (they go into bucket 0 and num_buckets+1).
