用NTILE函数分组并发处理数据

oracle分析函数之NTILE

 

   NTILE函数对一个数据分区中的有序结果集进行划分，将其分组为各个桶，并为每个小组分配一个唯一的组编号。这个函数在统计分析中是很有用的。例如，如果想移除异常值,你可以将它们分组到顶部或底部的桶中，然后在统计分析的时候将这些值排除。ORACLE 数据库统计信息收集也使用NTILE函数来计算直方图信息边界。在统计学术语中，NTILE函数创建等宽直方图信息。

www.2cto.com  

建表语句：

Sql代码  

create table SMALL_CUSTOMERS(CUSTOMER_ID NUMBER,SUM_ORDERS  NUMBER);       

insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 10);       

insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 20);       

insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (1000, 30);       

insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 5);       

insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 10);       

insert into SMALL_CUSTOMERS (CUSTOMER_ID, SUM_ORDERS) values (800, 1);   

www.2cto.com  

具体用法：

Sql代码  

select customer_id,  

        sum_orders,  

        ntile(3) over(order by sum_orders) ntile  

   from small_customers t;  

 

      NTILE函数在诸如将总工作量在N个并行的进程中进行划分的实际应用中是很有用的，让我们假设你有3个平行的进程，你可以将工作量划分为3个桶并将每个桶放到一个进程当中。

比如删除一个大表：

1）获取匹配的ROWID列表，可用parallel这个hint提高查询速度
2）把ROWID排序并分组（例如分为16组），可以用NTILE函数分组

3）创建多个JOB（例如16个JOB），根据ROWID分别并行删除对应的组的数据。3800W行大概耗时2-3个小时，当然这和磁盘性能有关。