Analyze this!

There’s a question on the Database General forum on OTN containing the following observation:

I have some chained rows in some of my tables. I have exported, truncated, import back the data in the table. Then collect the stats. But I have the same Chained_count. Nothing has changed.

If you export, truncate the table, then re-import the data, you expect to get rid of any migrated rows – but if a row is too big to fit in a single block, or has more than 255 columns and a non-null value after the 255th, (either of which will be reported as a chained row) it’s not going to change after exporting and importing; so the first question to ask is: “What’s the definition of the table ?” optionally followed by: “Can you run some SQL to check for very long rows.”

I Wish

A couple of days ago I wrote about some things I’d like to see in index statistics, which means changing dbms_stats.gather_index_stats(). Here’s an idea for dbms_stats.gather_table_stats(). I’d like to see the number of chained and migrated rows counted separately in dba_tables when we run the packaged procedure. The optimizer will use information about chained or migrated rows, but the information is only collected if you use the analyze command (and even then the two figures are summed into the single value chain_cnt) .

Glossary

When writing about Oracle, people often use expressions whose meanings aren’t necessarily well-known to the less experienced user, so I’ve started building a glossary of commonly used Oracle terms. It’s going to take some time to build and it will keep growing as time passes. To make it easy to get to the page, I’ve added a special link near the top of the options column (in the “Special Links” list).

If you have any suggestions for terms feel free to add them in the comments. Please stick to one term per comment so that I can delete the comment after I’ve added the term to the glossary. As a guideline, you should be able to fit the term into a sentence like: “what is an X” rather than “how does X work.”

Before you look here, though, you might want to look at the Glossary in the Oracle manual (currently linked to the 11.2 documentation set).