BN.com Gift Guide

Oracle 7 for Dummies

Overview

Behind the plain-looking facade of Oracle7 is a relational database engine far more powerful than anything else on the market. Now, discover the unbridled power of the Oracle7 program's unique database capabilities and turbocharge your Standard Query Language in no time with the latest version of this monster database program.

Whether you're working on a desktop PC, a Power Mac, a UNIX workstation, or an IBM mainframe, Oracle7 For Dummies can get you up to speed with practical ...

See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (15) from $1.99   
  • Used (15) from $1.99   
Close
Sort by
Page 1 of 2
Showing 1 – 7 of 15 (2 pages)
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$1.99
Seller since 2008

Feedback rating:

(16239)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

Very Good
John Wiley & Sons Inc (Computers), 02/28/1997, Paperback, Very Good condition. With CD!

Ships from: Frederick, MD

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2009

Feedback rating:

(6935)

Condition: Acceptable
Book selection as BIG as Texas.

Ships from: Dallas, TX

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2014

Feedback rating:

(1827)

Condition: Good
Good used copy. May have minor markings.

Ships from: Hillsboro, OR

Usually ships in 1-2 business days

  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$2.39
Seller since 2009

Feedback rating:

(3725)

Condition: Good
01/03/1997 Paperback Used-Good Book in good or better condition. Dispatched same day from US or UK warehouse.

Ships from: Valley Cottage, NY

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$2.74
Seller since 2009

Feedback rating:

(3725)

Condition: Good
01/03/1997 Paperback Used-Good Book in good or better condition. Dispatched same day from US or UK warehouse.

Ships from: Valley Cottage, NY

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$4.90
Seller since 2006

Feedback rating:

(121)

Condition: Like New
"Softcover in near mint condition, pages tight, clean, part I has highlights, no reading crease, includes sealed CD-ROM, Publ. IDG Books Worldwide 1997. Shipped with USPS delivery ... confirmation." Read more Show Less

Ships from: Holmdel, NJ

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
$5.00
Seller since 2014

Feedback rating:

(9)

Condition: Good
1997 Paperback Good Connecting readers with great books since 1972. Used books may not include companion materials, some shelf wear, may contain highlighting/notes, may not ... include cd-rom or access codes. Customer service is our top priority! Read more Show Less

Ships from: Dublin, CA

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Page 1 of 2
Showing 1 – 7 of 15 (2 pages)
Close
Sort by
Sending request ...

Overview

Behind the plain-looking facade of Oracle7 is a relational database engine far more powerful than anything else on the market. Now, discover the unbridled power of the Oracle7 program's unique database capabilities and turbocharge your Standard Query Language in no time with the latest version of this monster database program.

Whether you're working on a desktop PC, a Power Mac, a UNIX workstation, or an IBM mainframe, Oracle7 For Dummies can get you up to speed with practical advice, step-by-step guidance, and sample code for building and fine-tuning your Oracle databases. This book features tons of practical advice on designing efficient and effective relational databases, speeding up queries with keys and indexes, sharing and safeguarding your data, and getting a nuts-and-bolts view of Oracle's own SQL*Plus programming environment.

Oracle7 For Dummies also comes with a valuable bonus CD-ROM disc that includes a trial version of Personal Oracle7 for Windows 95, sample SQL scripts, and all the tables and sample data used in the book.

For anyone whose job requires working with a powerful database built on the Oracle database software, this . . . For Dummies guide will make getting around the application easier, because simple and easy-to-cover instructions cover all the primary features and commands.

Read More Show Less

Product Details

  • ISBN-13: 9780764500831
  • Publisher: Wiley, John & Sons, Incorporated
  • Publication date: 3/28/1997
  • Series: For Dummies Series
  • Edition description: BK&CD-ROM
  • Edition number: 1
  • Pages: 384
  • Product dimensions: 7.37 (w) x 9.18 (h) x 0.99 (d)

Table of Contents

Introduction

Why This Book Makes Sense
Some Basic Assumptions
Quickie Overview of This Book
Part I: Road Map
Part II: Getting Started
Part III: Putting Oracle7 to Work
Part IV: Tuning Up and Turbocharging
Part V: The Part of Tens
Appendix A: Resources Guide
Appendix B: About the CD
Glossary
Of Mice and Sven (Mouse Moves and Special Text)
Reading the Icons

Part I: Road Map

Chapter 1: A Quick Tour of Oracle7
Oracle7: The Program That Runs It All
Oracle7's core package
Personal Oracle7's extras
Starting Up Oracle7
Starting up on a PC or a Mac
The Object windows
The other Navigator elements
Starting up on a network or mainframe
SQL*Plus: The Messenger Priestess of Oracle7
Starting and stopping SQL*Plus on a PC or a Mac
Starting and stopping SQL*Plus on a mainframe or a network
Getting Help: Let Your Mouse Do the Walking
Shutting Down Oracle7
Shutting down Oracle7 on a PC or a Mac
Shutting down Oracle7 on a mainframe or a network
Chapter 2: Data Whaaaaat? A Database Primer
Dataspeak: Definitions for the Techno-Impaired
Databases
Users and roles
Tables
Columns and rows
Relationships
The Kinds of Database Things You Can Do with Oracle7
Keeping track of a fish bowl (the easy example)
Running a pet shop (the medium example)
Tracking endangered species globally (the hard example)
Chapter 3: SQL*Plus Nuts and Bolts
How SQL*Plus and SQL Fit Together
Fixing Mistakes, or Where's the Brake?!
How to use ROLLBACK
Commands that can't be undone, even with ROLLBACK
How to Ask a Question in SQL
The basic SQL query
Sampling some queries
Some tips to help you write good queries
Using an Editor While Running SQL*Plus
Pulling Data Out without Breaking Anything
Combining Tables -- The Meat and Potatoes of SQL*Plus
Basic join query structure
Examples of join queries
Chapter 4: Using the Personal Oracle7 Navigator
Starting PO7 Navigator
Viewing Your Tables
Updating Your Data the Mouse Way
Moving into Inserts
Deleting a Row

Part II: Getting Started

Chapter 5: The Relational Model and You
Redundant Relational Database Redundancy
Keys Rule
Types of keys
Importing foreign keys
Shuffling Columns: A Judgment Call
The Key, the Whole Key, and Nothing but the Key
One to Many: The Bread and Butter of Relational Databases
Chapter 6: Diagramming Your World
Getting Something Down on Paper
Tree Diagram: Easy Once You Know the Lingo
The table
The relationship
Arranging the tables and relationships
Sample Chart: Probably Invented by an Accountant
Special Effects to Make You Look Clever
Chapter 7: Getting Familiar with Oracle7 Structure
Data Dictionary Views You Can Really Use
Looking at Data Dictionary Views with SQL*Plus
List useful data dictionary views
List all Oracle users
Perusing Data Dictionary Views with PO7 Navigator
Adjust database connection properties
View data dictionary views
Chapter 8: Oracle's User
Role Playing
What kind of users are there?
What kind of user are you?
The SQL*Plus method
The PO7 Navigator method
Creating a New User from Scratch
The SQL*Plus method to create a new user
Using a wizard to help you conjure up a user
Changing Your Password
Changing your password in SQL*Plus
Changing your password in PO7 Navigator
Creating a Database Connection for Yourself
Part III: Putting Oracle7 to Work
Chapter 9: Defining Tables, Tablespaces, and Columns
Tablespace: The Final Frontier
A Word or Two about Columns
Defining columns in Oracle7
Nulls or not nulls
Creating and Dropping Your Own Table with SQL
PO7 Navigator's Wizard for Tables
Chapter 10: Security Options: Roles, Profiles, and Grants
Security Blanket Included
Roles Meet the Real World
Profiles: The Power Players
Dive In: Creating and Assigning Roles with SQL
Working with Roles in PO7 Navigator
Chapter 11: Views and Synonyms -- Do You See What I See?
A View Is Like a -- Table Almost
Views that Narrow
Pseudocolumns and the DUAL table
Views That Tie Everything Together
PO7 Navigator's Wizard Does Views
Synonyms: Nicknames for Tables and Views
Good uses for synonyms
Combining a grant and a synonym
Chapter 12: Reports: The Hidden Talent of SQL*Plus
Getting a Decent-Looking Report from SQL*Plus
Adjusting the size and heading for a column
Adding a title to your query
Sending SQL output to a file
Summarizing, Grouping, and Ordering in Queries
Sorting the results of your query
Summarizing instead of showing details in your query
Grouping rows and showing totals on break points
Dazzle Your Friends with These Commands
Defining a variable
Changing the page length and width
Making two queries appear on one page
Hiding the query
Chapter 13: Sharing and Getting Data Out of Oracle7
Breaking the Database Barrier
Techno-junkie alert: Here's the SQL*Plus code
Client/server technology with Oracle7
Pulling Data Out of Oracle7 with SQL*Plus
Pushing Data Into Oracle7 With SQL*Plus
Chapter 14: The Five Ws of Safeguarding Your Data
Why Back Up -- Onward and Upward!
What to Back Up
When to Back Up (Back Up Before You Go Off the Cliff)
How to Back up (Choosing Your Backup Weapon)
Backup Manager
Export utility
Building a project for export
Export the project
EXP and IMP commands
Exporting with the EXP command
Importing with the IMP command
Where to Hide Your Backup Tapes

Part IV: Tuning Up and Turbocharging

Chapter 15: What's Slowing Your Query Down?
Baby Steps versus Giant Leaps
Set SQL*Plus Timing On
Looking at the SELECT Clause -- Too Much or Too Little
Picking apart the FROM clause for little flaws
Missing table joins: The WHERE clause monster
Extra table joins: Overkill that slows you down
The ORDER BY clause reduces Oracle7 to a crawl
The danger of NOT
IN versus EXISTS: Important facts you should know
Chapter 16: Speeding Up Queries with Keys and Indexes
Why Create an Index?
Adding, Changing, and Removing a Primary Key
Adding, changing, and removing a primary key using PO7 Navigator
Adding, changing, and removing a primary key with SQL*Plus
Adding a primary key with SQL*Plus
Foreign Keys are Created and Indexed Separately
Adding, changing, and removing a foreign key with PO7 Navigator
Adding a foreign key with PO7 Navigator
Handling a foreign key with SQL*Plus
Creating Your Own Indexes
Creating an Index Using the Navigator Index Dialog Box
Adding an index with PO7 Navigator
Removing an index with PO7 Navigator
Changing an index with PO7 Navigator
Viewing an index with PO7 Navigator
Creating an Index Using SQL
Adding an index with SQL*Plus
Oracle's Brain on Index
Do you have an index?
Indexing and the infamous null value
Concatenated text-type columns
Wildcards can throw everything off
Mixing and matching different datatypes
The logically illogical order of data
Indexes Can Be Too Much of a Good Thing
Chapter 17: Correcting Flaws
Changing Columns in a Table
Changing columns using PO7 Navigator
Changing columns using SQL*Plus
Restrictions for Changing Column Attributes
Column must be null
Column must not have nulls
Re-creating a Table
PO7 Navigator steps for restructuring a table
Using SQL*Plus to restructure a table
Chapter 18: Utilizing Oracle7's Space-Saving Features
Oracle7 is a Blockhead, Charlie Brown
Overhead Storage
How Oracle7 Saves Space
Everything Was So Great -- Until . . .
The SQL*Plus checkup
The SQL*Plus cure
Preventive Maintenance for Healthy Tables

Part V: The Part Of Tens

Chapter 19: Ten Tips for Good Design
Naming Tables and Columns Creatively and Clearly
Look Before You Leap -- Designing Before Building
Go Ahead, Leap! -- Building a Prototype
Share -- Don't Re-invent the Wheel
Primary Keys Are Your Friends
Small keys take up less room
Non-intelligent keys are easy to maintain
Use Caution When Modifying Table Definitions
Handling Derived Data
Practical Ways to Approach Roles for Security
Low security
Medium security
High security
Be Smart about Test Data
Talk to Non-Techie-Type Humans
Chapter 20: Ten Cool SQL*Plus Tricks
Putting a Date into a Title
Formatting Dates
The WHENEVER Setting
The EMBEDDED Setting
The RECSEP and RECSEPCHAR Settings
The DECODE Function
The INSTR Function
The SUBSTR Function
The CONCATENATE Symbol
The NVL Function
Chapter 21: Ten Common Problems and How to Fix Them
What Do You Mean, My Table Doesn't Exist?!
Solution 1: That table does too exist!
Solution 2: Really! That table does too exist!
Solution 3: That $@!#!! table does too exist!
I Can't Log onto Oracle7
I Can Look at the Data, But I Can't Update the Table
This SQL Code Works in Oracle7 But Not in Access
How Can I Tell Blanks From Nulls?
I Forgot to Put In a Primary Key
I Want to Test a Query
I Want to Display the Date Without Changing My Query
Chapter 22: Ten Handy SQL Scripts
Finding the Duplicate Rows
Removing One of Those Duplicate Rows
Sniffing Out Broken Relationships
Mending Broken Relationships
Checking Your Access to Any Table
Listing All the Tables
Listing All the Columns
Listing All the Indexes
Generating a Spiffy Select Statement
Prying the SQL Statement Out of a View
Chapter 23: Ten Other Tools That Go With Oracle7
PowerObjects
PowerBrowser
WebServer
The 2000 Series Packages
Oracle Media Objects
NetDynamics
WebBase
ODBC Drivers
SQL*Net
PowerBuilder

Part VI: Appendixes

Appendix A: Resources Guide
Web Database Products
WebLogic
WebBase
NetDynamics
WebContact
WebSeQueL
Web Designer Aids
Carol McCullough's Web resource page
HTML language textbook
Web Sites That Use Databases On-Line
Shop Texas style
Job search
Fun with Access
Shopping doggie
Web-Related Products
Microsoft Server
AOL Web Page Maker
HTML editor
Web browser comparisons
Adobe Acrobat
Database Software and Aids
International Oracle Users Group ­ Americas
SQL language textbook
Oracle free trial software
Sybase
Oracle books
Database software by Anyware
DB2 database
Appendix B: About the CD
Glossary

Index

License Agreement and Installation Instructions

Reader Response Card

Read More Show Less

First Chapter

Chapter 15
What's Slowing Your Query Down?

In This Chapter

  • Tune-ups, timing, and performance
  • Little bites versus wolfing your data
  • The SELECT clause
  • The FROM clause
  • The WHERE clause
  • The ORDER BY clause
  • NOT in the WHERE clause
  • IN versus EXISTS in the WHERE clause



Perhaps you write SQL code in your spare time. If so, you actually have no spare time. I suggest you get a life. On the other hand, you may use some additional software programs that generate all your queries for you. Everything goes well until one day you create a query that runs so slowly you consider taking it out and shooting it. The database administrator (DBA) starts yelling in your face. You take three Excedrin and grab the manuals.

This chapter is all about performance tuning, or speeding up your query. I cover the bases with useful information for tuning up your SQL code. This chapter and the others in this section will have your code flying like never before.

Baby Steps versus Giant Leaps

A SQL query or command can run slowly for many reasons. To discover important performance clues, ask yourself a couple of questions: "When did the problem start?" and "What did I change just before I started having trouble?"

When you create a complex SQL statement, you may be wise to take small bites out of the whole goal and then test each one as you go along. These steps can help you develop good habits for creating tricky SQL statements:

  1. Build the statement one table at a time. Choose the main table and build the beginning statement using only the main table.
  2. Test your beginning statement and make corrections.
  3. Add another table, test the statement, and make corrections.
  4. Continue adding tables, testing, and correcting until the SQL statement is completed.

Often you can catch trouble spots in your code when you gradually build the code. Taking baby steps gives steady, forward progress. Giant steps, in which you create an entire SQL command at once, tend to fall short.

SQL*Plus has a command to help you figure out whether you've improved the response time for your query. The next section discusses this command, called timing.

Set SQL*Plus Timing On

The timing command in SQL*Plus gives you great feedback on the performance speed of your SQL command. To turn the timing on, simply type:

set timing on

This is an environment command in SQL*Plus, meaning that it changes how SQL*Plus displays the results of your queries and other commands. An environment command stays on until you reset it or leave SQL*Plus. There are many environment commands. Some are described in this chapter, and others are described in Chapter 12.

To turn the timing setting on in SQL*Plus using Personal Oracle7 Navigator, start up SQL*Plus (refer to Chapter 4) and follow these steps:

  1. Select the Options menu at the top of the window.
  2. Select Set Options (Environment in Oracle 7.3).
  3. This opens a window for setting all your environment settings, as shown in Figure 15-1.

  4. Scroll to and select the timing setting and then click the On button. (In Oracle 7.3, first click the Current button, then click the On button.)
  5. Click OK.

After you do that, every SQL command you execute is followed by statistics. If you're running Oracle7 on a PC, you see something like this:

real: 550

This is the real time in thousandths of a second. Other operating systems show different statistics, such as the CPU time or buffers used.

The first time you run a SQL command, Oracle7 reads new data into its buffers. The data stays in the buffers until the end of the session or until Oracle7 runs out of buffers and reuses the one with this data. Consequently, when you use the timing statistics to compare variations on similar queries, make sure that you run each of them a second time. By doing this, you get timing statistics that do not include the initial loading of the buffers.

Now you have a method for testing out the changes you make to your query in an attempt to speed it up. You can look at the slow query's timing statistics, make changes to the query, and compare this changed query's timing statistics. As a starting point, the next section dives into parts of the query you can explore to determine what is slowing your query down.

Looking at the SELECT Clause -- Too Much or Too Little

When your query does not work efficiently, you can take specific steps to inspect and adjust it to work efficiently. The sections here go through your query piece by piece. Each section points out the most common causes of inefficient SQL code and shows how to correct the situation.

Picking apart the FROM clause for little flaws

The FROM clause contains a list of all the tables from which you retrieve data. I think FROM clause is a perfect name for this clause, don't you? Oracle7 goes out of its way to say in its documentation that the order in which you choose to name your tables in the FROM clause is of no significance. Then, in very fine print, buried somewhere in the manuals, Oracle7 comes clean and explains a little-known quirk of the Oracle7 engine that helps you create better, faster SQL.

In most cases, Oracle7 ignores the order of the tables in the FROM clause. However, when Oracle7 needs a tiebreaker, it looks at the FROM clause. It chooses to use the index on the last table in the FROM clause and to ignore the index on the other table. If you list the larger table first, performance suffers, because Oracle7 does not use an index on this large table. That means it will scan the entire contents of the table. So you may want to rearrange the order of the tables in your query, placing large tables at the end.

When listing tables in the FROM clause, list them in ascending order according to the number of rows to be retrieved from each table when the query runs.

You are an alien visiting Earth on spec for your home company. You set up database tables for 100,000 new clients (CUSTOMER_ACCOUNT) who have shown interest in your unusual product and for the 50 countries (COUNTRY) that these clients live in. COUNTRY_ID links the two tables together. A query listing the customers and countries could take two forms.

The wrong way to query would be something like this:

select C.COUNTRY, A.NAME
from CUSTOMER_ACCOUNT A, COUNTRY C
where A.COUNTRY_ID = C.COUNTRY_ID
order by COUNTRY, NAME;

The right way to query would look like this:

select C.COUNTRY, A.NAME
from COUNTRY C, CUSTOMER_ACCOUNT A
where A.COUNTRY_ID = C.COUNTRY_ID
order by COUNTRY, NAME;

Why is the latter approach better? Because the FROM clause in the second query places the larger table (the CUSTOMER_ACCOUNT table) last and the smaller table first.

Missing table joins: The WHERE clause monster

When you write a query, the WHERE clause has two main functions:

  • Listing criteria that narrow down the rows chosen (such as a date range)
  • Specifying connections between two tables (matching foreign and primary keys)

If your query contains more than one table and the second function (specifying connections) is missing or incorrect, SQL*Plus does not issue an error message. Instead, SQL*Plus figures you know what you're doing and processes your query as if you wanted to match every column in the first table with every table in the second. If one table has ten rows and the other has 20, your end result has 200 rows. That's probably not what you intended! This method of matching all rows with each other is called a Cartesian join.

A Cartesian join is a set of all the possible combinations of every row in two or more tables. Its namesake is the French philosopher and mathematician René Descartes, who founded much of modern geometry. As you might imagine, a Cartesian join is usually the result of an error in your query and causes your query to return many more rows than intended, and as a result slows down your response time.

A Cartesian join occurs in these two cases:

  • Two tables are named in the FROM clause and are not related to each other in the WHERE clause.
  • The relationship that is defined is incorrectly formed.

Figure 15-2 shows how a Cartesian join looks with two small tables.

As the amount of data increases, the size of the Cartesian join monster grows exponentially. The number of rows produced by a Cartesian join is equal to the number of rows in each table multiplied by the number of rows in the other table.

If the query results seem to show up as repeated rows, you very likely have a Cartesian join. Review the WHERE clause for relationships between tables.

You hastily prepare a query of all your customers whose names begin with A, and their countries, but you forget to place the relationship portion of the WHERE clause into the query.

The right way to handle table connections in your WHERE clause in this example is:

select C.COUNTRY, A.NAME
from CUSTOMER_ACCOUNT A, COUNTRY C
where A.NAME like 'A%'
and A.COUNTRY_ID = C.COUNTRY_ID
order by COUNTRY, NAME;

The wrong way to connect tables might look like this:

select C.COUNTRY, A.NAME
from COUNTRY C, CUSTOMER_ACCOUNT A
where A.NAME like 'A%'
order by COUNTRY, NAME;

The first query works because you have included a proper phrase in the WHERE clause to tell SQL*Plus how to connect the two tables.

Extra table joins: Overkill that slows you down

Be careful when you create queries that join more than two tables. A common mistake is to include a condition in the WHERE clause that is unneeded and repetitive, which can happen when a hierarchy of tables exists.

Avoid extraneous joins in hierarchical table relationships. Follow relationship lines in diagrams as an aid.

Dissecting a WHERE clause

The WHERE clause can have many parts. Oracle7 looks at the WHERE clause as a list of conditions strung together like train cars. The connector between each train car is the word AND or the word OR. Parentheses also help Oracle7 determine how to connect the conditions. In a long query with many tables, the WHERE clause gets long and seems complex. Just remember that it always breaks down into a list of conditions. Each condition stands on its own and contains either a relationship that defines how two tables are joined or a data test that must be satisfied. Each line of the following code is an example of a condition in a WHERE clause.

BIRTHDATE < TO_DATE('01-JAN-75')

A.CUST_ID = B.CUST_ID

STATE_CODE IN ('HI','FL','TN')

You have three tables: CONTINENT, COUNTRY, and CITY. Each table's primary key builds on the previous one. Figure 15-3 shows the relationships among the tables. The line between the CONTINENT table and the CITY table is unneeded and should be omitted. However, this line shows how easily you can make an error when working with several related tables. The CONTINENT column is a foreign key column in the CITY table. You can interpret it as a foreign key to either the COUNTRY table or the CONTINENT table. Refer to Chapter 5 for a thorough explanation of primary keys and foreign keys.

As you write a query, you add a condition that relates the CITY table with the CONTINENT table. This is an unneeded condition that makes Oracle7 do more work when it runs the query -- and slows performance.

Here's your example with the proper joins in the WHERE clause:

select CITY.CONTINENT, CTRY.COUNTRY_NAME, CITY.CITY
from CONTINENT CON, COUNTRY CTRY, CITY
where CITY.COUNTRY_ID = CTRY.COUNTRY_ID
and CITY.CONTINENT = CTRY.CONTINENT
and CTRY.CONTINENT = CON.CONTINENT
order by 1,2,3

This incorrect one contains extraneous joins in the WHERE clause:

select CITY.CONTINENT, CTRY.COUNTRY_NAME, CITY.CITY
from CONTINENT CON, COUNTRY CTRY, CITY
where CITY.COUNTRY_ID = CTRY.COUNTRY_ID
and CITY.CONTINENT = CTRY.CONTINENT
and CITY.CONTINENT = CON.CONTINENT
and CTRY.CONTINENT = CON.CONTINENT
order by 1,2,3

The first query works better because it requires SQL*Plus to do less work in connecting the three tables together and still gets the right results.

The ORDER BY clause reduces Oracle7 to a crawl

The ORDER BY clause determines how Oracle7 sorts the rows it returns to you as the result of your query. The ORDER BY clause does not limit you to any particular column. In fact, you can include columns that have functions, such as concatenation or addition, on them as well. Be aware that anything in the ORDER BY clause that is not indexed slows performance.

Review your ORDER BY clause for columns or expressions that are not indexes, because these slow performance. Resolve the performance problem by rewriting the ORDER BY clause to use indexes or by creating another index on the column or columns that you use. See Chapter 17 for an in-depth discussion of indexing.

Your sales staff just notified you that sales have decreased. You want to see the trend over the last 30 days, so you sort your query by descending date. The date you want in your report is converted to YY/MM/DD format, which is your preference.

The right way allows SQL*Plus to use the index on SALES_DATE:

select to_char(SALES_DATE,'YY/MM/DD'), SALES_AMOUNT
from DAILY_SALES
WHERE SALES_DATE BETWEEN
TO_DATE('01-JAN-96') and TO_DATE('31-JAN-96')
ORDER BY SALES_DATE

The wrong way has an ORDER BY clause that uses no index:

select to_char(SALES_DATE,'YY/MM/DD'), SALES_AMOUNT
from DAILY_SALES
WHERE SALES_DATE BETWEEN
TO_DATE('01-JAN-96') and TO_DATE('31-JAN-96')
ORDER BY to_char(SALES_DATE,'YY/MM/DD')

The first query works much faster because an index is used to retrieve data. Without an index, the data is retrieved by reading through every row in the table.

The danger of NOT

All the phrases or parts of your WHERE clause contain some kind of logical operator such as = (equal) or < (less than). NOT is tacked on to reverse the logic of any of these operators. NOT is used explicitly by adding parentheses around a phrase and placing NOT just in front of it, like this:

... where not (STYLE = '4-door')

NOT is contained implicitly in one logical operator -- the NOT EQUAL operator. In other words, NOT is there even though you don't type the word into your query. Here are the three forms that you can use to write NOT EQUAL:

<>
This is the preferred method, because it is accepted by all the variations of Oracle7 across all hardware platforms.
!=
This form of NOT EQUAL does not translate between all platforms! Use the first form of NOT EQUAL (<>) instead.
^=
Again, this form of NOT EQUAL does not always translate between platforms. Use the first form of NOT EQUAL (<>).

You have great flexibility when using the NOT logical term in your query's WHERE clause, but you pay a price for using NOT -- Oracle7 cannot use an index on any phrase that contains NOT. Without an index to use, a query slows down. So don't use the NOT logical term in the WHERE clause. Just imagine if your doctor said, "You only have three days to live -- NOT!"

Here's an example of a query that works without NOT:

select * from COUNTRY
where COUNTRY_ID <= 100

Here's the same query using NOT:

select * from COUNTRY
where NOT (COUNTRY_ID > 100)

The first example runs faster than the second even though both get the same results. The first one allows Oracle7 to use an index that exists on the COUNTRY_ID column, while the second one prohibits use of the index.

IN versus EXISTS: Important facts you should know

Sometimes you compare a column to a list of values. A common way to create the list is to create a sub-query inside your WHERE clause. The basic method involves figuring out a sub-query that creates a list and then including that in the WHERE clause as a sub-query.

There are two general formats for a sub-query in a WHERE clause. The first one uses the IN logical operator and looks like this:

... where column in (select ... from ... where ... )

The second format uses the EXISTS logical operator and looks like this:

... where exists (select 'X' from ... where ...)

Most people use the first format because it is a lot easier to figure out. The second format actually gives you much better performance, if you take the time to learn how to create it. You will find that nearly every sub-query you create using the IN logical operator can be converted to use the EXISTS logical operator.

In the second format, the sub-query starts with select 'X'. This looks strange, but it is correct. EXISTS looks directly at the WHERE clause and does not even care what data you pull from the table you have in the sub-query. EXISTS uses a correlated sub-query, which is why it is more difficult to construct.

Chapter 12 discusses the correlated sub-query. Refer to that chapter for a good explanation of how to construct a correlated sub-query.

When you replace IN with EXISTS, you gain performance speed. Oracle7 saves time when it uses EXISTS because it evaluates the main query first and runs the sub-query only until it finds a match. With IN, Oracle7 suspends processing of the main query while executing the sub-query to make a list. Oracle7 stores that list in an indexed temporary table and then resumes processing of the main query. While there are exceptions, Oracle7 usually performs queries that use EXISTS faster than those that use IN. Therefore, examine your WHERE clause for the IN condition. In most cases, you can replace the IN condition with EXISTS.

Here's an example of a sub-query using EXISTS:

select *
from COUNTRY C
where exists (select 'X' from CITY where CITY.COUNTRY_ID = C.COUNTRY_ID)

Here's the same query using IN for the sub-query:

select * from country C
where C.COUNTRY_ID in (select CITY.COUNTRY_ID from CITY)

The first one gets the same results (the same rows retrieved) as the second one but runs faster because it uses EXISTS rather than IN.

Whenever possible, also replace NOT IN with NOT EXISTS. Even though both have NOT in them (which can slow performance down because it prevents the use of indexes), NOT EXISTS performs faster than NOT IN.

SQL gets fancy

This chapter really just scratches the surface of all the subtleties of SQL*Plus and the SQL language. If you have a taste for more, perhaps you'd be interested in another book, SQL For Dummies®, by Allen G. Taylor, published by your favorite and mine: IDG Books Worldwide, Inc.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)