Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.2 January 25, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) This Issue's URLs 3) Did you know... 4) SQL Tip: Function-based Indexes 5) Answer to the Pop Quiz 6) Your Feedback --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- Once connected to the database, how can you tell whether you logged in as SYS or as SYS AS SYSDBA? --------------------------------------------------------- 2) THIS ISSUE'S URLs --------------------------------------------------------- Oracle has bundled its ERP (enterprise resource planning), CRM (customer relationship management), B2B (business-to-business) exchange, financials management, and other applications into the Oracle e-Business Suite. With the database market eventually maturing, implying slower growth of licensing fees, Oracle is counting on the e-Business Suite to be its "next big thing." To find out what it's all about, check out the main Web page at http://www.oracle.com/applications/. The main page provides high-level marketing information. To get to the "meat" of the product, visit http://appsnet.oracle.com/. This site contains the user guides, white papers, and implementation guides for each of the products belonging to the e-Business Suite. Before you can use it, however, you have to register to become an Appsnet member at http://www.oracle.com/appsnet/membership/register.html?/admin/account/oan_reg.html. Once registered, you can access the documentation, including user guides, newsletters, presentations, and white papers at http://www.oracle.com/appsnet/members/index.htm. The products page, http://www.oracle.com/appsnet/products/index.htm, provides information on the products that belong to the suite. As you'll see, there are QUITE a few products indeed! One way of tackling the subject matter is to see whether Oracle has a customized implementation for your industry. If so, it'll give you a good reference point for understanding the product: http://www.oracle.com/appsnet/industry/index.htm. It's impossible to get a complete view of the product by relying solely on the online reference. To take courses on the suite, or to get Oracle (and its solutions partners) consulting help, go to http://www.oracle.com/appsnet/services/index.htm. Finally, Oracle is organizing a conference on its e-Business Suite in February. Find out more at http://www.oracle.com/appsworld/index.html?content.html. --------------------------------------------------------- 3) DID YOU KNOW... --------------------------------------------------------- * that there's an interesting online tutorial on SQL by Philip Greenspun: http://www.arsdigita.com/books/sql/? The company that publishes the tutorial, ArsDigita (http://www.arsdigita.com/), also offers other interesting books, although they're not directly related to Oracle. Check them out anyway at http://www.arsdigita.com/books/. * that Quest software (http://www.quest.com) has bundled products designed for managing servers running Oracle e- Business Suite? http://www.quest.com/solutions/orapps.asp --------------------------------------------------------- 4) SQL TIP: Function-based Indexes --------------------------------------------------------- We often need to do case-insensitive searches, but the use of a function on an indexed column turns off the index. In Oracle8i, we can create indexes on functions based on columns. So if you want to do a case-insensitive name search, you can now create an index like this: create index i on people (upper(person_name)); The index will be used if your query looks like this: WHERE upper(person_name) = upper(in_person_name); There are a few steps that you must take to make sure this works: 1. Set the following parms in init.ora: COMPATIBLE = 8.1.6 QUERY_REWRITE_ENABLED = TRUE 2. Connect as sys and grant the following system privileges to the relevant database user account (e.g., user1): QUERY REWRITE or GLOBAL QUERY REWRITE 3. As user1, create table and index, and make sure to collect statistics: create table t (t1 varchar2(10)); create index i on t(upper(t1)); insert into t values('xxxx'); insert into t values('fas'); insert into t values('adf'); insert into t values('aex'); analyze table t compute statistics; 4. Write your queries: select c.t1 from t c where upper(c.t1) = upper('smith') and upper(c.t1) is not null; 5. In fact, function-based indexes also work for range scans. For example, the following WHERE clauses will cause the function-based index i to be used: WHERE upper(c.t1) like upper(in_person_name); or WHERE upper(c.t1) like upper(in_person_name) || '%' --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- In a simple case, you can use SHOW USER in SQL*Plus to determine the name of the currently connected database user. However, you can't use this option because both SYS and SYS AS SYSDBA will be returned as SYS if you run the SHOW user command. Even if you query the v$session view (i.e., you issue the query "select username from v$session"), you won't be able to tell the difference. Once again, you'll see SYS as the output. To address this, you can obtain the current session ID from v$session, as shown here: SQL> select sid,username,osuser,program from v$session; SID USERN OSUSER PROGRAM ---- ----- -------------- ---------------------------------- ------------------------------ 1 SYSTEM ORACLE.EXE 2 SYSTEM ORACLE.EXE 3 SYSTEM ORACLE.EXE 4 SYSTEM ORACLE.EXE 5 SYSTEM ORACLE.EXE 6 SYSTEM ORACLE.EXE 7 SYS Administrator SVRMGRL.EXE 9 SYS Administrator C:\WINNT\Profiles\All Users\Start Menu\Programs\Oracle for Windo 8 rows selected. Looking at the program column, you'll be able to identify the operating system application program that you used to connect to the database. Using the corresponding session ID, SID, you can query the V$SESSION_CONNECT_INFO view: SQL> select SID,AUTHENTICATION_TYPE,OSUSER from V$SESSION_CONNECT_INFO; SID AUTHENTI OSUSER ---- -------- -------------- 7 OS Administrator 9 DATABASE Administrator 9 DATABASE Administrator 9 DATABASE Administrator If the authentication type is DATABASE, you logged in as the regular SYS user, but if the type is OS, it means you logged in as SYS AS SYSDBA. --------------------------------------------------------- 6) YOUR FEEDBACK --------------------------------------------------------- Thanks very much to those who took the time to respond to my informal survey. It appears that many of you liked the idea of a bi-weekly eNewsletter that offers solid information (SQL, DBA, industry buzz) on Oracle. I'll try to work on that. Meanwhile, if you have any new ideas regarding the eXTRA, please don't hesitate to email me at mailto:GChan@ProcaseConsulting.com. --------------------------------------------------------- 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) 2001 http://www.pinnaclepublishing.com All rights reserved.