Skip to content

Author Archives Jonathan Lewis

Partition Stats

It’s possible to spend ages talking about the best ways of collecting, or creating, statistics on partitioned tables.
The possible strategies for maintaining partitioned tables, (exchange partition, split partition, drop partition etc.) the types of partitioning available, and the way that the optimizer plays with the stats as you do so, have kept changing over the [...]

Posted 17 March 2010 § Uncategorized ‡ Comments Off °

Advert – MOW

I’ve just noticed that Miracle Open World is less than a month away – so I just had to bounce this advert to the top of the blog to remind people about this event, which probably has the highest concentration of international speaker per square metre of any Oracle conference in the world. But you [...]

Posted 15 March 2010 § Uncategorized ‡ Comments Off °

Sample Clause

In some of my previous posts (particularly the ones about analysing the data by running aggregate queries) I’ve mentioned the “sample” clause from time to time, so I thought I’d better write a short note about it for those not familiar with it.
To demonstrate the feature, my first step was to run a test script [...]

Posted 12 March 2010 § Uncategorized ‡ Comments Off °

Dropping OUTLN

I had a very pleasant day yesterday at a SIG meeting of the UK Oracle User Group where I did the presentation about “drawing your SQL” that is the basis of the article I wrote for Simple Talk a little while ago.
One of the other sessions had the entertaining title: “What happens if you drop [...]

Posted 11 March 2010 § Uncategorized ‡ Comments Off °

CBO arithmetic

Anyone who’s keen to keep track of how the cost based optimizer does its arithmetic, and how that arithmetic can change with version, may want to keep an eye on this blog.

Posted 07 March 2010 § Uncategorized ‡ Comments Off °

Treedump – 2

In an earlier article about investigating the state of an index in detail I supplied a piece of SQL that would analyse an index (no, not using the Analyze command) and summarise the number of entries in each leaf block that currently held any entries at all. Here’s a sample of the type of output [...]

Posted 07 March 2010 § Uncategorized ‡ Comments Off °

French Translations

I’ve just had another email about translating some of my postings, in this case into French. Franck Pachot has asked if he can post translations on his blog, and the answer is yes.
His intention is to select the postings which are more about how the database engine works, rather than examples of specific solutions, and [...]

Posted 05 March 2010 § Uncategorized ‡ Comments Off °

SQL Server 2

Following on from my posting about the presentation (about Enterprise databases) that I did at a Microsoft event on SQL Server 2008, I’ve just had an article on how to design efficient SQL published on a website that’s strongly biased towards SQL Server developers – using an example of SQL written in the SQL Server dialect.
It’s [...]

Posted 04 March 2010 § Uncategorized ‡ Comments Off °

Index Efficiency 3

My last posting about indexes was an introduction to a pl/sql script that estimated the sizes that your indexes would be if you rebuilt them at a given percentage of utilisation for leaf blocks. By comparing these estimates with the actual size of the indexes you can get a quick report of indexes that are probably significantly [...]

Posted 03 March 2010 § Uncategorized ‡ Comments Off °

Temporary Note

… to let peopl know that there’s a new item on the “Chinese Translation” catalogue.

Posted 02 March 2010 § Uncategorized ‡ Comments Off °