more sql 10.2.0.3
Date: Fri, 21 Mar 2008 09:27:16 -0500
It has been a very rewarding few weeks at work. I've gone through a number of views that have been giving us performance problems in our production database environment and have had great success in re-writing sql to reduce the LIO's to a fraction of their original version. Much of that success is related to the responses I've gotten from this list on and off-line. Thank you.
I do have one query that I think can be vastly improved but I'm having troubles with generating the proper number of missing time periods to carry allocations through. Here is the original query:
SELECT Project.projectNumber, period.periodID, allocation.fraction, allocation.code
FROM Period, Project, allocation, period allocPeriod
WHERE Project.projectNumber = allocation.projectNumber
AND Period.periodID >= allocation.periodID
AND allocPeriod.enddate = (
SELECT MAX(p.enddate) FROM allocation a, period p WHERE a.projectNumber = allocation.projectNumber And a.iscurrent = 1 AND a.periodID = p.periodid AND p.enddate <= period.enddate ) AND allocation.periodID = allocPeriod.periodID AND allocation.isCurrent = 1
The view shows percentages of resource allocations charged to various codes across periods by project number. Table allocation only contains entries for changes to the allocations so the above query carries the allocations through periods until those allocations change.
For several other queries I've been able to use partition outer joins to the period table to generate missing periods and then use last_value to carry whatever data through those periods. For those cases there have only been 1 value per project (or whatever other entity) per period. Here there are multiple values per period. My question is how to I carry all the allocations through?
The following is what I have so far but it only brings 1 allocation through periods:
select * from (
p.periodid, last_value(a.fraction ignore nulls) over (partitionby a.projectnumber order by p.periodid) fraction,
last_value(a.code ignore nulls) over (partition bya.projectnumber order by p.periodid) code
from (select * from allocation where iscurrent = 1) a partition by (a.projectnumber) right outer join period p on (a.periodid = p.periodid)
order by a.projectnumber, p.periodid
where fraction is not null;
This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.