 |
|
Web application programming is gobs of fun, especially compared to the olden days of edit/compile/debug/repeat in C, C++, Pascal, or (God forbid) FORTRAN. The ready availability of cheap server hardware, "free" web server software (in the case of Microsoft IIS that word is used in the ironic sense), and powerful relational database engines on Windows NT, Macintosh OS X, or LINUX make it conceivable for even the lone developer to write rich, platform-independent, data-driven, adaptable, configurable, responsive, and attractive applications that most of us could only dream about as recently as 1995. For a programmer, it's truly a great time to be alive.
But behold. Optimizing the SQL that is embedded in your web application is the key to performance. If accesses to the database are not efficient, the effect is magnified throughout the application and out the wire to the hapless user. Although any tyro can write a SELECT statement and join some tables to yank out some data, fully understanding a database physical model, writing the appropriate SQL, and taking full advantage of the bewildering array of functions and features in a modern RDBMS such as Oracle 9i is an entirely different kettle of fish. The fortunate web programmer will collaborate with an experienced database administrator (DBA) that can analyze the SQL and make recommendations for improvements, or tweak the physical database by adding indexes or partitioning data to support the web programmer's needs.
Said to say, highly skilled and dedicated DBAs are tougher to recruit than neurosurgeons. And their time is precious -- the three DBAs where I work support more than 50 production databases on a 7X24 basis, of which many are patient-care systems where a target of zero downtime is taken for granted -- so it's not prudent or cost-effective to bother our DBAs with obvious or bone-headed SQL questions. Most of these databases run on Oracle and UNIX or LINUX for scaleability and reliability. When I send the DBAs some SQL to analyze and tune, I want to feel that I've already done everything I can within my limited abilities to spiff up that SQL myself. That's where a book like Mastering Oracle SQL comes into the picture.
Do you know the fine points of inner joins, outer joins, equi-joins, and how a NULL can defeat a perfectly good-looking WHERE clause? How about self-joins, self-non-equi-joins, in-line views, hierarchical queries, compound queries, and correlated sub-queries? Can you manipulate, add, subtract, compare, and format dates and times with aplomb? Can you take advantage of GROUP BY and HAVING to get the database to do the hard work of aggregation for you and send you back the golden totals? Have you ever even heard of the features CUBE, ROLLUP, and GROUPING SETS? Mastering Oracle SQL has the answers for these questions and more.
There's still no substitute for getting the database structure right in the first place, for investing the time to understand the structure, for organizing the application properly, for using the SQL keywords and functions correctly, and of course for the hard lessons learned from practical experience in the front lines. And you always need a full set of core reference books like David Kreine's Oracle SQL, Jonathan Gennick's Oracle SQL*Plus, and Steven Feuerstein's Oracle PL/SQL Programming at your fingertips. But make a space on your bookshelf for Mastering Oracle SQL as well, and reach for it when you get that subconscious twinge that says "Hey, there's got to be a better way to do what I'm trying to do here."
-- Ray Duncan (ray.duncan@cshs.org)
|
Explanation of ERCB rating scale:
|
