Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 1.2 September 19, 2000 TABLE OF CONTENTS 1) 10-Second Tutorial: CHAR vs. VARCHAR2 vs. VARCHAR 2) Pop Quiz 3) This Issue's URLs 4) News: Oracle's Falling Share Price 5) Answer to the Pop Quiz 6) SQL Tip: More Database Dictionary Views --------------------------------------------------------- 1) 10-SECOND TUTORIAL: CHAR vs. VARCHAR2 vs. VARCHAR --------------------------------------------------------- In this issue's tutorial, we'll take a look at the differences among CHAR, VARCHAR2, and VARCHAR data types. CHAR stores fixed-length strings. For example, if you store a single character field into a CHAR(5) column in the database, the stored value will be right padded with four spaces by Oracle, as shown here: SQL> create table tmp (str_char char(5)); SQL> insert into tmp (str_char) values ('Y'); SQL> select '<' || str_char || '>' from tmp; '<'||ST ------- On the other hand, VARCHAR2 and VARCHAR are used to store variable-length strings. Although VARCHAR2 and VARCHAR have identical definitions, Oracle recommends that we use VARCHAR2 as the data type for variable-length strings. It has indicated its intention to change VARCHAR's definition in the future. (Note that Oracle has been warning us since Oracle7 that VARCHAR's definition may change. In any case, it's definitely safer to use VARCHAR2.) A VARCHAR2 field is actually made up of two components: string length and data content. For example, if you store a single character field into a VARCHAR2(5) column, Oracle will not right pad spaces for you: SQL> create table tmp (str_varchar2 varchar2(5)); SQL> insert into tmp (str_varchar2) values ('Y'); SQL> select '<' || str_varchar2 || '>' from tmp; '<'||ST ------- Let's compare the CHAR and VARCHAR2 data types to see which one is better suited for use in a database application: 1. Size - CHAR uses more space for long strings, whereas VARCHAR2 requires more space for short ones. A CHAR(1000) field implies that it will be padded with 999 blank characters if you store a 1-character string, and each blank character takes up space. On the other hand, if you space a 1-character string into a VARCHAR2(1000) field, Oracle simply requires that the length component and a 1-character string be maintained. Nevertheless, a VARCHAR2(1) field will require more than space its CHAR(1) counterpart because the VARCHAR2(1) has the extra overhead of storing the length of the field, which is 1 in this case. This may not be much in a regular online transaction processing system (OLTP); however, in a data warehouse in which tables may have hundreds of millions of rows, an extra byte for each row will quickly add up. 2. Application development - It's easier to user VARCHAR2 fields for string comparison because we don't need to include the right padded spaces in the comparison statement, as illustrated here: if str_varchar2 = 'Y' then ... On the other hand, if we wish to check the content of a CHAR field, we must first check its size and right pad accordingly: if str_char = 'Y ' then ... Worse still, if we wish to compare a CHAR field to a VARCHAR2 field, we must do the following: if str_varchar2 = rtrim(str_char) then ... Failing to include the "rtrim" (right-trimming the blanks) built-in function will cause to comparison to fail. Recommendation: VARCHAR2 for all strings whose size is greater than 1---e.g. VARCHAR2(5), VARCHAR2(2000). For character strings of size 1, you can still use VARCHAR2(1) to ensure consistency in your choice of data types. Alternatively, you should use CHAR(1) if the extra storage overhead of VARCHAR2(1) is a concern for you. --------------------------------------------------------- 2) POP QUIZ (answer at the end) --------------------------------------------------------- Who is generally credited with originating the idea of relational database management systems (RDBMS)? --------------------------------------------------------- 3) THIS ISSUE'S URLs --------------------------------------------------------- * Oracle OpenWorld will be held in San Francisco Oct. 1-6. This year's annual conference organized by Oracle features hundreds of exhibitors, technical sessions with topics ranging from ERP to Web development, and keynote speakers such as Intel CEO Craig Barrett, Sun CEO Scott McNealy, and, of course, Oracle CEO Larry Ellison. http://www.oracle.com/openworld/ * International Oracle User Group (IOUG) is a worldwide organization with more than 10,000 members and chapters in North America, Europe, and Asia. IOUG is independent of Oracle, and it offers useful resources at its Web site such as technical papers on data warehousing and development tools. In addition, their discussion forum offers an effective means of posting technical questions and obtaining answers from fellow Oracle professionals. To find a local chapter of IOUG, go to their home page and follow the "Oracle User Groups" link. http://www.ioug.org/ * IOUG also organizes its own conference, called IOUG-A Live! 2001 to be held from April 30 to May 3, 2001. If you wish to present a paper there, you can go to http://www.ioug.org/ and click on the IOUG-A Live! 2001 link to find out how. Note that the deadline for submitting an abstract is Oct 16. --------------------------------------------------------- 4) NEWS: Oracle's Falling Share Price --------------------------------------------------------- Oracle announced on September 14 that its first quarter profits had beat earnings estimates, earning 17 cents per share. Oracle was reporting its post-Ray Lane quarterly results, and obviously the numbers were closely followed by the investors to see whether Oracle's momentum was slowing. Oracle's share price dropped more than 5 percent on the following day, as investors ignored its $501 million quarterly earnings and instead focused on its "poor" growth in the applications business. To put things in perspective, Oracle grew its applications business by 40 percent, while the analysts and investors had expected 60 percent! *************************************************************** JOB-OF-THE-WEEK *************************************************************** Shape the future of computer entertainment. Sony Computer Entertainment America (SCEA), the marketer and distributor of the PlayStation® and PlayStation®2 entertainment systems is currently looking for seasoned Oracle professionals to join our team in Foster City, California. To apply for a Sr. Oracle Programmer Analyst or Oracle DBA position, email resume to mailto:hr_staffing@playstation.sony.com *************************************************************** --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- In 1970, Dr. E.F. Codd published a paper in the Association for Computing Machinery (ACM) journal, Communications of the ACM. In it he introduced the concept of a relational model for accessing data in a database. Prior to the relational model, data was primarily organized in a hierarchical or network database. Dr. Codd's proposal spawned new database technologies and had a profound impact on the IT industry. Hierarchical databases such as IBM's IDBMS have been phased out over time, and today, the database market is dominated by relational database management systems (RDBMS) such as Oracle, Sybase, Informix, IBM DB2, and Microsoft SQL Server. Now that the data is stored in a relational database, how do we effectively retrieve and analyze it for our bosses? Dr. Codd has an answer for that as well. In 1993, he coined the term OLAP---Online Analytical Processing---to describe the distinguishing characteristics of an OLAP product or system. Check out http://www.hyperion.com/olapterms.cfm for more information. --------------------------------------------------------- 6) SQL TIP: More Database Dictionary Views --------------------------------------------------------- In the last issue of Oracle Pro eXTRA, we started looking at Oracle's data dictionary views. This subject is important enough that we should take another look at it before moving on to explore other topics. As I mentioned last week, I always query the user_users view to ensure that I'm logged into the correct account; however, connecting to the correct account does not mean I'm connected to the right machine! So I would issue the following query to verify the database instance: SQL> select * from v$instance; INSTANCE_NUMBER INSTANCE_NAME --------------- ---------------- HOST_NAME ---------------------------------------------------------------- VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_ ----------------- --------- ------- --- ---------- ------- ----------- LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ---------- --- ----------------- ------------------ 1 dev server1 8.1.6.0.0 10-SEP-00 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE The query output indicates that the database instance is "dev", running on the "server1" machine, and the database version is 8.1.6 (Oracle8i). Knowing that I'm in the correct database, I can proceed with my SQL command. This type of confusion can arise when an application has several installations: a development database, a test database, and a production one. Often the DBA will create the same application account in all environments with the same user name and password. We could do the following to avoid this confusion: 1. Define the proper SQL prompt using the SET command: SQL> set sqlprompt "DEV> " DEV> Note that the "set sqlprompt" command changes the command prompt in SQL*Plus to the string following the "sqlprompt" keyword. 2. While it is reasonable to create the same application account in each of the development, test, and production databases, we should ensure that the account has a different password in each of these environments. For example, the HR application has passwords "dev", "test", and "prod", respectively. Now the chance of logging into the wrong database and dropping the wrong table will be greatly reduced. * * * * * * * To find out the operating system data files that belong to the database and their size, you can try the following: SQL> select file_name, tablespace_name, bytes from dba_data_files; FILE_NAME TABLESPACE_NAME BYTES ----------------------- ----------------- ---------- /user1/rbs1.dbf RBS 1614815232 /user2/rbs2.dbf RBS 1609572352 ... By summing up the BYTES column above, we can determine the amount of space allocated to the database. The allocated disk space is divided into two categories: used space and free space. Space is used for storing data such as application tables and Oracle's internal data dictionary. Oracle manages the allocation/deallocation of these data files internally. We cannot directly, say, create a table on a datafile; instead, we create it on a tablespace. A tablespace can be viewed as a logical "flat space" that may span one or more data files. Issuing the following query will display all of the tablespaces for the current database: SQL> select tablespace_name from dba_tablespaces; TABLESPACE_NAME ------------------------------ RBS USER ... Next we can find out how much free space is available by entering: SQL> select * from dba_free_space; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS ------------------------------ ---------- ---------- ---------- ---------- RBS 102 136962 4096 2 USER 105 698 2048 1 ... The sum of the BYTES column is the total free space available in the database. If you're asked, "What is the size of the database?" and you answer, "It depends," you're not too far off! If you want to know the size of operating system space allocated to your Oracle database, you should look at dba_data_files. (You should also include the other operating systems such as the redo log files, but I'll discuss that in a future issue.) If you want to know the amount of space used by your database, you should subtract the total free space returned by dba_free_space from the above number. Indeed, nothing is straightforward! --------------------------------------------------------- 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 weekly eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2000 http://www.pinnaclepublishing.com All rights reserved.