Java Reference
In-Depth Information
This query yields a result set that looks like this:
DESCRIPTION
STATE
COUNT
TOTAL
Cereal
NJ
2
14.1
Cereal
NY
2
4.88
Cookies
NJ
2
5.74
Cookies
NY
2
11.32
Soda
NY
2
5.4
You can also apply a HAVING clause to the entire result set by omitting the GROUP
BY clause. In this case, the DBMS treats the entire table as one group, so there is at
most one result row. If the HAVING condition is not true for the table as a whole, no
rows will be returned.
HAVING enables you to use aggregate functions in a comparison statement,
providing for aggregate functions what WHERE provides for individual rows.
Using Indexes to Improve the Efficiency of SQL Queries
You can improve database performance significantly by using
indexes
. An index is a
structure that provides a quick way to look up specific items in a table or view. In
effect, an index is an ordered array of pointers to the rows in a table or view.
When you assign a unique id to each row as a key, you are predefining an index for
that table. This makes it much faster for the DBMS to look up items by id, which is
commonly required when you are doing joins on the id column.
SQL's CREATE INDEX statement allows you to add an index for any desired column
or group of columns. If you need to do a search by customer name, for example, the
fact that the table has a built-in index on the primary key doesn't help, so the DBMS
has to do a brute force search of the entire table to find all customer names matching
your query. If you plan on doing a lot of queries by customer name, it obviously
makes sense to add an index to the customer name column or columns. Otherwise,
the task is like looking up names in a phone list that hasn't been alphabetized.
The SQL command to add an index uses the CREATE INDEX key word, specifying a
name for the index and defining the table name and the column list to index. Here's
an example:
CREATE INDEX STATE_INDEX ON MEMBER_PROFILES(STATE);
To remove the index, use the DROP INDEX command as follows:
