Java Reference
In-Depth Information
for(int i=1;i<=nColumns;i++){
System.out.print(rs.getString(i)+((i==nColumns)?"\n":"\t"));
}
}
java.util.Date endTime = new java.util.Date();
long elapsedTime = endTime.getTime() - startTime.getTime();
System.out.println("Elapsed time: "+elapsedTime);
stmt.executeUpdate("DROP INDEX MEMBER_PROFILES.STATE_INDEX");
}
catch(ClassNotFoundException e){
e.printStackTrace();
}
catch(SQLException e){
e.printStackTrace();
}
}
}
The example in
Listing 8-1
is run against a membership database containing approximately 150,000
members and shows an improvement of 2:1 in elapsed time for the query shown.
Once you have executed a SQL query and obtained a sorted, grouped set of data from the database, it
is frequently very useful to be able to save the query for reuse. One of the ways SQL lets you do this is
by using Views.
Views
A
view
is similar to a table, but rather than being created as a fundamental part of the underlying
database, it is created from the results of a query. In fact, you can think of a view as a temporary table.
Like a table, a view has a name that can be used to access it in other queries. Because views work like
tables, they can be a very useful tool in simplifying SQL queries. For example, you could create a view
based on a complex
JOIN
, and then work with that view as a temporary table rather than embedding
the
JOIN
as a subquery and working with the underlying tables.
The basic syntax used to create a view is as follows:
CREATE VIEW Orders_by_Name AS SELECT ...
The
SELECT
statement in the code is the
SELECT
you use in the query you want to save as a view, as
shown here: