Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 1.5 October 31, 2000 TABLE OF CONTENTS 1) Pop Quiz: Analytic Functions 2) Did You Know... 3) URLs of the Week: ERP Links 4) SQL Tip: ROLLUP and CUBE Functions 5) Oracle News: $10 Million Challenge 6) Answer to the Pop Quiz 7) Correction --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What are Oracle's analytic functions? --------------------------------------------------------- 2) DID YOU KNOW... --------------------------------------------------------- * that Oracle has guaranteed that its Oracle9i Application Server will run three times faster than Web sites based on IBM DB2 or Microsoft SQL Server database technology? If you find that this isn't the case, Oracle will pay you $1,000,000. http://www.oracle.com/guarantee/index.html?content.html --------------------------------------------------------- 3) URLs OF THE WEEK: ERP Links --------------------------------------------------------- This week we're going to explore a few links related to ERP (enterprise resource planning). According to AskJeeves.com, "ERP systems are accounting-oriented information systems for identifying and planning the enterprise-wide resources needed to take, make, distribute, and account for customer orders. ERP systems were originally extensions of MRP II systems, but have since widened their scope. An ERP system also differs from the typical MRP II system in technical requirements such as relational database, use of object-oriented programming language, computer aided software engineering tools in development, client/server architecture, and open system portability." The following are some of the major vendors of ERP systems: * SAP: http://www.sap.com/ * PeopleSoft: http://www.peoplesoft.com/ * Oracle: http://www.oracle.com/applications/ * J.D. Edwards: http://www.jdedwards.com/ * Baan: http://www.baan.com/index.html --------------------------------------------------------- 4) SQL TIP: ROLLUP and CUBE Functions --------------------------------------------------------- Oracle8i has included two new features, ROLLUP and CUBE, for data summarization. They're designed to provide additional levels of grouping in a query. Let's see how this works. Here's a listing of the rows in the EMP table of Oracle's demo account, SCOTT: SQL> select deptno, empno, ename, job, sal from emp order by deptno, empno; DEPTNO EMPNO ENAME JOB SAL --------- --------- ---------- --------- --------- 10 7782 CLARK MANAGER 2450 10 7839 KING PRESIDENT 5000 10 7934 MILLER CLERK 1300 20 7369 SMITH CLERK 800 20 7566 JONES MANAGER 2975 20 7788 SCOTT ANALYST 3000 20 7876 ADAMS CLERK 1100 20 7902 FORD ANALYST 3000 30 7499 ALLEN SALESMAN 1600 30 7521 WARD SALESMAN 1250 30 7654 MARTIN SALESMAN 1250 30 7698 BLAKE MANAGER 2850 30 7844 TURNER SALESMAN 1500 30 7900 JAMES CLERK 950 14 rows selected. If we wish to find the number of employees by department, we issue the query: SQL> select deptno, count(*) from emp group by deptno; DEPTNO COUNT(*) --------- --------- 10 3 20 5 30 6 If we also wish to include the total number of employees in the output, we need to include the UNION clause, as shown here: SQL> select deptno, count(*) from emp group by deptno union select to_number(null), count(*) from emp; DEPTNO COUNT(*) --------- --------- 10 3 20 5 30 6 14 Starting from Oracle8i, we can use the ROLLUP option to achieve the same result: SQL> select deptno, count(*) from emp group by rollup(deptno); DEPTNO COUNT(*) --------- --------- 10 3 20 5 30 6 14 By simply adding the invoking ROLLUP facility in the GROUP BY clause, we direct Oracle to summarize the data at levels above the columns specified, all the way to the grand total. Note that Oracle reports the grand total by leaving the GROUP BY column blank. If the GROUP BY column also contains null values, it might be difficult to distinguish those values from the grand total row. Fortunately, we can use a special function called GROUPING to report the current summarization level. The function returns two values: 0 indicates the current row is grouped at the specified GROUP BY level, and 1 indicates the row is grouped at a higher level. SQL> select deptno, count(*), grouping(deptno) from emp group by rollup(deptno); DEPTNO COUNT(*) GROUPING(DEPTNO) --------- --------- ---------------- 10 3 0 20 5 0 30 6 0 14 1 Now we can take advantage of the GROUPING function to format the output: SQL> select decode(grouping(deptno),0,to_char(deptno),'Total') deptno, count(*) from emp group by rollup(deptno); DEPTNO COUNT(*) ---------------------------------------- --------- 10 3 20 5 30 6 Total 14 The ROLLUP feature can in fact be applied to multiple columns. The result is multiple levels of rollup, as illustrated here: SQL> select deptno, job, count(*), grouping(deptno), grouping(job) from emp group by rollup(deptno, job); DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB) --------- --------- --------- ---------------- ------------- 10 CLERK 1 0 0 10 MANAGER 1 0 0 10 PRESIDENT 1 0 0 10 3 0 1 20 ANALYST 2 0 0 20 CLERK 2 0 0 20 MANAGER 1 0 0 20 5 0 1 30 CLERK 1 0 0 30 MANAGER 1 0 0 30 SALESMAN 4 0 0 30 6 0 1 14 1 1 As shown in this example, we're able to count the employees by 1) department and job; 2) department; and 3) grand total. Similar to ROLLUP is the CUBE function. CUBE groups data at multiple dimensions. In particular, it summarizes data based on all possible combinations of the columns specified in the GROUP BY clause. Let's see how this works: SQL> select deptno, job, count(*), grouping(deptno), grouping(job) from emp group by cube(deptno, job); DEPTNO JOB COUNT(*) GROUPING(DEPTNO) GROUPING(JOB) --------- --------- --------- ---------------- ------------- 10 CLERK 1 0 0 10 MANAGER 1 0 0 10 PRESIDENT 1 0 0 10 3 0 1 20 ANALYST 2 0 0 20 CLERK 2 0 0 20 MANAGER 1 0 0 20 5 0 1 30 CLERK 1 0 0 30 MANAGER 1 0 0 30 SALESMAN 4 0 0 30 6 0 1 ANALYST 2 1 0 CLERK 4 1 0 MANAGER 3 1 0 PRESIDENT 1 1 0 SALESMAN 4 1 0 14 1 1 The CUBE function includes the output generated by ROLLUP. In addition, it includes the number of employees by job---independent of the department number. In summary, the ROLLUP and CUBE features greatly simplify SQL coding. We no longer have to spend hours trying to come up with those UNION statements ourselves! --------------------------------------------------------- 5) ORACLE NEWS: $10 Million Challenge --------------------------------------------------------- The latest chapter in the ongoing war of words between Microsoft and Oracle focused on database performance. Microsoft SQL Server 2000 currently reigns as No. 1 in the TPC-C rankings. The TPC-C is an industry-standard test that measures transaction-processing-system throughput in terms of orders processed per minute. In a speech at Oracle OpenWorld in October, Oracle CEO Larry Ellison offered $10 million US to anyone who could get any application to run on Microsoft's SQL Server 2000 that won the top TPC-C price/performance ranking. Microsoft's lawyers apparently had contacted Ellison, threatening to sue him if he continued to "misinterpret" the performance of Microsoft's products. For further information, go to http://dailynews.yahoo.com/h/zd/20001026/tc/microsoft_to_oracle_cease_and_desist_1.html --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ: Analytic Functions --------------------------------------------------------- Oracle8i Release 8.1.6 included a new type of function for performing analytic processing such as data ranking and aggregation. These functions are included in the SELECT clause, using the following format: ([]) OVER([]) where refers to the name of the analytic function provided by Oracle, refers to the optional input parameters for the analytic function, and refers to the additional directives for manipulating the data. Using the EMP table referenced in the previous section, we can use the AVG analytic function to calculate the average employee salary, as shown here: SQL> select empno, avg(sal) over() avg_sal from emp order by empno; EMPNO AVG_SAL ---------- ---------- 7369 2073.21429 7499 2073.21429 7521 2073.21429 7566 2073.21429 7654 2073.21429 7698 2073.21429 7782 2073.21429 7788 2073.21429 7839 2073.21429 7844 2073.21429 7876 2073.21429 7900 2073.21429 7902 2073.21429 7934 2073.21429 With the use of analytic functions, we can localize the summarization operation to individual columns in the SELECT clause, removing the need to specify the GROUP BY operation. In the "old" days, we had to use the following SQL to display the individual's salary as well as the overall average in one output list: SQL> select e1.empno, e1.sal, avg(e2.sal) from emp e1, emp e2 group by e1.empno, e1.sal; EMPNO SAL AVG(E2.SAL) --------- --------- ----------- 7369 800 2073.2143 7499 1600 2073.2143 7521 1250 2073.2143 7566 2975 2073.2143 7654 1250 2073.2143 7698 2850 2073.2143 7782 2450 2073.2143 7788 3000 2073.2143 7839 5000 2073.2143 7844 1500 2073.2143 7876 1100 2073.2143 7900 950 2073.2143 7902 3000 2073.2143 7934 1300 2073.2143 14 rows selected. By default, the aggregate function is applied to the entire output data set. We can change this by specifying the PARTITION BY sub-clause in the OVER clause, as shown here: SQL> select deptno, empno, avg(sal) over(partition by deptno) avg_sal from emp order by deptno, empno; DEPTNO EMPNO AVG_SAL ---------- ---------- ---------- 10 7782 2916.66667 10 7839 2916.66667 10 7934 2916.66667 20 7369 2175 20 7566 2175 20 7788 2175 20 7876 2175 20 7902 2175 30 7499 1566.66667 30 7521 1566.66667 30 7654 1566.66667 30 7698 1566.66667 30 7844 1566.66667 30 7900 1566.66667 Now, the average salary is calculated by department. I'll discuss the other analytic functions in future issues of the eXTRA. Stay tuned! --------------------------------------------------------- 7) CORRECTION --------------------------------------------------------- In issue 1.4 of the eXTRA (October 16), I included the following query: SELECT sum(pins) pins, sum (reloads) misses FROM v$library; It should have been: SELECT sum(pins) pins, sum (reloads) misses FROM v$librarycache; --------------------------------------------------------- Well, that's it for this week. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2000 http://www.pinnaclepublishing.com All rights reserved.