SQL Performance (MOSC)

MOSC Banner

Dynamic sampling on partitioned table

edited Jan 5, 2018 4:24PM in SQL Performance (MOSC) 11 commentsAnswered

Hi ,

Our oracle database is 12.1.0.2 , we have a huge partitioned table with interval partitions and each partition has about 32 hash sub partitions

Statistics have been collected at table and partition level. Thr hash sub partitions do not have statistics collected

The global_stats on the table and partitions is YES

While for the sub partitions the global stats is NO

There are no stale stats in the table

Still when I run a query on the table with a where clause , the explain plan shows it is using dynamic sampling instead of optimizer statistics. It is doing a full table scan.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center