GeoMedia GIS Blog

"A picture is only worth a thousand words. A map may be worth a thousand numbers. But a GIS is worth a thousand tables."

General Update – SQL Tuning

Posted by jeffhobbs on November 5, 2007

Welcome back! Sorry it’s been so long since I updated the blog. Two weeks ago I attended an Oracle SQL Tuning class at the Oracle Training Center in Belmont, CA. I had taken a number of Oracle 8i classes four or five years ago. However I never took the SQL Tuning class. Over the past couple of years I’ve found myself having to do more and more at the database level as opposed to the application level (like GeoMedia), so I thought it was about time to take the class and try and get my queries faster.

For those that aren’t aware, SQL tuning is basically the idea of writing your SQL queries in ways that will run most efficienty in the database. So in other words, look at the following two SQL queries – both will return the same results but are written in different ways:

SELECT cust_city, avg(cust_credit_limit)
FROM customers
WHERE cust_city = ‘Paris’
GROUP BY cust_city


SELECT cust_city, avg(cust_credit_limit)
FROM customers
GROUP BY cust_city
having cust_city = ‘Paris’

In the two queries the WHERE clause will be evaluated before the HAVING clause. Therefore, by using WHERE as opposed to HAVING, you’re able to reduce the number of records returned faster in Query A as opposed to Query B. As a whole, this can be VERY important – especially when dealing with hundreds of thousands of records.

Anyhow, as a whole, the class covered A LOT of information and really went back into the core of Oracle and how it operates for many things. Very interesting (I think)…but also very detailed. However, if I had to outline what I learned in a single thought, it would be the following:

"Oracle 10g wants to use the cost-based optimizer. So, it’s going to do it; and most often the cost-based optimizer is correct. So, the idea of including 8i and 9i SQL hints for the most part has been elimianted. In fact, unless the complete query is written using hints, Oracle will simply IGNORE the hints you put into an SQL query."

This was very good for me to understand as I was constantly playing with SQL hints trying (for the most part unsuccessfuly) to get my SQL queries to run faster.

All in all, I did find the class to be very useful and really helped me answer a number of questions I’ve had over the years. I’m looking to test some of the Oracle SQL tuning tools against a spatial query in the near future…. Should be interesting to see how much faster I can make it.

Sorry, the comment form is closed at this time.

%d bloggers like this: