oracle olap query execution is taking too long

Question

I have these following tables:

1) date_table_dim

2) clock_table_dim

3) onlinegpspoint : which contains our main information for olap reports

And also there is a sql query like this:

SELECT 
  date_table_dim.day_id day_id,
  clock_table_dim.hour_id hour_id

FROM  onlinegpspoint olgps
INNER JOIN date_table_dim
ON ( 
olgps.occurance_time >= to_date('2014-03-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND olgps.occurance_time  >= date_table_dim.day_id
AND olgps.occurance_time   < date_table_dim.day_id + 1
)
INNER JOIN  clock_table_dim
ON ( clock_table_dim.hour_id <= TO_NUMBER(TO_CHAR(occurance_time, 'HH24'))
AND clock_table_dim.hour_id   > TO_NUMBER(TO_CHAR((occurance_time - 1/24), 'HH24') ))

GROUP BY 
  date_table_dim.day_id,
  clock_table_dim.hour_id ;

My problem is that this query is taking too long to execute. What actions can be taken to improve the performance of this query execution?

EDIT

There is an index for occurance_time on onlinegpspoint. By this query I want to get some Olap information for 1 hour period of time. (This query is a kind of summary of my fact_table query.)

There is an index for occurance_time on onlinegpspoint. By this query I want to get some Olap information for 1 hour period of time. (This query is a kind of summary of my fact_table query.) — Banafshe Alipour, Apr 11 '15 at 08:42
When adding information to a question please **use the edit button** to include the additional information in the body of the question - do not use comments as people may not read them. Now, please **edit the question** and include information about the datatypes of the fields in these tables. Thanks. — Bob Jarvis - Слава Україні, Apr 11 '15 at 11:49

Ponder Stibbons · Answer 1 · 2015-04-11T13:13:46.180

You can try following query.

SQLFiddle

with t as (select d.day_id, o.occurance_time ot
  from onlinegpspoint o
  join date_table_dim d on ( o.occurance_time >= date '2014-03-01'
    and d.day_id <= o.occurance_time and o.occurance_time < d.day_id + 1) ) 
select day_id, c.hour_id
  from t join clock_table_dim c on ( 
    c.hour_id <= to_char(t.ot, 'HH24') and to_char((t.ot - 1/24), 'HH24') < c.hour_id )
  group by day_id, c.hour_id order by day_id, c.hour_id;

In your original query you compare to_char(occurance_time, 'HH24') with hour_id, here index probably does not work. So idea is to firstly filter data to interesting period and then work only with these filtered data.

There is one more query worth trying, which gave me promising results:

select distinct trunc(occurance_time) day_id, to_char(occurance_time, 'hh24')+0 hour_id 
  from onlinegpspoint o join (
    select to_date(to_char(day_id, 'yyyy-mm-dd ')||' '
        ||lpad(hour_id, 2, 0), 'yyyy-mm-dd hh24') dt 
      from date_table_dim, clock_table_dim) d 
    on (o.occurance_time >= date '2014-03-01' 
      and d.dt-1/24 <= o.occurance_time and o.occurance_time < d.dt)

oracle olap query execution is taking too long

EDIT

1 Answers1