Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Closed TopicStart new topic
> Help - to design procedure
ora99
post Apr 27 2012, 12:06 PM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 27-April 12
Member No.: 47,146



I have a table (refreshed weekly) with about 15million records and over 600 columns, table contains customer aggregated information and “BILLING_ACCOUNT” is the primary key in the table. There are two types of data in the table:
  1. with continuous data like “TOTAL_BILL” , “TOTAL_USAGE”...
  2. with categorical values like “CONNECTION_TYPE”, “OPTION_PLAN” now this may have either single character values like (‘A’,’B’,’C’), (‘1’,’2’,’3’) or like (‘option_1_single’, ‘option_2_single’, ‘option_1_double’, ‘option_2_double’) and new categories may be added or removed during table refresh.

Problem:
I have to generate report where I have to compute mean,max,min and sum for 1st type of data and frequency distribution for 2nd kind of data for about 100 fields for whole base (not at individual customer level ). Please note that list of 100 fields could change every week depending on business need.
Could I design a generic procedure to do this !?!
Go to the top of the page
 
+Quote Post
burleson
post Apr 27 2012, 03:37 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,314
Joined: 26-January 04
Member No.: 13



>> Could I design a generic procedure to do this !?!

Yes.


****************************

>> compute mean,max,min and sum for 1st type of data

I would use scalar subqueries to compute the min. max values:

http://www.dba-oracle.com/art_9i_scalar_subq.htm


*****************************
>> frequency distribution for 2nd kind of data for about 100 fields for whole base

I would write this in PL/SQL using bulk collect and forall

For syntax examples, see the code download in this book:

http://www.rampant-books.com/book_0601_plsql_tune.htm


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Closed TopicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd July 2014 - 10:20 PM