1

For a partitioned table I can count the rows in a partition quickly with a query like this:

select count(*)
from db.table Partition (P)
where column value is Null

Is there a way to do this for multiple partitions?

My desired result is the following-

Partition name         Count of Null Rows
P1                         20000
P2                         12000
P3                          30000
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Jae Trapper
  • 11
  • 1
  • 2

3 Answers3

2

It won't be as efficient as using FROM TABLE PARTITION (p), but you could do this:

select o.object_name, o.subobject_name partition, count(*) 
from sh.sales s 
inner join all_objects o on o.object_id = dbms_rowid.rowid_object(s.rowid)
where o.subobject_name in ('SALES_Q1_2000','SALES_Q2_2000')
-- for performance, you can add conditions to restrict to the partitions of interest, if you know them
-- e.g., and s.sale_date between to_date('01-JAN-2000','DD-MON-YYYY') and to_date('30-JUN-2000','DD-MON-YYYY')
group by o.object_name, o.subobject_name;

Basically, it uses the rowid to determine the partition for each row and counts them.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
0

This has been answered before. If your table is not partitioned on date, simply change the predicate and allow Oracle to do the work for you. Many thanks to Mr. Cave :)

Select from several partitions at once

You almost never want to use the PARTITION clause when querying a partitioned table. You almost always want to specify a predicate that allows Oracle to do partition pruning on its own.

SELECT t.column1, t.column2
FROM first_table t
WHERE t.partitioned_date_column >= <> AND t.column3 = 'someVal'

When you specify a predicate on the date column that the table is partitioned on, Oracle can automatically determine which partition(s) need to be accessed.

1991DBA
  • 805
  • 1
  • 9
  • 18
  • The OP wants the result set to show a breakdown of rows by partition. Can you please clarify how you accomplish that with your approach? – Matthew McPeak Dec 10 '17 at 13:49
  • There is already a better answer posted, but by using count(*) in the select statement, OP could get the number of rows in each partition by manipulating the where clause to match the same condition(s) that have been used for table partitioning – 1991DBA Dec 11 '17 at 19:06
  • My partition is based on a company id. Which is a ranged partition is that something that would work? – Jae Trapper Dec 12 '17 at 16:36
  • Hi Jae, yep! You could do a where clause that looks something like where company id between 0 and x, etc. – 1991DBA Dec 14 '17 at 16:18
0

After gathering statistics (with the default, auto-sample size), the numbers can be retrieved almost instantly with this query:

select partition_name, num_nulls
from all_part_col_statistics
where owner = user
    and table_name = 'TABLE_NAME'
    and column_name = 'COLUMN_NAME'
order by partition_name;
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Those numbers will still be estimates –  Dec 11 '17 at 07:01
  • @a_horse_with_no_name For 11g+, the numbers will be accurate unless you explicitly change the sample size. By default, Oracle reads the whole table and will be 100% accurate for the number of rows and the number of nulls. – Jon Heller Dec 11 '17 at 07:12