How to optimize Oracle Query with lots of and statements

Question

I am selecting data from oracle table using several and Statements in Select query. In addition I am using Like Statement. The problem is when I work with big table it is taking too much time to execute query. How can I alter some parts of the query below.

SELECT t.co_filial as fil_code, t.emp_birth as emp_code, to_char(t.curr_day, 'YYYY-MM-DD') as operation_date, 
TRUNC(t.Sum_Pay/100) As summa
FROM operation_history t 
WHERE Substr(t.Co_Acc, 8) LIKE '12294%' And Substr(t.Co_Acc, -3) > 599 And Substr(t.Co_Acc, -3) != 683 And Substr(t.Co_Acc, -3) < 696
AND t.state_id = 41
And t.curr_day >= to_date('12.08.2019', 'DD.MM.YYYY')
And t.curr_day <  to_date('13.08.2019', 'DD.MM.YYYY')

Why ```Substr(t.Co_Acc, 8) LIKE '12294%'``` instead of ```Substr(t.Co_Acc, 8, 5) = '12294'```? — Joan Lara, Aug 19 '19 at 11:46
In order to optimize the query we need to know the table structure e.g. indicies - can you provide it as well? — sudo, Aug 19 '19 at 11:58
And you can use `trunc(t.curr_day) = to_date('12.08.2019', 'DD.MM.YYYY')` instead of `t.curr_day >= to_date('12.08.2019', 'DD.MM.YYYY') And t.curr_day < to_date('13.08.2019', 'DD.MM.YYYY')`. — Radagast81, Aug 19 '19 at 11:58
It might be more a question of adding an useful index then to rewrite the query. But to optimize we also need to know which values of your query are fixed and which get changed so `Substr(t.Co_Acc, 8, 5) = '12294'` and `trunc(t.curr_day) = to_date('12.08.2019', 'DD.MM.YYYY')` only make sense in some scenarios. — Radagast81, Aug 19 '19 at 12:02
You should see answer of this [question](https://stackoverflow.com/questions/543580/equals-vs-like/28609966) — Popeye, Aug 19 '19 at 12:09
If you can write the statements with `=` then it can highly benefit from an index and that will increase performance imensely. As with other compare operations as `LIKE`, `>`, `<` the benefit drops significantly. And yes `LIKE` is allways slitely slower than `=` but most likely the compiler itself will optimize that part itself and execute `Substr(t.Co_Acc, 8, 5) = '12294'`. — Radagast81, Aug 19 '19 at 12:12
@Radagast81 your suggestion to use `trunc...` would potentially prevent the query from using an index on `curr_day`. — Dave Costa, Aug 19 '19 at 12:36
@Dave Costa Yeah i know, but you could add a function based index on `state_id, Substr(Co_Acc, 8, 5), trunc(curr_day), Substr(Co_Acc, -3)` so the complete where clause could be evaluated in one single index range scan. That is not possible using `<` and `>` on different columns. — Radagast81, Aug 19 '19 at 12:46
@APC I have no reall benchmark myself, but there is a thread https://stackoverflow.com/questions/6142235/sql-like-vs-performance confirming my statement. — Radagast81, Aug 19 '19 at 12:54
@APC the important fakt is that ` like 'whatever%'` is slower than ` = 'whatever'`, telling `like`is slitely slower then `=` . `substr(whatever, a, b)` should be the same time regardless of a and b. But that performance difference should be very minor so no benefit to argue if it's really true or not. — Radagast81, Aug 19 '19 at 13:55

Popeye · Answer 1 · 2019-08-19T12:59:50.450

3

One obvious improvement is: Substr of string to fetch last 3 characters can be used once, instead of three times and used in subquery as following.

SELECT fil_code,
  emp_code,
  operation_date,
  summa
From
  (SELECT t.co_filial as fil_code, 
     t.emp_birth as emp_code, 
     to_char(t.curr_day, 'YYYY-MM-DD') as operation_date, 
     TRUNC(t.Sum_Pay/100) As summa,
     Substr(t.Co_Acc, -3) AS SUBSTR_3 -- ADDED THIS
   FROM operation_history t 
  WHERE Substr(t.Co_Acc, 8, 5) = '12294' -- used direct equals operator 
  --And Substr(t.Co_Acc, -3) > 599 
  --And Substr(t.Co_Acc, -3) != 683 
  --And Substr(t.Co_Acc, -3) < 696
  AND t.state_id = 41
  And t.curr_day >= to_date('12.08.2019', 'DD.MM.YYYY')
  And t.curr_day <  to_date('13.08.2019', 'DD.MM.YYYY'))
-- added following where clause
WHERE SUBSTR_3 BETWEEN 600 AND 695
AND SUBSTR_3 != 683

I have used between which is inclusive of upper and lower value so 1 is added and subtracted from respective limits.

Cheers!!

edited Aug 19 '19 at 12:59

answered Aug 19 '19 at 11:57

Popeye

35,427
4
10
31

Also, Equals is used instead of LIKE – Popeye Aug 19 '19 at 12:21
@Tejash Just one minor copy error: should be `Substr(t.Co_Acc, 8, 5)` as startpoint is 8 in the original query... – Radagast81 Aug 19 '19 at 12:24
@Tejash Could you update query with column names instead of dots if it is possible, cause it is giving error inside subquery, oracle cannot see t.Sum_Pay. – Abdusoli Aug 19 '19 at 12:43
@AbdusoliErgashev - so you're asking somebody to fix your syntax errors even though they don't have access to your schema and so can't run this code whereas you do? Moreover, fixing syntax errors in code you haven't posted. – APC Aug 19 '19 at 12:50
1

Yeap, APC is right. Anyway, I have solved your problem. Please see my updated answer – Popeye Aug 19 '19 at 13:01

score 0 · Answer 2 · answered Aug 19 '19 at 12:37

For this query (which I've cleaned up a little):

SELECT t.co_filial as fil_code, t.emp_birth as emp_code, to_char(t.curr_day, 'YYYY-MM-DD') as operation_date, 
        TRUNC(t.Sum_Pay/100) As summa
FROM operation_history t 
WHERE Substr(t.Co_Acc, 8) LIKE '45294%' And Substr(t.Co_Acc, -3) > 599 And
      Substr(t.Co_Acc, -3) <> 683 And
      Substr(t.Co_Acc, -3) < 696 AND 
      t.state_id = 41 And
      t.curr_day >= date '2019-08-12' and
      t.curr_day <  date '2019-08-13';

To make this run faster, you want to use indexes on the WHERE clause. That is probably the only thing that will give noticeable improvement.

I would recommend an index on operation_history(state_id, curr_date, Substr(t.Co_Acc, 8)).

Because you are only looking for one day in the results, there is one more thing you can do:

SELECT t.co_filial as fil_code, t.emp_birth as emp_code, to_char(t.curr_day, 'YYYY-MM-DD') as operation_date, 
        TRUNC(t.Sum_Pay/100) As summa
FROM operation_history t 
WHERE Substr(t.Co_Acc, 8) LIKE '45294%' and
      Substr(t.Co_Acc, -3) > 599 and
      Substr(t.Co_Acc, -3) <> 683 and
      Substr(t.Co_Acc, -3) < 696 and 
      t.state_id = 41 and
      trunc(t.curr_day) = date '2019-08-12';

Then, the index you want is on operation_history(state_id, trunc(curr_date), Substr(t.Co_Acc, 8)).

As I said in the comments, building a function-based index solely to support this query might be worth the overheads of maintaining it. But we know nothing about how often this query is run, how often the table is written to and how important the query is. Not to mention why the data model is so broken. — APC, Aug 19 '19 at 13:06
@APC . . . Correct. We only know that the OP asked about optimizing *this* query. — Gordon Linoff, Aug 19 '19 at 13:48

Bruno Manuel Rosas Marques · Answer 3 · 2019-08-19T16:46:34.033

Some suggestions:

1) Instead of calculating on the fly the operation_date substr_3 and summa fields, create 3 generated (computed) columns , one for each expression. These will be pre-calculated in the database, and you just need to select the computed column(s). Much faster. Columns should be persistent (not virtual)

https://oracle-base.com/articles/11g/virtual-columns-11gr1

2) check if you can do the same for those substr expressions

3) create indexes for the computed columns you created in 2), if any

4) create indexes for stateid, and curr_day

Regards

How to optimize Oracle Query with lots of and statements

3 Answers3