Oracle9i significantly enhances both the functionality and performance of SQL to address the requirements of business intelligence operations. Among the most widely applicable enhancements are the new SQL analytic functions. These new functions simplify development and improve query performance for a broad range of analytic processing. In many cases, the analytic functions can improve query speed by a factor of 2 to 5 times.

The SQL analytic functions of Oracle9i build upon the functions introduced in Oracle8i, Release 2. This earlier release of Oracle8i included calculations for:

• Ranking ("Find the top 10 sales reps in each region")
  
  
• Moving-window aggregates (cumulative sums, moving averages and much more)
  
  
• Period-over-period comparisons ("What is the percentage growth from January, 2000 over January, 1999?")
  
  
• Ratio-to-report calculations ("What is January's sales as a percentage of the entire year's?")
  
  
• Statistical functions (linear regression, correlations)

Oracle9i additionally provides SQL support for:

• Inverse Percentiles - These functions allow queries to find the data which corresponds to a specified percentile value. For instance, users may find the median value of a data set by querying PERCENTILE_DISC(0.5).
  
  
• Hypothetical Ranks and Distributions - These functions allow queries to find what rank or percentile value a hypothetical data row would have if it were inserted into an existing data set. For example, a query could specify the salary for a newly hired employee and find where the salary would rank within the employee's department or division.
  
  
• Histograms - A new function performs the core calculations for a width-balanced histogram. For each row, this function returns the number of the histogram bucket appropriate for the data.
  
  
• FIRST/LAST Aggregates - Two new aggregate functions let queries specify sort orders for the rows within groups specified by a GROUP BY clause. The functions return a value from either the first or last row of each sorted group. For example, given a table holding departments, salaries and commissions for a company's sales representatives, these functions could sort the representatives within each department according to commission and then return the salary of each department's highest or lowest commission representative.
  

More Info

	Oracle9i for E-Business: Business Intelligence - Technical White Paper
	Oracle9i SQL Reference Release 1 (9.0.1): Chapter 6 - SQL Functions
	Oracle9i Data Warehousing Guide Release 1 (9.0.1): Chapter 19 - SQL for Analysis in Data Warehouses

Oracle9i Database Daily Features

Archives