When displaying the list of table subpartitions in SB, the query being run is from dba_tab_subpartitions and uses ‘ORDER BY PARTITION_NAME, SUBPARTITION_POSITION’. Could this query be updated to join to dba_tab_partitions to grab partition_position in place of partition_name? We have a number of tables where the partitions are displaying in different orders across these tabs. Thanks
Hi Michael,
Sorry for the slow response. When I created the subpartitions tab, I wanted to do that, but I hesitated because I thought that join might produce a slow query on systems with a lot of partitions and subpartitions. Do you have a lot? Here is the joined query (you might want to substitute the hard-coded table name here with one of your own). How does this query perform on your database?
select s.*
from dba_tab_partitions p, dba_tab_subpartitions s
where p.table_name = s.table_name
and p.table_owner = s.table_owner
and p.partition_name = s.partition_name
and p.table_owner = ‘SYS’
and p.table_name = ‘WRI$_OPTSTAT_SYNOPSIS$’
order by P.PARTITION_POSITION, S.SUBPARTITION_POSITION
-John
5 seconds for a table with 264 partitions and over 11,000 sub partitions
Well that’s not great, but not terrible either. I’ll make the change.