2

I am trying to take count of records from table which has 194 million records. Used parallel hints and index fast scan but still its slow. Please suggest any alternative or improvement ideas for the query attached. 

SELECT
 /*+ parallel(cs_salestransaction 8)
 index_ffs(cs_salestransaction CS_SALESTRANSACTION_COMPDATE)
 index_ffs(cs_salestransaction CS_SALESTRANSACTION_AK1) */
 COUNT(1)
FROM cs_salestransaction
WHERE processingunitseq=38280596832649217
AND (compensationdate BETWEEN DATE '28-06-17' AND DATE '26-01-18'
OR eventtypeseq IN (16607023626823731, 16607023626823732, 16607023626823733, 16607023626823734));

Here is Execution plan:

[[Execution plan]

The query gave result but took 2 hours to calculate 194 million.

Edits:

Code edited to add DATE per suggestion by Littlefoot. Code edited with actual column names. I am new to stack overflow, hence have attached plan as image.

APC
  • 144,005
  • 19
  • 170
  • 281
Divya Sam
  • 131
  • 2
  • 10
  • What is es execution plan? – Wernfried Domscheit Oct 15 '19 at 06:30
  • @WernfriedDomscheit have attached it as image. I am new to stack overflow. So not sure how to copy paste it in readable format. – Divya Sam Oct 15 '19 at 07:39
  • Use the `{}` icon to format the text as readable code. – Wernfried Domscheit Oct 15 '19 at 07:57
  • 1
    Typically you get the explain plan with `EXPLAIN PLAN FOR ... {your statement};` and `SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);` or `SELECT DBMS_XPLAN.DISPLAY_PLAN from dual;` – Wernfried Domscheit Oct 15 '19 at 08:02
  • 1
    "*parallel(8) hint*": so how many CPUs does your database server have? What else was running while you executed this query? – APC Oct 15 '19 at 08:07
  • What columns are indexed by CS_SALESTRANSACTION_AK1 and CS_SALESTRANSACTION_COMPDATE? Do you have an index on `processingunitseq`? Basically, tuning Oracle queries depends on a lot of factors. Please read [this useful thread on how to ask Oracle tuning questions](https://stackoverflow.com/a/34975420/146325).. – APC Oct 15 '19 at 08:13
  • How many rows are returned by the query? How restrictive are each of the filters? Regular b-tree index access usually work best for conditions that return a small percentage of rows, full table scans and index fast full scans work best for conditions that return a large percentage of rows. I'd recommend changing the hint to `/*+ parallel(8) */`, and let Oracle decide how to do the parallelism. Also, it would help to use a text based execution plan, with `explain plan for select ...` and then `select * from table(dbms_xplan.display);`. Graphical plans are inconvenient and can leave a lot out. – Jon Heller Oct 16 '19 at 03:12
  • 1
    @APC that was one of the reasons for my slow query. many session running in background. thanks – Divya Sam Oct 16 '19 at 10:28

2 Answers2

5

Also, if compensationdate is DATE datatype, don't compare it to strings (because '28-JUL-17' is a string) and force Oracle to perform implicit conversion & spend time over nothing. Switch to

compensationdate BETWEEN date '2017-07-28' and date '2018-01-26'
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Thanks for that. That speeded up the SELECT * query and decreased time to 1 second (from 5 seconds). However my SELECT COUNT(1) is still taking time (still running).. – Divya Sam Oct 15 '19 at 07:37
  • yes i thought about it later yesterday..and yes for 200 million records its significant. – Divya Sam Oct 16 '19 at 09:38
0

Having OR CONDITION in where clause ignores the use of index in the query. You should get rid of OR condition. There can be multiple ways for that. One of the method is - 

SELECT /*+ parallel(sales 8)
           index_ffs(sales ,sales_COMPDATE) 
           index_ffs(sales , sales_eventtypeseq )*/
       COUNT(1)
FROM sales 
WHERE processingunitseq=38
AND compensationdate BETWEEN TO_DATE('28-JUL-17') AND TO_DATE('26-JAN-18')
UNION ALL
SELECT /*+ parallel(sales 8)
           index_ffs(sales ,sales_COMPDATE) 
           index_ffs(sales , sales_eventtypeseq )*/
       COUNT(1)
FROM sales 
WHERE processingunitseq=38
AND compensationdate NOT BETWEEN TO_DATE('28-JUL-17') AND TO_DATE('26-JAN-18')  -- To avoid duplicates
AND eventtypeseq IN (1, 2, 3, 4);

For other suggestions, Please post the execution plan of the query.

Ankit Bajpai
  • 13,128
  • 4
  • 25
  • 40