Do I need to gather statistics on an Oracle table that has no index?

0
votes

I have an Oracle 11g DB, and some of the tables are truncated and re-imported from a source system regularly. I have no index on those tables (for import performance reasons), though they have many million rows. Do I need to gather the statistics of the tables every time I import them? When I treat the table I need to analyze and process every row anyway.

2
sqldatabaseoracleoracle11g
Truncate removes the maintenance cost against frequent DMLs, since it brings down the HIGH WATERMARK to zero. Look at my answer below. - Lalit Kumar B
Please stop voting to move these questions to DBA. This question clearly meets all 4 criteria listed in the Help Center. In a data warehouse, this topic is very much a programmer problem, not a DBA problem. - Jon Heller

2 Answers

1
votes

Optimizer statistics should be gathered after any significant data change.

That's a good general rule to follow. There are exceptions but a lack of indexes is not a good enough reason. It's true that one of the main benefits of accurate statistics is deciding whether to use a full table scan or an index access. But that's only one of the many ways statistics are used. For example, if Oracle incorrectly believes a table has no rows it may still use a NESTED LOOP on it, which could be disastrous if the table really has millions of rows.

Settings can be modified to reduce statistics gathering time. Specifically setting the DEGREE may vastly improve performance. 11g has a great default ESTIMATE_PERCENT, but sometimes it may be useful to use a very low value. Statistics can also be imported from previous runs, if the data is very similar.

Another factor to consider is that leaving large tables un-analyzed may affect later statistics gathering jobs. Most databases have a nightly job to gather stale and missing statistics. Even a data warehouse will often have such a job, but with the assumption that really large statistics jobs will be run manually. Leaving large statistics jobs to the default processes may overwhelm that system and cause other stale statistics to wait longer.

0
votes

Since you do a TRUNCATE every time, Oracle brings the HIGH WATERMARK to zero. So, you don't have to worry about incremental maintenance.

But, regarding the query performance while you hit the table, you MUST follow the NORMALIZATION rules. Check the query EXECUTION PLAN, if you see FULL TABLE SCAN for LOW CARDINALITY, then you should consider that column to be indexed.

I would suggest CTAS method to you if you need to build the table too frequently. CTAS means create table as select. It would be much efficient. Also, in addition, you can use NO LOGGING and other features as required, to reduce the UNDO and REDO.