Oracle DB: Statistics on Load

DZone 's Guide to

Oracle DB: Statistics on Load

Be aware that if you are direct-path loading IOTs, make sure to take the time to also collect statistics on that table before you start querying it in earnest.

· Database Zone ·
Free Resource

There are several changes that arrive with the optimizer when you move to 12c and above. There are changes to histograms, all of the adaptive features etc, but also, one of the simplest but coolest optimizer improvements when it comes to loading data. When you perform a direct-path insert into an empty table, or a create-table-as-select (CTAS) operation, there is no need to perform an additional DBMS_STATS call to gather statistics at the conclusion of the load. The load process itself handles that for you. Here’s a simple example of that in action:


Of course, many people are now aware of this functionality, so why does it warrant a blog post? Mainly to make readers aware of a boundary case. If the table you are creating or loading is an index-organized table (IOT), then you do not get the statistics gathered automatically.


Initially, I suspected that the reason for this is that since the statistics that are being picked up on the fly are being collected on the source data. It only becomes possible for those statistics to be transposed if the target table structure is the same as the source. But, if that were (strictly) the case, then other examples would exhibit a similar restriction, such as going from compressed data to uncompressed or vice versa. But you can see that the CTAS on load statistics are fine in this circumstance:


Similarly, even if I am copying from an identical index-organized table, the statistics will not be collected.


Whatever the root cause is, just be aware, that if you are direct-path loading IOTs, then make sure you take the time to to also collect statistics on that table before you start querying it in earnest.

optimizer, oracle, statistics

Published at DZone with permission of Connor McDonald , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}