Oracle 插入数据效率对比

浏览数：30 / 时间：2015年06月12日

oracle插入数据有多种方式：

将从多个表中查出来的数据插入到临时表中

数据行数 5189597

1.传统方式：直接将数据插入到表中

 1 insert into LLB_BASIC_USER_D_TEMP_TEST
 2     select t.serv_id,
 3            t.phone_id,
 4            a1.loc_imei t,
 5            region_code,
 6            t.county_code,
 7            t.payment_mode_cd,
 8            t.plan_id,
 9            t.productflux,
10            t.allflux,
11            t.netuse / 1024 / 1024,
12            t.lj_sn_roam_flux / 1024 / 1024,
13            t.lj_sj_roam_flux / 1024 / 1024,
14            a.llb_jcb,
15            a.llb_jcb_flux,
16            a.llb_bd,
17            a.llb_bd_flux,
18            a.llb_gn,
19            a.llb_gn_flux,
20            a.llb_xs,
21            a.llb_xs_flux,
22            a.llb_xy,
23            a.llb_xy_flux
24       From llb_basic_temp1           t,
25            LLB_BASIC_PACK_ORDER_info a,
26            llb_phone_imei            a1
27      where t.phone_id = a.phone_id(+)
28        and t.phone_id = a1.accs_nbr(+);

View Code

耗时41秒

2.用Hint 提示减少操作时间

 1 insert /*+ Append*/ into LLB_BASIC_USER_D_TEMP_TEST
 2     select t.serv_id,
 3            t.phone_id,
 4            a1.loc_imei t,
 5            region_code,
 6            t.county_code,
 7            t.payment_mode_cd,
 8            t.plan_id,
 9            t.productflux,
10            t.allflux,
11            t.netuse / 1024 / 1024,
12            t.lj_sn_roam_flux / 1024 / 1024,
13            t.lj_sj_roam_flux / 1024 / 1024,
14            a.llb_jcb,
15            a.llb_jcb_flux,
16            a.llb_bd,
17            a.llb_bd_flux,
18            a.llb_gn,
19            a.llb_gn_flux,
20            a.llb_xs,
21            a.llb_xs_flux,
22            a.llb_xy,
23            a.llb_xy_flux
24       From llb_basic_temp1           t,
25            LLB_BASIC_PACK_ORDER_info a,
26            llb_phone_imei            a1
27      where t.phone_id = a.phone_id(+)
28        and t.phone_id = a1.accs_nbr(+);