CODEWITHSUNDEEP
sqloracle-databasequery-optimization
Abdusoli
Abdusoli

Reputation: 659

How to optimize Oracle Query with lots of and statements

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

Upvotes: 0

Views: 150

Answers (3)

Bruno Manuel Rosas Marques
Bruno Manuel Rosas Marques

Reputation: 51

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

Upvotes: 0

Popeye
Popeye

Reputation: 35910

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!!

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1270191

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

Upvotes: 0

Related Questions