more sql 10.2.0.3

From: Stephens, Chris <chris_stephens_at_admworld.com>
Date: Fri, 21 Mar 2008 09:27:16 -0500
Message-ID: <7070047601C21A4CB387D50AD3661F6E0950BF9F@050EXCHANGE.research.na.admworld.com>

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 (

            select a.projectnumber,

                   p.periodid,

                   last_value(a.fraction ignore nulls) over (partition

by a.projectnumber order by p.periodid) fraction,

                   last_value(a.code ignore nulls) over (partition by

a.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;    

CONFIDENTIALITY NOTICE:
        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.

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Mar 21 2008 - 09:27:16 CDT