Tuning a query that selects the latest rows based on the date

Question

Edited: Sorry I forgot to add an important condition value1 - value 2 <> 0;. Without it result have ~111k records. And as I rewrite query using CTE go get set of code(s) in max date to join with main table it seems index can't help my case, i think. --==================================

We have a query (a CTE) that get records with latest date.

For one of worst cases, this take 8.6 secs to get 11k rows. Table table_name has 3.1 mil rows.

For now this query use full table scan. I try but cannot find an index to add to use in this query. Could anyone have a way to tune it?

--sample - one of worse cases
p_filter_code_1 VARCHAR2(200) := ' ';
p_filter_code2 VARCHAR2(10) := ' ';
p_filter_code3 VARCHAR2(10) := ' ';
p_filter_code4 VARCHAR2(10) := ' ';
p_filter_code5 VARCHAR2(10) := ' ';
p_filter_start_date NUMBER := 20170101;
p_filter_end_date NUMBER := 20171231;    
--===============


SELECT code_2, code_3, code_4, code5
    value1, value2
FROM (
    SELECT t.*,
        ROW_NUMBER() OVER (PARTITION BY code_1, code_2, code_3, code_4, code_5 ORDER BY date_col DESC) AS rn
    FROM table_name t
    WHERE
        (
            code_1 IN (SELECT code_1 FROM temp_code)
        )
        AND date_col < p_filter_start_date
        AND (code_2 LIKE p_filter_code2 OR p_filter_code2 = ' ')
        AND (code_3 LIKE p_filter_code3 OR p_filter_code3 = ' ')
        AND (code_4 = p_filter_code4 OR p_filter_code4 = ' ')
        AND (code_5 = p_filter_code5 OR p_filter_code5 = ' ')
)
WHERE rn = 1 AND value1 - value 2 <> 0;

This is sample value for code_1_table and temp_code with filter from user: '1002,1020-1025,1030,1040-1050' (like print pages in word)

code_1_table (sample)
code_1     code_1_name
1001        test
1002         x..
1023          .sona
1025        .sojj
1026        .oifhal


temp_code sample records with p_filter_code_1 := '1002,1020-1025,1030,1040-1050'
code_1
1002
1023
1025

CREATE TABLE table_name
    (code_1                         VARCHAR2(10 BYTE) ,
    code_2                          VARCHAR2(20 BYTE) ,
    code_3                         VARCHAR2(20 BYTE) ,
    code_4                         VARCHAR2(25 BYTE) ,
    code_5                          VARCHAR2(25 BYTE) ,
    value1                          NUMBER,
    value2                          NUMBER,
    value3                          NUMBER,
    value4                          NUMBER,
    date_col                        NUMBER  )

ALTER TABLE table_name
ADD CONSTRAINT table_name_p 
    PRIMARY KEY (code_1, code_2, code_3, code_4, code_5, date_col)
/

How many rows are in temp_code and how many code_1's in temp_code are NULL? — Rob van Wijk, Jan 19 '18 at 08:10
@RobvanWijk you could see in my edited. No NULL value. Will it affect performance if it has NULL? — Pham X. Bach, Jan 19 '18 at 08:16
ok, and "select count(\*) from table_name" gives 3.1M rows. How many rows does "select count(\*) from table_name where date_col < l_filter_start_date" give? — Rob van Wijk, Jan 19 '18 at 08:21
What's rowcount for of select distinct code1, code2, code3, code4, code5 from table_name? And for select distinct code1, code2, code3, code4, code5, date_col from table_name? — Alexander Anikin, Jan 19 '18 at 09:16
@AlexanderAnikin first select 128k rows, seconds is PK so = 3.1 mil — Pham X. Bach, Jan 19 '18 at 09:33
Do you have an option to put value1 - value 2 <> 0 inside inner select? This change will change results, but for some use cases it might be ok. If you can, you can try functional index with date_col and this condition and then code_1. If — Alexander Anikin, Jan 19 '18 at 10:04

Alessandro Cucina · Answer 1 · 2018-01-19T11:19:35.703

0

Performing select t.*... Oracle most likely skips any index on t.

Maybe you could try creating an index on date_col (or a sorted index) and use a with construct to materialize a dataset in witch you get most recent record with row_number and then join it with table_name on rowid.

edited Jan 19 '18 at 11:19

answered Jan 19 '18 at 08:05

Alessandro Cucina

148
1
12

Not mention that oracle will rewrite the SELECT t.* in my query, even when I use SELECT t.*, it could use index on `t`. For CTE using with and get max set of code to join with main table, I will give it a try, thks – Pham X. Bach Jan 19 '18 at 08:17

score 0 · Answer 2 · answered Jan 19 '18 at 08:44

Make sure you have NOT NULL constraints on table_name.code_1 and temp_code.t_code1 Indexes probably won't help you here, Unless your table_name contains a huge number of columns and the 8 columns you need for this query are in its own index structure.

Your query will need to process quite a lot of rows and will likely never be subsecond, however, you're using the output of an analytic function as a filter, where grouping/aggregation is more efficient, so by rewriting your query as below, you'll see a slight improvement.

select t.code_2
     , t.code_3
     , t.code_4
     , t.code5
     , max(t.value1) keep (dense_rank last order by date_col) value1
     , max(t.value2) keep (dense_rank last order by date_col) value2
  from table_name t
       inner join temp_code tc on (t.code_1 = tc.code_1)
 where t.date_col < l_filter_start_date
   and (t.code_2 LIKE l_filter_code2 OR l_filter_code2 = ' ')
   and (t.code_3 LIKE l_filter_code3 OR l_filter_code3 = ' ')
   and (t.code_4 = l_filter_code4 OR l_filter_code4 = ' ' )
   and (t.code_5 = l_filter_code5 OR l_filter_code5 = ' ' )
 group by t.code_1
     , t.code_2
     , t.code_3
     , t.code_4
     , t.code5

Radim Bača · Answer 3 · 2018-01-19T08:27:42.163

-1

Actually, I believe that Oracle is not willing to use an index once you wrote window function. Use GROUP BY + MAX instead.

SELECT tn.code_2, tn.code_3, tn.code_4, tn.code5,
    tn.value1, tn.value2
FROM  table_name tn
JOIN (
    SELECT code_1, code_2, code_3, code_4, code_5, max(date_col) max_date_col
    FROM table_name t
    WHERE
        (
            code_1 IN (SELECT code_1 FROM temp_code)
        )
        AND date_col < l_filter_start_date
        AND (code_2 LIKE l_filter_code2 OR l_filter_code2 = ' ')
        AND (code_3 LIKE l_filter_code3 OR l_filter_code3 = ' ')
        AND (code_4 = l_filter_code4 OR l_filter_code4 = ' ' )
        AND (code_5 = l_filter_code5 OR l_filter_code5 = ' ' )
    GROUP BY code_1, code_2, code_3, code_4, code_5
) t ON tn.code_1 = t.code_1,
       tn.code_2 = t.code_2,
       tn.code_3 = t.code_3,
       tn.code_4 = t.code_4,
       tn.code_5 = t.code_5,           
       tn.max_date_col = t.date_col

and create index like this

create index ix_table_name_code_max on table_name (
     date_col, code_1, code_2, code_3, code_4, code_5, value1, value2);

edited Jan 19 '18 at 08:27

answered Jan 19 '18 at 08:14

Radim Bača

10,646
1
19
33

Indexes can be used in combination with analytic functions. Indexes on expressions containing variables is not possible. Other index will not be used. – Rob van Wijk Jan 19 '18 at 08:18
@RobvanWijk it is not mentioned anywhere that `l_filter_codeX` is a variable not a table column – Radim Bača Jan 19 '18 at 08:19
Sorry `l_filter_codex` is filter variables. I thougt its name is sefl-describle so I didn't mention that. – Pham X. Bach Jan 19 '18 at 08:23
@PhamX.Bach ok, then the situation is much easier. Please read the update. – Radim Bača Jan 19 '18 at 08:28
True, it wasn't mentioned. I just recognized the convention to prefix local variables with "l_". – Rob van Wijk Jan 19 '18 at 08:46

Tuning a query that selects the latest rows based on the date

3 Answers3