oracle 优化问题

      这几天被派到另外一个项目组帮忙，在工作过程中，感触最深的是海量数据的处理。以前一直以为自己接触的数据量就够大了，到了这个项目组，才发现，原来的数据量和这边差太远了。这边的数据量才算得上海量数据。

       接触到海量数据，就遇到一个问题，就是海量数据的处理。我的主要工作就是根据逻辑对表进行增删改查，逻辑不难，但是难的是，数据量简直太大了，数据根本就跑不动。就比如上周四，我写了一个表对抽的sql，从A表将数据完全转移到B表，并且表结构完全相同，按理说sql语句相当简单，但是我跑了一天没有跑出数据。

       情况：A表大概有3.7亿数据，20多个字段，完全直抽到B表中，sql语句如下：

       Insert into A select * from B;

       上午跑了四个小时以后，就停掉了，下午开了并行，运行了两个小时后仍然没有跑出，又停掉，然后回滚了很长时间，一天8个小时，没有跑完一个sql语句，最后终于在牛人的帮助下优化了一下sql，终于完成了跑数，用时一个小时。

       优化后的sql语句：

      insert /*+ append */

       into B

      select /*+ parallel(t,32) parallel_index(t,32) */

      from A t；

       通过这几天不断的处理海量数据，数据量过大，根本就运行不下去，一个语句经常会运行一两个小时后看不到任何希望，只能想办法优化。现总结一下自己接触到的优化方法。

       1.开启并发

          并发应该是和服务器有关系还有数据量。如果服务器有比较空闲的资源，多cpu多内存，就可以开并发。   但是数据量小的表不要开并发，开并发反而慢

        示例：

         select /*+ parallel(t,32) parallel_index(t,32) */ from A t；

      2.建立分区

         oracle分区有四种： 1 .range分区  2.list分区   3. hash分区   4 .复合分区

         list分区示例：

 create table test

  (

     CUSTNO         VARCHAR2(22) not null,

     DEPTID            CHAR(4)

   )

 partition by list (DEPTID)

  (

    partition PART_0000 values ('0000')

    tablespace test_1

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

      initial 64K

      minextents 1

      maxextents unlimited

    ),

  partition PART_1000 values ('1000')

    tablespace test_1

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

      initial 64K

      minextents 1

      maxextents unlimited

    ),

  partition PART_2000 values ('2000')

    tablespace test_1

    pctfree 10

    initrans 1

    maxtrans 255

    storage

    (

      initial 64K

      minextents 1

      maxextents unlimited

    )

  )

3.建立索引

索引是表的一个概念部分,用来提高检索数据的效率.

示例：

create  index  index_1 on A(Ano) local parallel ;

4.优化sql小技巧

    SELECT子句中避免使用 ‘*‘

    减少访问数据库的次数

    使用DECODE函数来减少处理时间

    用TRUNCATE替代DELETE

    用EXISTS替代IN

5.批量提交

示例一：

truncate table t;

 

DECLARE

   v_count NUMBER;

 BEGIN

   FOR cur IN (SELECT * FROM t_ref) LOOP

     INSERT INTO t VALUES cur;

     v_count := v_count + 1;

     IF v_count >= 100 THEN

       COMMIT;

     END IF;

   END LOOP;

   COMMIT;

 END;

示例二：

DECLARE

   CURSOR cur IS

     SELECT * FROM t_ref;

   TYPE rec IS TABLE OF t_ref%ROWTYPE;

   recs rec;

 BEGIN

   OPEN cur;

   WHILE (TRUE) LOOP

     FETCH cur BULK COLLECT

       INTO recs LIMIT 100;

     FORALL i IN 1 .. recs.COUNT

       INSERT INTO t VALUES recs (i);

     COMMIT;

     EXIT WHEN cur%NOTFOUND;

   END LOOP;

   CLOSE cur;

 END;