The greatest decrement in the performance of querying column-partitioned tables occurs when the following conditions take place.
- Most or all of the columns in a column-partitioned table are projected by a request.
- The request is not selective.
- The table being queried has thousands of column partitions.
- The retrieval performance for a column-partitioned table or join index is not good when the number of column partition contexts that are available is significantly fewer than the number of column partitions that must be accessed to respond to the query. There are at least eight available column partitions contexts. Depending on your memory configuration, there may be more available contexts.
When this happens, consider reconfiguring the table or join index to decrease the number of column partitions that need to be accessed by combining column partitions so there are fewer of them.
- The table being queried has enough row-partitioned levels that the number of physical rows in populated combined partitions is small, and the physical rows contain one or a small number of column partition values.