Oracle counting rows in a PARTITION-CodePudding

I have the following setup, which includes PARTITIONS. Is there a query I can use that will provide a count for each PARTITION within the table..

I prefer not to have a possible estimate by gathering statistics as opposed to the actual count(*). Note the PARTITION name can be renamed!!

Below is my test CASE. Thanks to all who answer.


ALTER SESSION SET NLS_DATE_FORMAT = 'MMDDYYYY HH24:MI:SS';

CREATE TABLE dts (
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);


INSERT into dts(dt)
select to_date ( 
'01-08-2022','mm-dd-yyyy' )   
         ( level / 24 ) dt
from   dual
connect by level <= ( 24   ( 24 * 
    (to_date('01-15-2022' ,'mm-dd-yyyy') -       to_date('01-08-2022','mm-dd-yyyy') )
  ) 
) ;

SELECT table_name,
       partition_name,
       num_rows
FROM   user_tab_partitions
WHERE table_name not like 'BIN$%'
ORDER BY table_name, partition_name;

TABLE_NAME    PARTITION_NAME    NUM_ROWS
DTS    OLD_DATA     - 
DTS    SYS_P415755     - 
DTS    SYS_P415756     -

CodePudding user response：

Try this one:

declare
   c integer;
begin
   for aPart in (select partition_name FROM user_tab_partitions where table_name = 'DTS') loop
      execute immediate 'select count(*) from DTS PARTITION ('||aPart.partition_name||')' INTO c;
      DBMS_OUTPUT.PUT_LINE(aPart.partition_name || ' ' || c || ' rows');
   end loop;
end;

CodePudding user response：


select table_name ,Partition_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select /*  parallel(a,8) */
count(*) c from '||table_name||' partition ('||partition_name||') a ')),'/ROWSET/ROW/C')) as count 
from user_tab_partitions

TABLE_NAME  PARTITION_NAME  COUNT
DTS OLD_DATA    0
DTS SYS_P415799 167
DTS SYS_P415800 25

CodePudding user response：

You can rely on optimizer statistics for a perfect count, as long as you're using the default sample size and algorithm.

begin
    dbms_stats.gather_table_stats
    (
        ownname          => user,
        tabname          => 'DTS',
        estimate_percent => dbms_stats.auto_sample_size
    );
end;
/

If you run the above PL/SQL block, your original query against USER_TAB_PARTITIONS will return the correct NUM_ROWS. Since version 11g, Oracle scans the entire table to calculate statistics. While it uses an approximation for counting things like non-distinct values and histograms, it's trivial for the algorithm to get a completely accurate row count.

The manual is not super clear about this behavior, but you can put it together from the manual and other articles that discuss how the new algorithm works. From the "Gathering Optmizer Statistics" chapter of the "SQL Tuning Guide":

To maximize performance gains while achieving necessary statistical accuracy, Oracle recommends that the ESTIMATE_PERCENT parameter use the default setting of DBMS_STATS.AUTO_SAMPLE_SIZE. In this case, Oracle Database chooses the sample size automatically. This setting enables the use of the following:

A hash-based algorithm that is much faster than sampling

This algorithm reads all rows and produces statistics that are nearly as accurate as statistics from a 100% sample. The statistics computed using this technique are deterministic.

Most likely, you don't even need to specify the ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE argument. It is extremely unlikely for someone to set that preference for a table or the system. You can use the below query to see how your statistics are typically gathered. Most likely the query will return "DBMS_STATS.AUTO_SAMPLE_SIZE":

select dbms_stats.get_prefs(pname => 'ESTIMATE_PERCENT', ownname => user, tabname => 'DTS')
from dual;

CodePudding user response：

Oracle provides a handy function PMARKER exact for this purpose

SELECT DBMS_MVIEW.PMARKER(p.rowid) PMARKER, count(*) cnt, min(dt), max(dt)
from dts p
group by DBMS_MVIEW.PMARKER(p.rowid)
order by 1;

   PMARKER        CNT MIN(DT)             MAX(DT)            
---------- ---------- ------------------- -------------------
     74312        167 08.01.2022 01:00:00 14.01.2022 23:00:00
     74313         25 15.01.2022 00:00:00 16.01.2022 00:00:00

Note that you need not know the partition name, the partition key column value lets you access the partition using the partition extended names:

Example for the first partition

select count(*) from dts  partition for (DATE'2022-01-08');

  COUNT(*)
----------
       167