Just had a fantastic success improving the performance of an update statement running on a table in an Oracle 10g database.
I’d originally written this update as three seperate SQL statments to be run sequentially.
Script 1 created a table containing the records to be considered for update.
Script 2 created a view that ranks a set of entity relationships and produces one row per row in the table created by script 1 ready for the update
Script 3 joins the view from script 2 and the base table to run the update.
There were only 150,000 records to update, out of a table containing many millions of records.
The explain plan for the update in script 3 was horrendously long with all manner of hash and merge joins, sorts etc. The total optimiser cost of the original query was 66,406 and it had been running for 19 hours without completing.
There were no indexes on the table created in Script 1. I created a non-unique index on the two columns used in the joins within the view created in Script 2 and then ran an explain plan on Script 3 again. The total optimiser cost came down to 40,221.
I restarted the update but wasn’t 100% convinced that I’d brought the run time down enough to get my job completed on time.
As I sat and looked at my update statement I realised I only required 2 columns in my update statement – the target table’s primary key and the value to be updated.
With such a narrow table my first thought was to create an index on both columns so that the update could run entirely from the index without having to force Oracle to read any blocks from the underlying table.
Then I remembered a colleague talking about Index Organized Tables (IOTs). These are eseentially B-Tree indexes where all the table data is stored within the index. A quick read of the Oracle Data Sheet on IOTs explained that they are most useful in improving query performance when accessed directly by their primary key.
So I created a new IOT, then inserted into by selecting the two required columns from my view in Script 2 above. I then modified my update statement to run from the new IOT. When I ran an explain plan on my latest version of the update statement I couldn’t believe my eyes…the cost was 3!!!
As a natural pessimist I thought “Yeah right, the cost might be 3, but I bet the run time is still hours.”
However I barely had chance to view the update through TOAD’s session browser as it completed within 1 minute! 🙂
Mike's Tech Blog 