Re: SQL to generate percentage growth per time period?

From: Digeratus 2006 <digeratus2006_at_nospam.hotmaildotcom>
Date: 02 Dec 2007 12:13:37 GMT
Message-ID: <Xns99FAC617DC99Cdigeratus2006@63.218.45.20>

DG problem <skatefree_at_gmail.com> wrote in news:647c8500-6164-4691-8332- 99bdbc1dcab7_at_e6g2000prf.googlegroups.com:

> On Nov 28, 2:02 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> DG problem wrote:
>> > Just wondering what is the easiest way to show percentage growth of
>> > database objects per time period.
>>
>> > For example, what SQL would show the weekly percentage growth of
the
>> > following data?
>>
>> > alter session set nls_date_format= 'YYYYmmdd';
>> > create table t (time date,mb integer);
>> > insert into t values('20071117',103442);
>> > insert into t values('20071110',97592);
>> > insert into t values('20071103',93446);
>> > insert into t values('20071026',87648);
>> > insert into t values('20071020',84191);
>> > insert into t values('20071013',76495);
>> > insert into t values('20071006',72294);
>> > commit ;
>> > select * from t order by 1 desc ;
>>
>> > Thanks
>>
>> SELECT timepoint, delta_space_usage, delta_space_alloc,
>> total_space_usage, total_space_alloc
>> FROM TABLE(dbms_space.object_growth_trend_swrf(USER, 'T', 'TABLE'));
>> --
>> Daniel A. Morgan
>> Oracle Ace Director & Instructor
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>>
>> - Show quoted text -

> 
> You have to pay extra for some of the 10g views, does that include the
> SQL you gave above as it uses AWRF?
> 
> 
> Also, I still need to be able generate percentage change information
> (the above example was used for simplicity) based on the table I gave
> above if that is possible?
> 

I thimk that you want to know the weekly change between data values. You want to compare values of data from two distinct rows. You cannot do this easily using SQL. You need to use PL/SQL to do this. The DBMS_SPACE space package will do trend analysis for database table sizez.

It is one of the easier of the non-trivial things that you can do yourself using PL/SQL. If you know how to program using PL/SQL, then it sould take about 10 minutes. If you know how to program, but not in PL/SQL, then it might take a few hours, but that is time well spent. If you do not know how to program, ask again, I'm sure that someone will be willing to demonstrate their programming prowess.

HTH
Andy Young Received on Sun Dec 02 2007 - 06:13:37 CST