Increase the performance of a SQL query

Question

I have a tranctions table in which I need to get the transtions date and time for 5 differnect tractions (order drop, order pick, order Address labeled, loaded and ship).

For each order I'm trying to get these transtions time for specific date and on orders we ship on those date. My query run almost for every to get these information.

Is there a better way to rewrite this? I would really appreciate your help.

SELECT
    "ORDER",
     MAX(CASE WHEN "ACTION" = 'DNLD' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd 
     hh24:mi')  END) AS ORDER_Drop_time,
     MAX(CASE WHEN "ACTION" = 'REQUEST' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd hh24:mi')  END) AS Label_request_time,
     MAX(CASE WHEN "ACTION" = 'PICK' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd hh24:mi')  END) AS pick_time,
     MAX(CASE WHEN "ACTION" = 'LOAD' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd hh24:mi')  END) AS Load_TIME,
     MAX(CASE WHEN "ACTION" = 'SHIP' THEN TO_CHAR(datetimecreated,'yyyy-mm-dd hh24:mi')  END) AS SHIP_COM_TIME
FROM
    historymaster hm
WHERE
    "ORDER" IN (
        SELECT
            "ORDER"
        FROM
            historymaster
        WHERE
            datetimecreated >=:usestartdate
            AND   datetimecreated <=:useenddate
            AND   "ACTION" = 'SHIP'
            AND   "OBJECT" = 'OBORDLINE'
    ) -- Looking up order ID for ship transations and using it 
    AND   (
        (-- Order drop 
            "ACTION" = 'DNLD'
            AND   "OBJECT" = 'OBORDLINE'
            AND   actionmodifier IS NULL
            AND   reasoncode = '00'
        )
        OR --Address label request
         (
            "ACTION" = 'REQUEST'
            AND   "OBJECT" = 'LABEL'
            AND   "CLASS" = 'ADDR'
        )
        OR -- pick 
         (
            "ACTION" = 'PICK'
            AND   "OBJECT" = 'OBO'
            AND   "CLASS" = 'INVE'
            AND   actualquantity != 0
            AND   substr(ordertype,1,1) = 'N'
        )
        OR   -- Trailer Load
         (
            "ACTION" = 'LOAD'
            AND   "OBJECT" = 'OBO'
            AND   "CLASS" = 'INVE'
        )
        OR --Ship Complete 
         (
            "OBJECT" = 'OBORDLINE'
            AND   hm.package = ' '
            AND   actionmodifier IS NULL
            AND   "ACTION" = 'SHIP'
        )
    )
GROUP BY
    "ORDER";

Output:

ORDER         ORDER_Drop_time   Label_request_time  PICK_TIME   Load_TIME   SHIP_COM_TIME
D2KJJKJLB-35689 8/2/2018 9:50   8/6/2018 9:50   8/6/2018 8:50   8/6/2018 10:50  8/7/2018 14:16

The performace is good when i just use a date filter but goes bad when i try to check each order id that was shipped in a specific date but since the tranctions happens in different dates i had to look up on each order id. is there a better way to do this? — Karthik Elangovan, Aug 08 '18 at 15:49
Provide more data: how many ORDER do you query, how many ORDERs and rows has your table. Show the used [execution plan](https://stackoverflow.com/questions/34975406/how-to-describe-performance-issue-in-relational-database?answertab=active#tab-top) — Marmite Bomber, Aug 08 '18 at 15:54
I have 7000 orders [explain plan](https://drive.google.com/open?id=1AxDMwIlMq8ief7wYJSR7olnwmR1xQC1x) sorry i coundn't get the explain plan in the formate you asked for. — Karthik Elangovan, Aug 08 '18 at 17:43

The Impaler · Answer 1 · 2018-08-10T17:53:18.933

0

You should use indexes if this query is getting slow.

The most important index I can think of is:

create index ix1 on historymaster ("OBJECT", "ACTION", datetimecreated);

Additionally, if you really want this query to be even faster, you can add:

create index ix2 on historymaster ("OBJECT", "ACTION", reasoncode);
create index ix3 on historymaster ("OBJECT", "ACTION", "CLASS");

Be careful not to add to many indexes to our tables, since it can make your INSERTs, UPDATEs, and DELETEs slow. There's a balance you need to find.

Please retrieve the execution plan for the query. To do it:

Prepare the retrieval:
```
drop table plan_table;
```
Create the execution plan:
```
explain plan for
select ...
```

Retrieve the execution plan:

select plan_table_output 
  from table(dbms_xplan.display('plan_table',null,'typical'));

Once you get it, add the plan to your question.

edited Aug 10 '18 at 17:53

answered Aug 08 '18 at 15:47

The Impaler

45,731
9
39
76

is there another way i can increase the performance since i don't have access to add index – Karthik Elangovan Aug 08 '18 at 17:46
In order to respond to your question, you would need to post the full list of existing indexes, as well as the primary key and unique constraints on this table. Can you add them to the question? – The Impaler Aug 08 '18 at 17:54
There are 10 index and i already have a index with one of the index have all these col. which is been used in explain plan (TRANS_SEQ_NUM,OID,LIST,TRANS_ACT,WHSE_ID,LNO,TRANS_CLASS,TRANS_ACT_MOD,OID_TYPE,TYPE_OF_ORDER,TRANS_OBJ) – Karthik Elangovan Aug 10 '18 at 01:15
I have also added the current index on the history table to the questions and there is no constraints on this table. – Karthik Elangovan Aug 10 '18 at 14:43
Well, your indexes seem to be in place. If it's still slow, can you post the EXPLAIN PLAN? Added steps to do it in my answer, if you don't know how to do it. – The Impaler Aug 10 '18 at 17:51
i have added the screen shoot of the explain plain. I was able to separate the problem causing the performance but could not find a solutions. The query is performing good within 30 sec. but when I add a filter in the where clause("Looking up order ID for ship transations and using it ") to lookup for order id for that specific ship data the performance cost goes up exponential and takes more than 10min. if you could direct me on what I should be look for it would be very helpful. – Karthik Elangovan Aug 13 '18 at 03:59

score 0 · Answer 2 · answered Aug 09 '18 at 10:22

Your query performs basically two access, so (provided you select only a small number of orders) you need two indexes to reflect it.

The first index select the orders with propper action and object in a given time range

 ("ACTION","OBJECT",datetimecreated)

The second index then queries all records for those orders with requested action and object

 ("ORDER", "ACTION" ,"OBJECT")

To demostrate the principal, I created a test table with 2M rows and 100K orders (see script below).

Note, that I don't use reserved names (e.g. "ORDER" is order_id) and I use "neutral" names for action and object.

select order_id,
max(case when action_id = 'A1' then datetimecreated end) as A1_date,
max(case when action_id = 'A2' then datetimecreated end) as A2_date,
max(case when action_id = 'A3' then datetimecreated end) as A3_date,
max(case when action_id = 'A4' then datetimecreated end) as A4_date,
max(case when action_id = 'A5' then datetimecreated end) as A5_date 
from test
where order_id in (
   select order_id
   from test
   where datetimecreated >= DATE'2018-02-05' and  datetimecreated <= DATE'2018-02-06' and
   action_id = 'A1' and object_id = 'O1')
AND
(action_id = 'A1' and object_id = 'O1' or
 action_id = 'A2' and object_id = 'O2' or
 action_id = 'A3' and object_id = 'O3' or
 action_id = 'A4' and object_id = 'O4' or
 action_id = 'A5' and object_id = 'O1')
group by order_id;


  ORDER_ID A1_DATE  A2_DATE  A3_DATE  A4_DATE  A5_DATE
---------- -------- -------- -------- -------- --------
       826 05.02.18 10.02.18 15.02.18 20.02.18 21.02.18
       833 05.02.18 10.02.18 15.02.18 20.02.18 21.02.18
.....
       823 05.02.18 10.02.18 15.02.18 20.02.18 21.02.18
       838 05.02.18 10.02.18 15.02.18 20.02.18 21.02.18

25 rows selected.

Elapsed: 00:00:00.10

You expect this execution plan - the inner NESTED LOOP performs the two access described above - see the access condition 5 and 7.

Plan hash value: 1930696803

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |           |   223 | 13380 |    12   (9)| 00:00:01 |
|   1 |  HASH GROUP BY                 |           |   223 | 13380 |    12   (9)| 00:00:01 |
|   2 |   NESTED LOOPS                 |           |       |       |            |          |
|   3 |    NESTED LOOPS                |           |   223 | 13380 |    11   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TEST      |     1 |    30 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | TEST_IDX1 |     1 |       |     3   (0)| 00:00:01 |
|   6 |     INLIST ITERATOR            |           |       |       |            |          |
|*  7 |      INDEX RANGE SCAN          | TEST_IDX2 |     1 |       |     6   (0)| 00:00:01 |
|   8 |    TABLE ACCESS BY INDEX ROWID | TEST      |   502 | 15060 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("ACTION_ID"='A1' AND "OBJECT_ID"='O1' AND 
              "DATETIMECREATED">=TO_DATE(' 2018-02-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
              "DATETIMECREATED"<=TO_DATE(' 2018-02-06 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   7 - access("ORDER_ID"="ORDER_ID" AND ("ACTION_ID"='A1' AND "OBJECT_ID"='O1' OR 
              "ACTION_ID"='A2' AND "OBJECT_ID"='O2' OR "ACTION_ID"='A3' AND "OBJECT_ID"='O3' OR 
              "ACTION_ID"='A4' AND "OBJECT_ID"='O4' OR "ACTION_ID"='A5' AND "OBJECT_ID"='O1'))

Here the script to produce test data to play with:

create table test as 
with ord as (
select rownum order_id, date'2018-01-01' + rownum / 24 datetimecreated from dual connect by level <= 100000),
act as (
select 'A1' action_id, 'O1' object_id, 1 offset from dual union all
select 'A1' action_id, 'O2' object_id, 2 offset from dual union all
select 'A1' action_id, 'O3' object_id, 3 offset from dual union all
select 'A1' action_id, 'O4' object_id, 4 offset from dual union all
select 'A2' action_id, 'O1' object_id, 5 offset from dual union all
select 'A2' action_id, 'O2' object_id, 6 offset from dual union all
select 'A2' action_id, 'O3' object_id, 7 offset from dual union all
select 'A2' action_id, 'O4' object_id, 8 offset from dual union all
select 'A3' action_id, 'O1' object_id, 9 offset from dual union all
select 'A3' action_id, 'O2' object_id, 10 offset from dual union all
select 'A3' action_id, 'O3' object_id, 11 offset from dual union all
select 'A3' action_id, 'O4' object_id, 12 offset from dual union all
select 'A4' action_id, 'O1' object_id, 13 offset from dual union all
select 'A4' action_id, 'O2' object_id, 14 offset from dual union all
select 'A4' action_id, 'O3' object_id, 15 offset from dual union all
select 'A4' action_id, 'O4' object_id, 16 offset from dual union all
select 'A5' action_id, 'O1' object_id, 17 offset from dual union all
select 'A5' action_id, 'O2' object_id, 18 offset from dual union all
select 'A5' action_id, 'O3' object_id, 19 offset from dual union all
select 'A5' action_id, 'O4' object_id, 20 offset from dual)
select order_id, datetimecreated + offset datetimecreated, action_id,object_id  from ord
cross join act;

create index test_idx1 on test(action_id, object_id,datetimecreated);
create index test_idx2 on test(order_id,action_id, object_id);

thank you, that was very useful. I was able to separate the problem causing the performance but could not find a solutions. The query is performing good within 30 sec. but when I add a filter in the where clause("Looking up order ID for ship transations and using it ") to get order id for that specific ship data the performance cost goes up exponential and takes more than 10min. if you could direct me on what I should be look for it would be very helpful. — Karthik Elangovan, Aug 10 '18 at 14:42

Increase the performance of a SQL query

2 Answers2