请教newkid:避免使用union 或圈套循环的查询sql

xgghxkhuang

select * from (
  select * from t_bank_code
  where '123456789' like range||'%'
  order by rules desc
  )
  where rownum=1

metaking

xgghxkhuang 发表于 2012-3-15 12:17
select * from (
  select * from t_bank_code
  where '123456789' like range||'%'

这个结果没有问题，数据多时效率不高

xgghxkhuang

我的思路是这样的
1.因为range是字符，所以会按照字符排序
如果查询满足条件，一定满足
卡号  between     range     and  to_char(range+1)
2.我想通过上面的恒等式建立索引或函数索引，但是实践下来发现range+1>=卡号的条件索引能用上，但是卡号<=range的条件始终无法用上，
建议你提出思路，然后提交你们的开发dba，他们应该有手段。

xgghxkhuang

create table  rptdata.t_bank_code
     (bank_id varchar2(10),--bank_id
      rules number(10), --range μ?3¤?è
      range varchar2(15) --bank bin
      );
insert into rptdata.t_bank_code
select
dbms_random.string('A',9),
null,
'3'||to_char(trunc(dbms_random.value(0,99999999)))
from dual
connect by level<=1000000;
update rptdata.t_bank_code
set rules =length(range) ;
commit;
create index rptdata.vv_idx1 on rptdata.t_bank_code(to_char(to_number(range)+1),range DESC );
CREATE TABLE RPTDATA.SAMP
AS
SELECT AA FROM (
SELECT RANGE||LPAD(DBMS_RANDOM.value(0,TO_NUMBER(LPAD('9',(15-LENGTH(RANGE)),'9'))),15-LENGTH(RANGE),'0') AA
FROM  rptdata.t_bank_code vvv
ORDER BY DBMS_RANDOM.value)
WHERE ROWNUM<=10000
SELECT LPAD('m',10,'0') FROM DUAL;
--TEST
CREATE OR REPLACE PACKAGE TEST_TURN
IS
PROCEDURE P_IDX;
PROCEDURE P_FUL;
END;
/
CREATE OR REPLACE PACKAGE BODY TEST_TURN AS
procedure P_IDX IS
T_IDX RPTDATA.t_bank_code%ROWTYPE;
BEGIN
FOR AA IN (SELECT aa  CARD FROM  RPTDATA.SAMP WHERE ROWNUM<=100)
LOOP
SELECT *  INTO T_IDX FROM (
SELECT * FROM (
SELECT  /*+ index(vvv vv_idx1) */  *
FROM  rptdata.t_bank_code vvv
WHERE
to_char(to_number(range)+1) >=AA.CARD AND
RANGE  <=AA.CARD  )
WHERE substr(AA.CARD,1,rules)=range
ORDER BY RULES DESC )
WHERE ROWNUM=1;
END LOOP;
END P_IDX;
PROCEDURE P_FUL
AS
T_FUL RPTDATA.t_bank_code%ROWTYPE;
BEGIN
FOR AA IN (SELECT aa  CARD FROM  RPTDATA.SAMP WHERE ROWNUM<=100)
LOOP
SELECT * INTO T_FUL FROM (
SELECT * FROM  rptdata.t_bank_code vvv
WHERE substr(AA.CARD,1,rules)=range
ORDER BY RULES DESC )
WHERE ROWNUM=1;
END LOOP;
END P_FUL;
END TEST_TURN;
/
SET TIMING ON;
SET SERVEROUTPUT ON;
EXEC TEST_TURN.P_FUL;--100次75秒
EXEC TEST_TURN.P_IDX;--100次24秒
--结果完全符合要求
--方案:
create index rptdata.vv_idx1 on rptdata.t_bank_code(to_char(to_number(range)+1),range DESC );
--查询
SELECT *   FROM (
SELECT * FROM (
SELECT  /*+ index(vvv vv_idx1) */  *
FROM  rptdata.t_bank_code vvv
WHERE
to_char(to_number(range)+1) >=AA.CARD AND
RANGE  <=AA.CARD  )
WHERE substr(AA.CARD,1,rules)=range
ORDER BY RULES DESC )
WHERE ROWNUM=1;



     

newkid

SELECT * FROM (SELECT * FROM t_bank_code WHERE SUBSTR('305550123456789',1,rules)=range ORDER BY range DESC) WHERE ROWNUM=1;

lastwinner

与此相关的算法是字符串匹配，有个相关术语叫“模”，楼主可以查查相关的资料

xgghxkhuang

--方案:
create index rptdata.vv_idx1 on rptdata.t_bank_code(to_char(to_number(range)+1),range DESC );
--查询
SELECT *   FROM (
SELECT * FROM (
SELECT  /*+ index(vvv vv_idx1) */  *
FROM  rptdata.t_bank_code vvv
WHERE
to_char(to_number(range)+1) >=:CARD AND
RANGE  <=:CARD  )
WHERE substr(:CARD,1,rules)=range
ORDER BY RULES DESC )
WHERE ROWNUM=1;
楼主可以试验一下上面的方案，
to：newkid 你的方法数据肯定对的，但是楼主说数据量较大，你的这个消耗太多了，见前面的帖子。

metaking

xgghxkhuang 发表于 2012-3-16 09:24
--方案:
create index rptdata.vv_idx1 on rptdata.t_bank_code(to_char(to_number(range)+1),range DESC  ...

谢谢xgghxkhuang：
这个方法逻辑读减少很多，关键是索引建的比较好，基本一次定位到记录

xgghxkhuang

metaking 发表于 2012-3-16 09:46
谢谢xgghxkhuang：
这个方法逻辑读减少很多，关键是索引建的比较好，基本一次定位到记录

互相学习，欢迎常来。

xgghxkhuang

xgghxkhuang 发表于 2012-3-16 12:25
互相学习，欢迎常来。

to newkid：有一个问题我很纳闷，就是我明明建立了两个字段的索引，
但是使用索引的时候却只能使用到第一个索引字段：
即：1.acess的时候只能使用第一个字段
    2.第二个字段是filter的，这个方案我试验了几次得出的。
    这里面的filter和acess，基本概念还是不是很清楚。
    我使用的降序索引是想索引按一个顺序去扫描，我是参照另一本书，但是给出的执行计划还是不懂。
   如果按照我的逻辑acess的时候是访问两个字段的，另外一个不应该是filter的。