1.ORACLE高效设计 理解SQL

1.ORACLE高效设计 理解SQL

 

SQL是一个强有力的语言。可以做很多工作。

执行如下：

SQL>col week format 99

Col U1 format a5

Col U2 format a5

Col U3 format a5

Col U4 format a5

Col U5 format a5

Col U6 format a5

Col U7 format a5

Col U8 format a5

SQL>with players as (select 'P' ||ROWNUM username

       from all_objects where ROWNUM <=8),

weeks as

       (select ROWNUM week

       from all_objects

       where ROWNUM <=7)

       select week,

              max(decode(rn,1,username,null)) u1,

              max(decode(rn,2,username,null)) u2,

              max(decode(rn,3,username,null)) u3,

              max(decode(rn,4,username,null)) u4,

              max(decode(rn,5,username,null)) u5,

              max(decode(rn,6,username,null)) u6,

              max(decode(rn,7,username,null)) u7,

              max(decode(rn,8,username,null)) u8

       from (select username,week,row_number() over (partition by week order by rnd) rn

              from (select username,week,dbms_random.random rnd from players,weeks)

              )

       group by week

;

这个 SQL是在8个参赛者在7周中随机分配给四个组中的两个。

有7行记录，对应7周。当使用数据库时，重要的是集合的思路。

       大数据量操作比单行的过程代码要快的多。如果想使从事务处理系统到报告系统的数据拷贝得更快，应使用单个SQL语句，而不是使用一行接着一行的过程处理。

1. ROWNUM伪列

ROWNUM是ORACLE的一个神奇的列。通常用来做三件事情

1. 调整查询
2. 通过查询标页号
3. 执行前-N处理

ROWNUM是可用在查询中的一个伪列，不是真正的列。

       使用ROWNUM，建立一个测试：

Create table t1 ( prc_chk_key number(9) not null,

Prod_key number(12) not null,

Cmpt_loc_key number(5) not null,

Loc_key number(5) not null,

Prc_chk_dt date

);

SQL>insert into t1 select 2,3,4,5,sysdate from all_objects where rownum<=50;

Create table t2 (

prc_chk_key number(9) not null,

prc_chk_typ_desc varchar2(35) not null,

Cmpt_loc_key number(5) ,

Loc_key number(5) not null

);

SQL> insert into t2 select 2,'x',4,5 from all_objects where rownum <= 50;

创建一个函数：

sys@HAMMER>create or replace function F

    ( v_prod_key in number default NULL,

    v_prc_chk_key in number default null,

    v_return in varchar2 default null,

    v_want_sr in varchar2 default null,

    v_version in number ) return varchar2

    as

    begin

      dbms_application_info.set_client_info

      (userenv('client_info')+1);

      return 'x';

   end;

   /

开始测试。

sys@HAMMER>exec dbms_application_info.set_client_info(0);

PL/SQL 过程已成功完成。

sys@HAMMER>select /*+ use_hash( a11,a12) */

    a12.prc_chk_typ_desc prc_chk_typ_desc,

    a11.prc_chk_dt prc_chk_dt,

    a11.cmpt_loc_key cmpt_loc_key,

    a11.prod_key upc_prod_key,

    a11.loc_key loc_key,

    max(F(a11.PROD_KEY,a11.PRC_CHK_KEY,'QTY','D',1)),

    max(F(a11.PROD_KEY,a11.PRC_CHK_KEY,'AMT','D',1)),

    max(F(a11.PROD_KEY,a11.PRC_CHK_KEY,'CODE','D',1)),

   max(F(a11.PROD_KEY,a11.PRC_CHK_KEY,'PRC','D',1))

   from t1 a11, t2 a12

   where a11.cmpt_loc_key=a12.cmpt_loc_key

   and a11.loc_key = a12.loc_key

   and a11.prc_chk_key=a12.prc_chk_key

   group by a12.prc_chk_typ_desc,a11.prc_chk_dt,

   a11.cmpt_loc_key,a11.prod_key,a11.loc_key;

a11.cmpt_loc_key,a11.prod_key,a11.loc_key;

 

执行计划

----------------------------------------------------------

Plan hash value: 2197463864

 

----------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |    50 |  5950 |     6  (34)| 00:00:01 |

|   1 |  SORT GROUP BY      |      |    50 |  5950 |     6  (34)| 00:00:01 |

|*  2 |   HASH JOIN         |      |    50 |  5950 |     5  (20)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| T1   |    50 |  3050 |     2   (0)| 00:00:01 |

|   4 |    TABLE ACCESS FULL| T2   |    50 |  2900 |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------

SQL>set autotrace off

sys@HAMMER>select userenv('client_info') data from dual;

DATA

-------------------------------------------------------------

10000

表明，对F调用了10000次。