两道SQL性能优化题

[碧水幽幽泉]

问题1.表结构和数据如下：
帐号 交易金额 交易币种
AC_NO AMOUNT TX_CCY
10001 100 HKD
10001 200 USD
10002 300 HKD
10003 400 USD
10003 500 OTHER
其中OTHER表示除HKD和USD之外的币种。
要求显示的结果如下：
10001 100 HKD
10001 200 USD
10001 0 OTHER
10002 300 HKD
10002 0 USD
10002 0 OTHER
10003 0 HKD
10003 400 USD
10003 500 OTHER

注意SQL语句的优化：表数据超过1000w条。

问题2.表结构和数据如下：
帐号 支票本编号 支票号范围
AC_NO CHQ_ID CHQ_RANGE
601001 1 100-150
601001 2 200-300

要求结果显示如下：
AC_NO CHQ_ID CHQ_NUMBER
601001 1 100
601001 1 101
。。。。。
601001 1 150
601001 2 200
601001 2 201
。。。。
601001 2 300

注意SQL语句的优化：表数据超过500w条。

自己也能实现以上效果：这里希望大侠们能给出性能较高的SQL语句，先谢谢了！

[陈灬风]

条件列建索引

[z123zjf]

先把你的实现SQL贴出来呀。不然怎么知道谁优

[小灰狼W]

第一个，将币种列表与数据表外连接即可 表的数据有1000万，但是正常情况下，不会要求查出所有数据，因为这没有意义。如果有过滤条件，过滤完以后再与币种表外连接 第二个， WITH NUMBERS AS (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 500) SELECT T1.AC_NO, T1.CHQ_ID, REGEXP_REPLACE(T1.CHQ_RANGE, '^([[:digit:]]+)\-([[:digit:]]+)$', '\1') + T2.RN - 1 CHQ_NUMBER FROM TAB T1, NUMBERS T2 WHERE REGEXP_REPLACE(T1.CHQ_RANGE, '^([[:digit:]]+)\-([[:digit:]]+)$', '\2') - REGEXP_REPLACE(T1.CHQ_RANGE, '^([[:digit:]]+)\-([[:digit:]]+)$', '\1') + 1 >= T2.RN ORDER BY 1, 2, 3; numbers根据取值范围来调整。和第一个问题一样，如果没有先筛选出少量的记录，而是将全表来连接，500万*100=5亿，怎么优化都没用

[越烟]

哥也来试着解答一下， 性能方面， 如果SQL的 吞吐量很大，很难优化。 一般的方式是分页 第一条结果 先把币种 存储在一张表中， 让后再 管理， 这种关联性能其实 还可以。 第二种， 这种需求是 sql 补充数据， SQL语句 with tab1 as( select 601001 AC_NO, 1 CHQ_ID, '10-15' CHQ_NUMBER from dual union all select 601002 AC_NO, 2 CHQ_ID, '20-30' CHQ_NUMBER from dual ), tab2 as(select tab1.*, to_number( regexp_substr(CHQ_NUMBER, '[^-]+',1, 1)) re1, to_number(regexp_substr(CHQ_NUMBER, '[^-]+',1, 2)) re12 from tab1 )select ac_no,chq_id, re1+level-1,chq_number from tab2 connect by level <=re12-re1+1 and prior ac_no = ac_no and ( prior dbms_random.value() ) is not null ;

[卖水果的net]

第一题：
你应该还有另外两张表，一个存 10001 这些数据，一个存 USD 这些数据 ；

SQL> 
SQL> create table type(id int , c varchar(10));
 
Table created
SQL> begin
  2    insert into type values(1,'HKD') ;
  3    insert into type values(2,'USD') ;
  4    insert into type values(3,'OTHER');
  5    commit ;
  6  end ;
  7  /
 
PL/SQL procedure successfully completed
SQL> create table base(AC_NO varchar(10)) ;
 
Table created
SQL> begin
  2    insert into base values('10001');
  3    insert into base values('10002');
  4    insert into base values('10003');
  5    commit;
  6  end;
  7  /
 
PL/SQL procedure successfully completed
SQL> create table data(AC_NO varchar2(10),AMOUNT int , TX_CCY varchar(10));
 
Table created
SQL> begin
  2  insert into data values('10001', 100 , 'HKD');
  3  insert into data values('10001', 200 , 'USD');
  4  insert into data values('10002', 300 , 'HKD');
  5  insert into data values('10003', 400 , 'USD');
  6  insert into data values('10003', 500 , 'OTHER');
  7  commit ;
  8  end ;
  9  /
 
PL/SQL procedure successfully completed
SQL> select nvl(type.c ,data.tx_ccy) tx_ccy ,
  2         nvl(data.AC_NO ,base.AC_NO) AC_NO,
  3         nvl(data.AMOUNT,0) AMOUNT
  4  from base cross join type
  5  left join data on type.c = data.tx_ccy and base.AC_NO = data.ac_no
  6  order by base.ac_no , type.id;
 
TX_CCY     AC_NO          AMOUNT
---------- ---------- ----------
HKD        10001             100
USD        10001             200
OTHER      10001               0
HKD        10002             300
USD        10002               0
OTHER      10002               0
HKD        10003               0
USD        10003             400
OTHER      10003             500
 
9 rows selected
SQL> drop table data purge ;
 
Table dropped
SQL> drop table type purge ;
 
Table dropped
SQL> drop table base purge ;
 
Table dropped
 
SQL> 

[zhangjjhao]

根据HKD'，USD') ，'OTHER')建立表分区