Composite index on different tables ORACLE SQL

Question

Hi guys, I need optimize this query:

  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;

But How I can use indexes on ALL order by colums (t0.RISK DESC, t3.AMOUNT DESC, t3.DATE_TIME DESC, t0.ID DESC)? I tryied to create 2 indexes:

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

But I still have FULL SCAN on TRANS and ALERT tables, but if I change sorting to ORDER BY t0.RISK DESC, t0.ID DESC: risk_idx index works and query execute faster. Also I tried to set index on each of that 4 columns individualy:

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);

But thath didnt help too( P.S. Columns ALERT.RISK, TRANS.AMOUNT , TRANS.DATE_TIME has NULLABLE = true;

Personally, I find it almost impossible to read the query. Try to reformat it and use proper explicit `join` syntax. The `join` conditions in the `where` clause just makes it even harder to know how to improve such a query. — Gordon Linoff, Feb 19 '15 at 13:31
isn't this nonsense? `( 0 = ( SELECT COUNT(*) ...` is `NOT EXISTS` clause. Why do you use ordering inside nested subquery? — ibre5041, Feb 19 '15 at 13:59
oh you don't I just read the query wrong way, but you should use `NOT EXISTS` anyway. — ibre5041, Feb 19 '15 at 14:00
Updated my sql query syntax and added NOT EXISTS, but problem still take a place — MeetJoeBlack, Feb 19 '15 at 14:03
So you join ALERT and INVESTIGATION twice. 1st you search for those ALERTS which do NOT have any INVESTIGATION (NOT EXISTS=anti join) and then for those which do have it (outer join). Both joins use the same joining condition. What if this query was rewritten into two separate queries and then use UNION ALL on them. — ibre5041, Feb 19 '15 at 14:08
I would remove the outer query too and change its inner query to `WHERE ROWNUM BETWEEN 100 AND 120` - might help a little, not sure — Hugh Jones, Feb 19 '15 at 14:16
@HughJones - that condition would never be true, [because of how and when `rownum` is assigned](http://stackoverflow.com/q/855422/266304). It coudl be changed to use `row_number()` instead though, but that woudl affect any stopkey optimisation. — Alex Poole, Feb 19 '15 at 14:29
@Alex. That's not how I read it - the query is triple nested, the sort is in the innermost query, the rownum is assigned in the middle query - surely that means the outer query is redundant — Hugh Jones, Feb 19 '15 at 15:13
@HughJones - the middle level can filter with `rownum <= 120` based on the sorting in the inner level, but it *cannot* do `rownum > 100`. To achieve that 20-row page size it includes the 1-120 rownum value in its select list (aliased as `rnum`, which is also necessary), and the outer query is then able to filter based on that `rnum` value - not on its own `rownum`, which will be 1-20. (It could filter on `rnum > 100 and rnum <=120` instead of filtering in the middle layer, but it still needs three layers). — Alex Poole, Feb 19 '15 at 15:37
@Alex - yes I was wrong. Thanks for putting it straight for me. — Hugh Jones, Feb 19 '15 at 15:43

score 0 · Accepted Answer · answered Feb 19 '15 at 16:20

I made some modifications in query and exeprimented with indexes.

Changes in your query:

replaced old style joins with ansi joins
it seems that inner query counting rows can be replaced with not exists... or even with checking if t1.alert_id is null
added analytic row_number function instead of order by and filters based on rownums
I am not sure if joins with t4 and t5 are necessary - if there is relation 1 to many with these tables then they are, but if one to one then these tables have no impact on result (columns from t4 and t5 are not used in conditions and order)

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

This query may need some modifications, I could not check if everything is correct without data access. Syntax is OK, I run it on sample tables containing columns mentioned in you query. Please check counts from your query and mine (of course removing filter on rnum). If you see any logic error please correct.

Indexes

It's hard to tell without data access, but columns used in joins are obvious candidates. You probably have indexes on that fields ase they seem to be primary or foreign keys. Anyway I'd try these:

/* 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);

And below is how I build tables to test query. Not too important, but may be useful for someone.

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);

Composite index on different tables ORACLE SQL

1 Answers1