Whilst writing some code that referenced the DBA_SEGMENTS dictionary view today, I realised that the contents of the PARTITION_NAME column actually contains the name of the subpartition when the table is a subpartitioned table…a script and results to illustrate:
drop table jeff_unpartitioned purge / drop table jeff_partitioned purge / drop table jeff_subpartitioned purge / create table jeff_unpartitioned(col1 number,col2 number) / create table jeff_partitioned(col1 number,col2 number) partition by range(col1) (partition p1 values less than(100) ,partition p2 values less than(maxvalue) ) / create table jeff_subpartitioned(col1 number,col2 number) partition by range(col1) subpartition by list(col2) subpartition template (subpartition sub1 values(1) ,subpartition sub2 values(2) ) (partition p1 values less than(100) ,partition p2 values less than(maxvalue) ) / select segment_name,partition_name,segment_type from user_segments where segment_name like 'JEFF%' order by segment_name /
…gives the following results…
Table dropped.
Table dropped.
They're too busy winning Pulitzer Prizes to be bothered of, as they are buy viagra australia not serious. Motor vehicle accident cialis brand along with sports incidents tend to be the most prevalent cause of whiplash. Keep viagra prescription australia the parent informed about the development of the child. Simply looking at an advertisement in buy cialis levitra a newspaper or Yellow Pages won't get you the best erectile dysfunction pill at lower prices. Table dropped.
Table created.
Table created.
Table created.
Partition
SEGMENT_NAME Name SEGMENT_TYPE
------------------------ ------------------
JEFF_PARTITIONED P2 TABLE PARTITION
JEFF_PARTITIONED P1 TABLE PARTITION
JEFF_SUBPARTITIONED P1_SUB1 TABLE SUBPARTITION
JEFF_SUBPARTITIONED P2_SUB2 TABLE SUBPARTITION
JEFF_SUBPARTITIONED P1_SUB2 TABLE SUBPARTITION
JEFF_SUBPARTITIONED P2_SUB1 TABLE SUBPARTITION
JEFF_UNPARTITIONED TABLE
7 rows selected.
As you can see, the subpartitioned table shows the subpartition name in the PARTITION_NAME column. It’s not a big deal and I only noticed because I assumed there would be a SUBPARTITION_NAME column whilst I was writing my code…the failure in compilation led me to track this slightly erroneous situation down.
Why does this occur?
Well, if you delve into the code behind DBA_SEGMENTS you’ll see it refers to another view in the SYS schema called SYS_DBA_SEGS. The SQL behind SYS_DBA_SEGS selects all levels (Table, Partition and subpartitions) from the SYS.OBJ$ view, but then “loses” the partitions when joining to SYS.SYS_OBJECTS (the OBJ# from SYS.OBJ$ does not map to any row in SYS.SYS_OBJECTS via the OBJECT_ID column). The SQL then “loses” the table when joining to SYS.SEG$ – exactly why it does this I don’t fully understand, but I’m guessing it’s because those components of the composite object don’t actually have their own segment to physically store anything in since there are lower levels – in this case the subpartitions.
In any event, it’s a little bit of a gotcha and the column could probably do with being renamed to SUB_SEGMENT_NAME perhaps.
This makes sense to me because as you have said the view dba_segments shows information about physical segments. Since only subpartitions are physical segments in this case it only shows them.
I can see what it’s doing…but I don’t think it should be called PARTITION_NAME as that can be incorrect/misleading. Calling the column SUB_SEGMENT_NAME might make it clearer perhaps.
This one could certainly run and run, but I’d call out the National Guard before agreeing to ‘sub-segment’ anything!
A segment is something that physically exists, made up of extents and blocks, and which can store user data. So a sub-segment is what, then? Something that sort-of exists? In a quantum superposition of the states of existence and non-existence?!
No thanks. Either it physically exists or it doesn’t, in which case it’s either a segment or not-a-segment -and there’s no room between the two to be a sort-of-segment-but-not-quite!
Given the word ‘partition’ means (as a verb) ‘to chop up’, a sub-partition is just as much a partition as, er, a non-sub-partition is. It might be a smaller piece as a result of some finer chopping, but it’s still a piece of the segment as a whole. That makes it a partition in my book and the column name right.
I think I probably agree with you on not calling it SUB_SEGMENT_NAME – I didn’t like it at the time but couldn’t think of a better name, hence me using the “probably” get out clause in my original post…yeah, it was weak, I know!
I certainly don’t like a column being called PARTITION_NAME when it can contain SUBpartition names – on any level I just don’t agree that that’s right…it’s plainly misleading…and that’s exactly what it did to me as I tried to use that dictionary view.
In my view, it actually doesn’t matter what the word partition means (as a verb) – we’re talking about how Oracle uses the term – not the general public and their use of the English language…so I don’t agree with you on that front either.
If Oracle called it WIDGET and SUB WIDGET I’d be fine with that as long as the DBA_SEGMENTS didn’t have a column with WIDGET_NAME on it that could have either WIDGET_NAMES or SUB_WIDGET_NAMES in it depending on what type of segment it was.
Actually, thinking about it, I’d prefer it if the DBA_SEGMENTS view had two columns – PARTITION_NAME and SUBPARTITION_NAME – if the segment is for a partition then the PARTITION_NAME gets completed and the SUBPARTITION_NAME should be NULL. If the segment is for a subpartition then the SUBPARTITION_NAME and the PARTITION_NAME should both be filled out with their appropriate values. That would rule out the confusion in my view.
Now, where did I put my flak jacket…