Whole setup on SQL Fiddle: http://sqlfiddle.com/#!4/1fd0e/5
I have some data containing persons id, level and the levels date range like shown below:
PID LVL START_DATE END_DATE
1 1 01.01.14 19.03.14
1 2 20.03.14 15.08.14
1 3 16.08.14 09.10.14
1 4 10.10.14 31.12.14
2 1 01.01.14 31.12.14
3 1 01.01.14 16.01.14
I need to set the start date to the first day of month and the end date to the last day of month. the last day rule applies only if it is not the last row of data for that person.
what I've don so far:
select
pid, lvl,
trunc(start_date, 'month') as start_date,
case when lead(pid, 1) over (PARTITION BY pid order by end_date) is not null
then last_day(add_months(end_date, -1))
else last_day(end_date)
end as end_date
from date_tbl t;
gives me the desired output:
PID LVL START_DATE END_DATE
1 1 01.01.14 28.02.14
1 2 01.03.14 31.07.14
1 3 01.08.14 30.09.14
1 4 01.10.14 31.12.14
2 1 01.01.14 31.12.14
3 1 01.01.14 31.01.14
BUT: It just works well with my test-data. On my production data on a table containing 25k+ rows of data (which is not too much data I'd say) it performs really slow.
Can anyone give me a hint how I could improve the query's performance? What indices to set on which columns for example...? The only indexed column so far is the PID column.
Ok guys, sorry for waisting your time. To make it short: it was my fault. In my procedure the query above makes a LEFT JOIN to another table in some subquery:
with dates as (
select
pid, lvl,
trunc(start_date, 'month') as start_date,
case when lead(pid, 1) over (PARTITION BY pid order by end_date) is not null
then last_day(add_months(end_date, -1))
else last_day(end_date)
end as end_date
from date_tbl t
),
some_other_table as (
select pid, (...some more columns)
from other_table
)
select * from (
select
b.pid, -- <== this has to be a.pid. b is much bigger than a!
a.start_date,
a.end_date
from dates a left join some_other_table b on a.pid = b.pid
)
The whole query is much bigger.
@jonearles thx for your comment. "And what is the full query?" helped me to get back on track: split the query into pieces and check again what REALLY slows it down.