GeorgiG GeorgiG - 9 days ago 6
SQL Question

Does Subpartition in Oracle hinder partition performance

I am doing a research on partitions and got to a bit of a dead end on the subpartition topic.

Here is what I found out in the oracle manual (https://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm):

“The partitions of a range-list partitioned table are logical structures only, as their data is stored in the segments of their subpartitions. The list subpartitions have the same characteristics as list partitions. You can specify a default subpartition, just as you specify a default partition for list partitioning.”

So, first of all, I conclude that since subpartition have their own "segment", they have their own file. Am I correct?

Secondly, I wonder if that hinders partition effectiveness. Here is what I mean:

If we have a partition, most often it is on a single tablespace & file (please correct me if I'm wrong, I don't even know if it is possible to spread it on a several tablespaces/files). This means that when we search specifically in the partition, it will look through only one file.

Then again, if subpartition is realized through several segments(files) and even in several Tablespaces, doesn't it mean that when we query the partition (not the subpartition) it would take more time (relatively) ?

Lastly, I don't have the time, nor the possibility to test this theory, and that's why I'm asking. The end of my project is near (completely my fault) and I can't really test partitioning on my company's server.

Thanks in advance!

Answer

There is no direct relationship between segments and files. Segments are allocated space from a tablespace, which is composed of one or more files. The number of files accessed is not a significant factor in performance.