Jonathan Lewis

Author

Hacking Indexes

Jonathan Lewis on 03 October 2016 with 2 comments

Indexes are expensive to maintain so we want to get the greatest benefit we can from them and make sure that Oracle uses them whenever it’s appropriate. Sometimes, though, the optimizer seems to ignore indexes that we think would be very effective and when this happens it’s often because the statistics that Oracle has gathered do not reflect the quality… Continue Reading →

All Things Oracle Full Articles, Performance Tuning, Troubleshooting

Rebuilding indexes is an activity that you shouldn’t need to do often. There are always a few special cases – like when you’ve moved or recreated a table or if you’ve done a massive delete on a table – when it’s probably a reasonable idea but, in general, there are very few cases where there’s any great benefit to be… Continue Reading →

All Things Oracle Full Articles, Oracle Database, Troubleshooting

Text Indexes

Jonathan Lewis on 05 September 2016 with 4 comments

The Oracle database has many features that are not spoken of frequently and a recent note by Connor McDonald reminded me that there are a couple of notes about optimising Text indexes that have been on my “must write” list for several years. If you’re not familiar with what text indexes can do for you, here’s a thumbnail sketch: create… Continue Reading →

All Things Oracle Full Articles

In the previous installment of this series I produced some figures highlighting the main differences between doing a large delete by tablescan and doing a large delete by index range scan. Depending on the data patterns involved the correct choice of strategy could make a significant difference to the number of random I/Os, the volume of redo generated, and the… Continue Reading →

12c, All Things Oracle Full Articles

In the previous installment of this series we looked at methods for seeing the pattern of the changes that would appear in a table and its indexes after a large delete. In this installment we move on to the workload implied by different patterns, then think about strategies or, indeed, the need for strategies for reducing that workload at the… Continue Reading →

All Things Oracle Full Articles, Performance Tuning

In Part 1 of this short series I provided an informal description of a couple of scenarios where we might do a large-scale delete from a table. Without a concrete example though, it can be hard to imagine how the nature of the data deleted and the access paths available could affect the impact a large delete operation could have… Continue Reading →

12c, All Things Oracle Full Articles

In a recent article about dropping columns from a table I left hanging the question of what you might do after you have dropped the column, because dropping a column is really nothing more than a special case of the “massive delete”. In this article, then, I’m going to lay the groundwork for how we ought to think around the… Continue Reading →

12c, All Things Oracle Full Articles

I have my Oracle Support account configured to send me a daily email of “hot topics”, and I’ve set it up so that the email lists the 100 bugs that have been updated most recently by Oracle support. One of the bugs that came up on 9th Jan was described as: “Bug 18700681 : DROP COLUMNS USING ALTER TABLE VERY… Continue Reading →

12c

In part 5 of this series I started working through a list of queries designed to demonstrate ways in which the optimizer can produce bad cardinality estimates. In this installment I’m going work through the remainder of the list. The outstanding queries are as follows: select count(*) from t1 where trunc_5 > ( select max(trunc_5) from t1 where mod_200 =… Continue Reading →

12c, All Things Oracle Full Articles, Performance Tuning

It’s been three months since I last published anything in this series on the Cost Based Optimizer, so a thumbnail sketch of the previous installments seems to be in order: In part 1 I gave an informal overview of how the optimizer would “think” about a simple query. In part 2 I created a data set, and ran a few… Continue Reading →

12c, All Things Oracle Full Articles, Performance Tuning