more sql 10.2.0.3

  • From: "Stephens, Chris" <chris_stephens@xxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • 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 (

            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.



Other related posts:

  • » more sql 10.2.0.3