CODEWITHSUNDEEP
sqlperformanceoracle-database
venergiac
venergiac

Reputation: 7717

Oracle SQL efficient query on Partitioned Table: "order by case"

I get a table of about 100 billions records partitioned on the key date_insert.

MY_BIG_TABLE

Input:

Note: :date_input1 < :date_input2

I want

  1. IF date_fact < :date_input_1 the first row ordered by date_fact DESC
  2. ELSE IF date_fact < :date_input_2 the first row ordered by date_fact ASC
  3. ELSE the latest

A query prototype should be:

SELECT date_insert, date_fact, data (

        SELECT
            date_insert, 
            date_fact,
            CASE 
               WHEN (date_fact < :date_input_1 AND id= :id) : 2
               WHEN (date_fact < :date_input_2 AND id= :id) : 1
               ELSE : 0  
            check,

            CASE 
               WHEN (date_fact < :date_input_1 AND id= :id) : NULL
               WHEN (date_fact < :date_input_2 AND id= :id) : data
               ELSE : NULL  
            data

        FROM MY_BIG_TABLE
        WHERE date_insert > :date_input3   
        order by check, DECODE(check, 1, date_fact) ASC,
                        date_fact DESC 

) WHERE ROWNUM <2

I need to do this hard work to avoid too many access to this table. Any suggetsion to rebuild completely the query to be simpler and easier?

Upvotes: 2

Views: 294

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You are only looking for one row. So, do three different subqueries, getting one row each, and then choose the one you want:

select date_insert, date_fact, data
from (select date_insert, date_fact, data
      from ((select date_insert, date_fact, data, 2 as check
             from (select date_insert, date_fact,
                          (CASE WHEN (date_fact < :date_input_1 AND id= :id) then NULL
                                WHEN (date_fact < :date_input_2 AND id= :id) then data
                                ELSE NULL
                           end) as data
                   from MY_BIG_TABLE
                   where date_fact < :date_input_1 and date_insert > :date_input3
                   order by date_fact desc
                  ) t
             where rownum = 1
            ) union all
            (select date_insert, date_fact, data, 1 as check
             from (select date_insert, date_fact,
                          (CASE WHEN (date_fact < :date_input_1 AND id= :id) then NULL
                                WHEN (date_fact < :date_input_2 AND id= :id) then data
                                ELSE NULL
                           end) as data
                   from MY_BIG_TABLE
                   where date_fact < :date_input_2 and date_insert > :date_input3
                   order by date_fact asc
                  ) t
             where rownum = 1
            ) union all
            (select date_insert, date_fact, data, 0 as check
             from (select date_insert, date_fact,
                          (CASE WHEN (date_fact < :date_input_1 AND id= :id) then NULL
                                WHEN (date_fact < :date_input_2 AND id= :id) then data
                                ELSE NULL
                           end) as data
                   from MY_BIG_TABLE
                   where date_insert > :date_input3
                   order by date_fact desc
                  ) t
             where rownum = 1
            )
           ) t
      order by check desc
     ) t
where rownum = 1;

Oracle should be smart enough to use the indexes for the subqueries, so this might actually run quite fast.

Upvotes: 4

Related Questions