Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 1.3 October 4, 2000 TABLE OF CONTENTS 1) Did you know... 2) 10-Second Tutorial: Bitmap Indexes 3) This Issue's URLs 4) In the News: Oracle9i 5) SQL Tip: Log Miner --------------------------------------------------------- 1) DID YOU KNOW... --------------------------------------------------------- * that the beta release of Oracle Script Debugger (OSD) for PL/SQL is currently available for download? OSD is a PL/SQL debugging environment on the Windows platform. The product apparently can be integrated with existing tools such as Microsoft Script Debugger, Microsoft Visual InterDev, and SQL*Plus to create a complete development environment for maintaining, testing, and deploying PL/SQL programs. http://technet.oracle.com/tech/nt/osd/ * that Quest Software (http://www.quest.com) also provides a PL/SQL debugger? Quest Software's TOAD offers an integrated PL/SQL editing environment. Bundled with the product is a debugger that allows you to debug PL/SQL programs one line at a time as it executes on the server. http://www.quest.com/toad/optional_modules.asp#debug * that Oracle8i offers the DBMS_DEBUG utility package, whose routines can be embedded in your PL/SQL programs for debugging purposes? * that if all else fails, you can always use DBMS_OUTPUT to display text messages to the terminal during program execution? --------------------------------------------------------- 2) 10-SECOND TUTORIAL: Bitmap Indexes --------------------------------------------------------- Bitmap indexes were introduced in Oracle 7.3 to improve the query performance of data warehouses. In particular, bitmap indexes are designed to handle queries with the following characteristics: * Large tables are accessed * The WHERE clause in the query includes non-selective columns (e.g., gender). In particular, bitmap indexes are best for columns in which the number of distinct values is small compared to the number of rows in the table (low cardinality columns). If the values in a column are repeated more than 100 times, the column is a candidate for a bitmap index. * The equality condition is used in the WHERE clause (e.g., gender = 'FEMALE'). * The column in the WHERE clause contains very few valid values (e.g., gender has valid values of MALE and FEMALE). * Ad hoc queries with large WHERE clauses are used for handling "what-if" scenarios (e.g., display the total invoiced amount by "male" sales representatives who are "married"). * There are "star" schemas with multiple joins. A star schema is typically found in a data warehouse in which a "fact" table (e.g., the sales order history) can be viewed from multiple "dimensions" (e.g., querying the sales order by the customer gender, sales reps' marital status, etc.). * Bitmap indexes are primarily intended for data warehousing applications where users typically query the data rather than update it. In a bitmap index, Oracle constructs a bitmap for each key value. Each bit in the bitmap corresponds to a possible value for the indexed column. If the bit is set, it means that the row contains the value denoted by the bit. A mapping function converts the bit position to an actual rowid, so the bitmap index provides the same functionality as a regular index even though it uses a different representation internally. If the number of different key values is small, bitmap indexes are very space efficient. Bitmap indexes are integrated with the Oracle cost-based optimizer. The optimizer considers bitmap indexes and other available access methods, such as regular B*-tree indexes and full table scan, and chooses the most efficient method. Bitmap indexing efficiently merges indexes that correspond to several conditions in a WHERE clause. Rows that satisfy some, but not all conditions are filtered out before the table itself is accessed. This improves response time. In particular, AND and OR conditions in the WHERE clause of a query can be quickly resolved by performing the corresponding Boolean operations directly on the bitmaps before converting the resulting bitmap to rowids. If the resulting number of rows is small, the query can be answered very quickly without resorting to a full table scan of the table. Let's consider the example of an invoice table: CUSTOMER # MARITAL STATUS GENDER AMOUNT 1 single male $100 2 married female $900 3 divorced female $500 Since MARITAL STATUS and GENDER are non-selective columns, we can create bitmap indexes on these columns. The CUSTOMER # column is highly selective; hence, we should not generate a bitmap index on it. Each of the MARITAL STATUS and GENDER columns will have their own bitmaps, as shown here: MARITAL STATUS='single' MARITAL STATUS='married' MARITAL STATUS='divorced' 1 0 0 0 1 0 0 0 1 Now, for customer 1 who is single, his/her marital status bitmap will be '100'. Customer 2 will have a bitmap of '010', and customer 3's bitmap will be '001'. Applying the same strategy to the gender bitmap, in which '10' corresponds to MALE and '01' corresponds to FEMALE, we can set customer 1's gender bitmap to '10'; customers 2 and 3 have a bitmap of '01'. After the bitmaps have been created, we can pose "what- if" questions such as, "How many customers are divorced and male?" select count(*) from customers where marital_status = 'divorced' and gender = 'male'; Bitmap indexes can process this query with great efficiency by merely constructing a bitmap for the predicates in the WHERE clause and comparing it to the marital status and gender bitmaps. Simple and efficient Boolean logic will allow Oracle to quickly retrieve the rows from the invoice table. --------------------------------------------------------- 3) THIS ISSUE'S URLs --------------------------------------------------------- Oracle offers a Web-based service for managing a sales organization. The Web site, http://www.OracleSalesOnline.com, provides Web-based maintenance of customer information, coordination of the sales staff, and forecasting. This product poses an interesting challenge to traditional customer relationship management (CRM) powerhouses such as Siebel (http://www.siebel.com). http://www6.oraclesalesonline.com/ofs313/welcome.html Nevertheless, Oracle is not the first company to offer such a service. A San Francisco-based company, Salesforce.com, has been delivering a similar service for a while now. Their business model is an online subscription service, enabling users to manage and share sales data over the Web. Interestingly, Salesforce.com was initially funded by Oracle; however, their ties have been weakened (if not broken) ever since Oracle made its announcement. http://www.salesforce.com/ A discussion of the CRM marketplace wouldn't be complete without mentioning Siebel. The company was started in 1993 by an ex-VP of sales at Oracle. Now it has a market capitalization in excess of 40 billion US dollars. http://www.siebel.com/ --------------------------------------------------------- 4) IN THE NEWS: Oracle9i --------------------------------------------------------- At Oracle OpenWorld, Larry Ellison is expected to announce the release of Oracle9i---a revision of the Oracle8i database server. Among other improvements, Oracle9i is expected to include improved administration and security functions. Ellison is also expected to announce the release of Oracle Internet Application Server (iAS) 9i, with leading-edge ("patent-pending") caching technology for faster access to Web pages. Finally, Ellison also plans to announce major improvements for Oracle's ERP product suite, Oracle Applications. The product is now rebranded as Oracle E-Business Suite Release 11i. Oracle will market the product on its reliability and cost effectiveness for handling procurement, human resources management, and customer relationship management tasks. --------------------------------------------------------- 5) SQL TIP: Log Miner --------------------------------------------------------- Oracle's redo log files contain database recovery information. In particular, you can use them during database recovery to reapply committed transactions (roll-forward) and undo transactions that did not complete when the database crashed (roll-back). Oracle8i supplies a log analyzer tool called DBMS_LOGMNR for you to examine the individual entries in the redo log files. Here are the steps for configuring the log miner: 1) Create and configure an operating system directory for storing the log miner file. First, create an operating system directory, say, c:\oralog. Next, add this directory to the UTL_FILE_DIR parameter in the initialization file: UTL_FILE_DIR = c:\oralog 2) Create the log miner dictionary file: SQL> execute dbms_logmnr_d.build(dictionary_filename=>'logminer.ora', dictionary_location=>'c:\oralog'); 3) Specify the redo log files that need to be examined: SQL> execute dbms_logmnr.add_logfile( logfilename=>'c:\oracle\oradata\dev\logdev1.ora', options=>dbms_logmnr.NEW); SQL> execute dbms_logmnr.add_logfile( logfilename=>'c:\oracle\oradata\dev\logdev2.ora', options=>dbms_logmnr.ADDFILE); Note that if you have five redo files to monitor, you must invoke with the ADD_FILE option five times. 4) Start the log miner utility: SQL> execute dbms_logmnr.start_logmnr( dictfilename=>'c:\oralog\logminer.ora'); 5) The redo log entries can then be retrieved using a data dictionary view, v$logmnr_contents: SQL> select sql_redo, sql_undo from v$logmnr_contents; Note that the sql_redo column refers to the transaction issued by a user, whereas sql_undo refers to the transaction used to undo the user request. --------------------------------------------------------- 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.