Toad World® Forums

SB / tables/ subpartitions

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.