代码之家 › 专栏 › 技术社区 › MeetJoeBlack

不同表上的复合索引ORACLE SQL

indexing oracle sql

MeetJoeBlack · 技术社区 · 10 年前

大家好,我需要优化这个查询:

  SELECT * 
    FROM 
        (SELECT r.*, ROWNUM RNUM 
        FROM (
            SELECT t0.RISK , t3.AMOUNT, t3.DATE_TIME , t0.ID
            FROM 
                REACTION.ALERT t0, REACTION.INVESTIGATION t1, 
                REACTION.CLASSIFICATION_TYPE t2, REACTION.TRANS t3, 
                REACTION.FRAUD_TYPE t4, REACTION.CARD t5 
            WHERE (
                    (NOT EXISTS (SELECT 1 FROM REACTION.INVESTIGATION WHERE REACTION.INVESTIGATION.ALERT_ID = t0.ID) OR 
                    t1.CLASSIFICATION_TYPE_ID IS NULL OR t2.CLASSIFICATION_TYPE = 2) AND 
                    t0.MODULE_TYPE = 0 AND
                    t0.PROCESSING_MODE_TYPE = 1 AND 
                    t0.ISS_INST IN (1201, 1101)
                ) AND 
                t0.ID = t1.ALERT_ID(+) AND 
                t0.TRANSACTION_ID = t3.ID AND
                t1.CLASSIFICATION_TYPE_ID = t2.ID(+) AND
                t1.FRAUD_TYPE_ID = t4.ID(+) AND
                t3.HPAN = t5.HPAN(+) 
                ORDER BY t0.RISK DESC, t3.AMOUNT DESC, t3.DATE_TIME DESC, t0.ID DESC
            ) r 
        WHERE ROWNUM <= 120)
    WHERE RNUM > 100;

但我如何按列对所有顺序使用索引( t0.RISK DESC,t3安装描述,t3日期时间描述,t0.ID描述 )? 我尝试创建两个索引:

create index risk_idx on  ALERT (risk,id);
create index amount_date_idx on  TRANS (AMOUNT,DATE_TIME);

但我在TRANS和ALERT表上仍然有FULL SCAN,但如果我将排序更改为 按t0.RISK DESC、t0.ID DESC排序 : 风险idx 索引工作,查询执行速度更快。此外,我还尝试对这4列中的每一列单独设置索引:

create index risk_idx on  ALERT  (risk,1);
    create index amount_idx on  TRANS  (amount,1);  
    create index date_time_idx on  TRANS  (DATE_TIME,1);

但这也无济于事( P.S.柱 ALERT.RISK, TRANS.AMOUNT , TRANS.DATE_TIME 有 NULLABLE=真 ;

1 回复 | 直到 10 年前

Ponder Stibbons 10 年前

我对查询进行了一些修改,并使用索引进行了扩展。

查询中的更改:

用ansi连接替换旧样式连接
内部查询计数行似乎可以替换为 not exists... 或者甚至检查 t1.alert_id is null
添加了分析row_number函数,而不是 order by 和过滤器基于 rownum s
我不确定与t4和t5的连接是否必要-如果与这些表有1对多的关系,那么它们是必要的,但如果一对多那么这些表对结果(t4和 t5不用于条件和顺序)

select r.*
  from (
    select t0.risk , t3.amount, t3.date_time , t0.id, 
        row_number() over (
          order by t0.risk desc, t3.amount desc, t3.date_time desc, t0.id desc) rnum
      from reaction_alert t0
        left join reaction_investigation       t1 on t1.alert_id = t0.id
        left join reaction_classification_type t2 on t2.id = t1.classification_type_id
        join reaction_trans                    t3 on t3.id = t0.transaction_id
        left join reaction_fraud_type          t4 on t4.id = t1.fraud_type_id  
        left join reaction_card                t5 on t5.hpan = t3.hpan         
      where 
        (
          t1.alert_id is null -- <- this should be enough to replace "group by query"
          or t1.classification_type_id is null 
          or t2.classification_type = 2
        ) 
        and t0.module_type = 0 and t0.processing_mode_type = 1
        and t0.iss_inst in (1201, 1101)
    ) r 
  where 100 < rnum and rnum <= 120
  order by rnum

这个查询可能需要一些修改,如果没有数据访问,我无法检查所有内容是否正确。语法是可以的,我在包含查询中提到的列的示例表上运行它。请检查您和我的查询中的计数(当然要删除rnum上的过滤器)。如果您看到任何逻辑错误,请更正。

索引

没有数据访问很难区分,但连接中使用的列显然是候选项。您可能在这些字段上有索引,因为它们似乎是主键或外键。无论如何,我会试试这些:

/* t0 */ create index idx_ra_cmplx1   on reaction_alert (module_type, processing_mode_type, iss_inst);
/* t1 */ create index idx_ri_alert_id on reaction_investigation (alert_id);
/* t2 */ create index idx_rct_id      on reaction_classification_type (id);
/* t3 */ create index idx_rt_id       on reaction_trans (id);
/* t3 */ create index idx_rt_cmplx1   on reaction_trans (id, amount desc, date_time desc);
/* t4 */ create index idx_rft_id      on reaction_fraud_type (id);
/* t5 */ create index idx_rc_hpan     on reaction_card (hpan);

下面是我如何构建表来测试查询。不太重要,但可能对某人有用。

create table reaction_alert (id number, transaction_id number, risk number, module_type number, processing_mode_type number, iss_inst number)
create table reaction_investigation (alert_id number, classification_type_id number, fraud_type_id number);
create table reaction_classification_type (id number, classification_type number);
create table reaction_trans (id number, amount number, hpan number, date_time date);
create table reaction_fraud_type (id number);
create table reaction_card (hpan number);