I Didn’t Know That 4 – What is Wrong with this Quote?

12 12 2010

December 12, 2010

(Back to the Previous Post in the Series) (Forward to the Next Post in the Series)

In the interest of interesting quotes, a quote that I found in the “Oracle Tuning the Definitive Reference Second Edition” book on page 987:

Row ordering matters!
In some systems where a table is always accessed by the same key sequence, re-ordering the table into the same order as the queries can dramatically reduce I/O and improve SQL performance.

When reorganizing tables to improve SQL performance, keep this in-mind:

  • Only tables that experience multi-block reads (full-table scans) may see an appreciable SQL performance benefit.
  • Some shops will use sorted hash cluster tables to maintain row sequence order (in the same order as the most common indexed retrieval), and you can reorganize a table with an ‘order by’ clause to make the rows in the same sequence as the index.

But it’s not just tables that require periodic maintenance, it’s also indexes.”

What, if anything, is wrong with the above quote? 

—————–

While my review of the book only provides an in-depth technical review of the first 200 pages of the book, this blog article series will dig into some of the pages that were not specifically included in the review.

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.

Other pages found during a Google search of the phrase:

  • remote-dba.net/t_oracle_net_reorganize_tables.htm
  • dba-oracle.com/art_disk_io.htm
  • dba-oracle.com/t_table_row_resequencing.htm
  • dba-oracle.com/t_create_table_select_ctas.htm

Actions

Information

4 responses

12 12 2010
Martin Berger

Some points this time:
yes, row ordering matters. (I totally agree here)
* It would be nice to mention IOTs at the first paragraph, as IOTs are ordered per se and do not need re-ordering.
* Also queries which create sequential reads on tables can benefit in particular ordering, as the clustering factor of one index can be quite better and therefore the index – table roundtrip can use buffer cache quite better if clustering factor is low, not to mention the optimizer calculations. Even read-ahead algorithm of storage systems will benefit from ordered IO (even sequential reads). But that’s probably out of scope here.
I hope no one forces me to prove the 2nd statement as I currently does not have access to a sandbox-system with full SAN support. (any sponsor around?)

12 12 2010
Charles Hooper

I would argue that SQL statements that correctly require tables to be accessed by a full table scan (as suggested by the book’s first bullet point) will benefit very little from the DBA constantly re-ordering the rows in the table blocks to match the order of one of the table’s indexes. I would also argue that Martin’s second point (and most likely the first point too) are much more valid points than those made by the book.

What about the book’s suggestion to use a sorted hash cluster? I found this helpful article that describes sorted hash clusters (it is a three part article series):
http://jonathanlewis.wordpress.com/2008/07/13/sorted-hash-clusters/

I wonder if the book’s suggestion to periodically re-order the table rows in the blocks isn’t a bit of a trap for a DBA who wishes to be proactive?

12 12 2010
Radoslav Golian

“In some systems where a table is always accessed by the same key sequence, re-ordering the table into the same order as the queries can dramatically reduce I/O and improve SQL performance.”
OK, let’s assume key sequence = some ordered set of values {v1, v2, .., vn}, so we access the table by v1, then by v2, .. and by nv at the end.
When is a table accessed by a key (= *ONE* key), it usually doesn’t matter what the sequence is..
What matters is the data associated by the key.. Is it a primary key? Then no ordering is needed, I think.. Access by primary key = unique index scan.
A range scan by a primary key is not very usual..

“re-ordering the table into the same order as the queries”
What does this exactly mean? How can I order a table by set of queries? It does not make sense for me..

“When reorganizing tables to improve SQL performance, keep this in-mind:
Only tables that experience multi-block reads (full-table scans) may see an appreciable SQL performance benefit.”
That’s absolutely not true.. 2 examples (I believe there are much more of them)
Index range scans can benefit from table reorganization – you can improve index clustering factor for one index, but be aware! by improving clustering factor for one index you can make a clustering factor worse for another index (if the sets of indexed columns are not correlated for those indexes), because you can order data only by one set of columns.
Be reorganizing tables you can enable new access paths, for example hash clusters, which could be faster then for example previous index scan..
For example:
t(id, a, b, ..)
select * from t where a = :X; — we have index on t(a), let’s assume it has a very poor clustering factor (a same values of a are spread across many table block ), so we have to access many table blocks by single block read.
now we reorganize table T-> we make a hash cluster from it – clustered by a
select * from t where a = :X — now the same values of a are physically together in the table – they are in few table blocks that contain the same value of a.. So we need to read only few table blocks. Do not implement (sorted) hash clusters heedlessly (they can slower your DML, for example), always do a research, test your approach and RTFM..

“Some shops will use sorted hash cluster tables to maintain row sequence order (in the same order as the most common indexed retrieval), and you can reorganize a table with an ‘order by’ clause to make the rows in the same sequence as the index.”
This is very unclear and vague..
1. sorted hash cluster (hashed by x, sorted by y) is good for these king of queries:
select ..
from …
where x = :X_val,
order by y
So we access the cluster by hash values for x and we order result set by y.. This kind of query will be very fast, we need only retrieve (probably) few blocks with value :X_val (data are hashed by x, so same values of x are physically together) and data are in those blocks sorted by y so we don’t need to do a sort.
2. be aware of consequences (not truncate table, slower DML, …), read docs.

“But it’s not just tables that require periodic maintenance, it’s also indexes.”
generally not true.. you should examine very very carefully which table/index to reorganize (because you don’t want to do useless work, or do you?), and it’s very feasible, you don’t need to reorganize/maintain most of your tables/indexes..

12 12 2010
Radoslav Golian

“When reorganizing tables to improve SQL performance, keep this in-mind:
Only tables that experience multi-block reads (full-table scans) may see an appreciable SQL performance benefit.”
Queries that use FTS might benefit from reorganization, for example after massive deletes.. So HWM is high and there are only few data in the table..
Try:
1. create table t with 1 000 000 rows
2. delete from t
3. select count(*) from t; — will take some time
4. truncate t <- reset HWM
5. select count(*) from t — fast

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 137 other followers

%d bloggers like this: