最近一个证券朋友问我一个insert性能问题,一张表记录所有用户的用户编码和股票编码,但是这个股票编码用逗号拼接,他想把每个用户的每一个股票单独插入一张表里,用了循环游标的方法,一条条的拆分插入,耗时较久问有没优化空间。
其实这个性能问题核心点在于,每一条数据都要进行commit,需要转行批量提交。所以我直接告诉他,将游标批量放到集合,再操作集合提交,确实效率提高了很多,那么有没有更简单更高效的方法呢,有就是对整张表进行行转列。不多比比直接上测试代码
--创建多行的模拟数据
CREATE TABLE TEST_1 AS
SELECT 'A' NAME,'1,2' CODE FROM DUAL
UNION ALL SELECT 'B','3,4' FROM DUAL;
--核心sql
SELECT NAME
,REGEXP_SUBSTR(CODE, '[^,]+', 1, rn)
FROM TEST_1
,(SELECT LEVEL rn
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(regexp_count(CODE, ',' )+1) FROM test_1));
这个是不是很简单,只需一个sql就搞定了,当然数据量太大,建议还是老实用批量的方法分批操作,不然撑爆临时表空间就不好了。