Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 1.7 November 28, 2000 TABLE OF CONTENTS 1) Pop Quiz: Database Buffer Cache 2) Did you know... 3) URLs of the Week: Books 4) SQL Tip: Final Episode of Analytic Functions 5) Oracle News: Brain Drain 6) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end): Database Buffer Cache --------------------------------------------------------- How do you "cache" an Oracle table in memory? --------------------------------------------------------- 2) DID YOU KNOW... --------------------------------------------------------- * that Steven Feuerstein, author of several PL/SQL books, has defined an open-source unit testing framework called utPLSQL? utPLSQL is being developed as part of a development methodology known as Extreme Programming. As a framework, utPLSQL defines a process and offers a set of database packages that PL/SQL developers can use to unit test their code. Find out more about the utPLSQL initiative at http://oracle.oreilly.com/utplsql/. * that Extreme Programming (XP) is a four-year old discipline aimed at providing "a deliberate and disciplined approach to software development"? Check out the XP Web site at http://www.ExtremeProgramming.org/. In particular, take a look at the XP rules and practices and see whether they make sense to you: http://www.ExtremeProgramming.org/rules.html. --------------------------------------------------------- 2) URLs OF THE WEEK: Books --------------------------------------------------------- I discussed Oracle book publishers in issue 1.4 of the Oracle Pro eXTRA. In addition to the ones I mentioned there, Macmillan and its subsidiaries also offer a number of books on Oracle: Sams Publishing http://www.mcp.com/sams/results_sams.cfm?parameter=oracle&scope=all QUE Publishing http://www.mcp.com/que/results_que.cfm?parameter=oracle&scope=all QUE recently released "Oracle8i from Scratch" by Dan Hotka. You can find out more about the book at http://www.mcp.com/que/detail_que.cfm?item=0789723697. --------------------------------------------------------- 3) SQL TIP: Final Episode of Analytic Functions --------------------------------------------------------- I spent the last couple of issues discussing Oracle's analytic functions. A number of you sent me very positive feedback and indicated that you'd like me to continue discussing them. Let's spend one more issue looking at these functions, and then we'll move on to other topics. Our examples will continue to be based on the EMP table in Oracle's demo account, SCOTT, as shown here: 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 JJONES MANAGER 297 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. So far our analytic functions are applied to all of the data rows in the entire query result set. We can actually use the OVER clause to specify a "sliding window." The windowing clause specifies a set (or "window") of rows to be processed by the analytic function. Let's see how this works with the following example: SQL> select empno, sal, sum(sal) over (order by empno rows between 1 preceding and 1 following) win_sal from emp; EMPNO SAL WIN_SAL ---------- ---------- ---------- 7369 800 2400 7499 1600 3650 7521 1250 5825 7566 2975 5475 7654 1250 7075 7698 2850 6550 7782 2450 8300 7788 3000 10450 7839 5000 9500 7844 1500 7600 7876 1100 3550 7900 950 5050 7902 3000 5250 7934 1300 4300 14 rows selected. First, the ORDER BY clause is used to sort the data by "empno." The sliding window is defined by the "ROWS BETWEEN" clause. Here, we asked Oracle to calculate win_sal using the following formula: win_sal = (current row) + (1 row prior to the current row) + (1 row after the current one) In the case of employee 7499, the win_sal value is equal to 1600 + 800 + 1250 = 3650. For employee 7369, there's no previous row, and thus win_sal is evaluated to 800 + 1600 = 2400. We can manipulate the ROWS BETWEEN clause to define a running total for the salary, as shown here: SQL> select empno, sal, sum(sal) over (order by empno rows between unbounded preceding and current row) running_sal from emp; EMPNO SAL RUNNING_SAL ---------- ---------- ----------- 7369 800 800 7499 1600 2400 7521 1250 3650 7566 2975 6625 7654 1250 7875 7698 2850 10725 7782 2450 13175 7788 3000 16175 7839 5000 21175 7844 1500 22675 7876 1100 23775 7900 950 24725 7902 3000 27725 7934 1300 29025 In the next example, we'll use the windowing function with the partition clause to tabulate the running total by department: SQL> select deptno, empno, sal, sum(sal) over (partition by deptno order by empno rows between unbounded preceding and current row) running_sal from emp; DEPTNO EMPNO SAL RUNNING_SAL ---------- ---------- ---------- ----------- 10 7782 2450 2450 10 7839 5000 7450 10 7934 1300 8750 20 7369 800 800 20 7566 2975 3775 20 7788 3000 6775 20 7876 1100 7875 20 7902 3000 10875 30 7499 1600 1600 30 7521 1250 2850 30 7654 1250 4100 30 7698 2850 6950 30 7844 1500 8450 30 7900 950 9400 Note that the ROWS BETWEEN clause refers to the physical offset of rows in relation to the current row. We can use the RANGE BETWEEN clause to determine the sliding window based on the "content" of a row. In the following example, we try to determine the minimum and maximum salary amounts for each department. The sliding window is defined by all of the employees within the current whose salary is less than the current employee, as well as the current employee. SQL> select deptno, empno, sal, min(sal) over (partition by deptno order by empno range between unbounded preceding and current row) dept_min_sal, max(sal) over (partition by deptno order by empno range between unbounded preceding and current row) dept_max_sal from emp; DEPTNO EMPNO SAL DEPT_MIN_SAL DEPT_MAX_SAL ---------- ---------- ---------- ------------ ------------ 10 7782 2450 2450 2450 10 7839 5000 2450 5000 10 7934 1300 1300 5000 20 7369 800 800 800 20 7566 2975 800 2975 20 7788 3000 800 3000 20 7876 1100 800 3000 20 7902 3000 800 3000 30 7499 1600 1600 1600 30 7521 1250 1250 1600 30 7654 1250 1250 1600 30 7698 2850 1250 2850 30 7844 1500 1250 2850 30 7900 950 950 2850 ************************************************************ A BETTER WAY TO FIND THE ORACLE POSITION YOU WANT! Now, you can register your career profile anonymously at ORAJOBS.com, where your qualifications and interests are automatically matched to open positions. REGISTER TODAY - Better jobs for hot Oracle talent, now only a click away! http://p-ad.net/ads.nsf/c?openagent&orajopx1127 ************************************************************ --------------------------------------------------------- 4) ORACLE NEWS: Brain Drain --------------------------------------------------------- Oracle announced a week ago that its executive vice president, Gary Bloom, would become CEO of Veritas Software, the storage software vendor. Bloom had spent more than 10 years with Oracle and was considered to be the rising star within the company. According to Bloom, "There was certainly no lack of opportunity for me at Oracle. At the same time, I've had a career desire to be a CEO. "In the case of Oracle, Larry is clearly the CEO of Oracle Corp., and as far as I can see, [will be] well into the foreseeable future... In a sense, that was a limiting factor in how far I can go at Oracle." Bloom's career move follows COO Ray Lane's split with Oracle in June. Refer to http://tm0.com/thestandard/sbct.cgi?s=64524413&i=279236&d=631201 for more details. With the departure of two senior executives within a six- month period, some analysts are wondering whether CEO Larry Ellison is becoming a hindrance to Oracle's growth. There's an interesting article that discusses whether Ellison is "the problem or the solution" at Oracle: http://www.thestreet.com/_yahoo/tech/software/1181412.html. * * * * There's an article on Internet Week that discusses Oracle's strategy for its Oracle9i release. The word "integration" seems to be a core aspect of the new product. Oracle9i aims to offers a closely integrated application server and database. In addition, it will offer integrated business intelligence functionality, including tools for online analytic processing (OLAP), data mining, and data extraction. The second keyword for the new release is "data warehousing." Apparently, Oracle will release the Oracle Warehouse Builder 3i data warehouse design and deployment tool, which includes metadata management integration, new data mapping and transformation features, and a flat file integration interface. This data warehousing product will again be closely integrated with the database. Find out more about Oracle's strategy at http://www.internetwk.com/story/INW20001116S0003. --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ: Database Buffer Cache --------------------------------------------------------- Caching an Oracle table basically implies loading the table into memory. Subsequent queries on this table will result in better performance because Oracle doesn't need to perform physical I/Os to retrieve the required data blocks; it can simply look up the table in memory. In practice, it might not always be possible to permanently cache a table in memory. First of all, there's no way we can cache a 10GB table in memory if we only have 500MB of RAM. Even if we allocate all physical memory to the database buffer cache, it's still not large enough for the table. Second, Oracle uses the least- recently-used (LRU) algorithm for caching data blocks in the database buffer cache, implying that a data block that hasn't been accessed for a long time may eventually be "swapped out of memory" to make room for data blocks required by the more recent SQL access. With all of these considerations in mind, we can still attempt to cache a table by issuing a dummy SQL SELECT statement on the table, as shown here: SQL> select * from countries; This query will cause Oracle to read all of the rows from the countries table into memory. This increases the likelihood that the data blocks belonging to the table will be resident in memory when subsequent queries are made against the countries table. As you can see, this isn't a very reliable way of loading a table into memory. A better approach is to take advantage of the subdivisions of the database buffer cache. The database buffer cache is actually made of three pools for different levels of usage. The "default" pool uses the default LRU algorithm for caching data blocks; the "keep" pool is for objects that you wish to keep in memory for as long as possible (e.g., small reference table); the "recycle" pool is an area for objects that do not need to be retained in memory (e.g., infrequently accessed tables). Here are some examples of associating a table with one of these pools: SQL> create table countries (...) storage (buffer_pool keep); SQL> alter table salary_history (...) storage (buffer_pool recycle); SQL> create table employees (...); By default, Oracle uses the default pool for data block caching. If you know of other methods of caching an Oracle table, let me know and I'll publish them in the next issue. --------------------------------------------------------- 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.