Partitions With And Without Rows

Just a quick note on a SQL technique I stumbled on that I thought I’d share.

Having a partitioned fact table, with some partitions populated and others not, I have a series of materialized views that have not necessarily been entirely maintained. In this situation the materialized views are all partitioned with exactly the same scheme as the fact table but some of the partitions that have rows in the fact table do not have rows in the materialized views.

I needed a query to rapidly identify those cases so that the offending MV partitions could be maintained, and I came up with a requirement as follows: Identify all the partitions of the fact table that have at least one row, and for which there are no rows in the equivalent partition of a particular materialized view.

Because I had a usable naming convention for my partitions I was able to translate a partition name into a predicate very easily. for example given a partition name “Y2005M01” I can translate this into ’01-jan-2005′ using the simple method to_date(partition_name,'”Y”YYYY”M”MM’). You can try this yourself with:
select to_char(sysdate,'”Y”YYYY”M”MM’) from dual;
…and then …
select to_date(to_char(sysdate,'”Y”YYYY”M”MM’),'”Y”YYYY”M”MM’) from dual;

So this allowed a query of the form:

Select partition_name
From user_tab_partitions utp
Where table_name = ‘MY_FCT_TABLE’ And
Exists
(Select /*+ norewrite */ 1
From MY_FCT_TABLE
Where date_of_month =
to_date(utp.partition_name,
‘”Y”YYYY”M”MM’)) And
Not Exists
(Select /*+ norewrite */ 1
From MY_FCT_TABLE_MV
Where date_of_month =
to_date(utp.partition_name,
‘”Y”YYYY”M”MM’));

For a table having 175 partitions this identified the half-dozen or so problem partitions in around 3 seconds. Not too shabby, I thought.

Advertisements

2 thoughts on “Partitions With And Without Rows

  1. Could you use the TBL$OR$IDX$PART$NUM() function here? :D
    We’ve done sonething similar to identify partitions – sometimes I wonder why Oracle use LONGs to store things like partition bounds and view text in the data dictionary – it makes life so much harder for Joe Coder to get the job done.

    Why do you have empty MV partitions? or is that a silly question

  2. I’m not sure how the function would come into play there. Given our naming convention we can convert to and from partition names very easily, and ultimately that is what the function contributes to though in a more round-about way. Do you have a code sample you could share?

    We appear to have missing data in the mv’s because of refresh failures associated with snapshot too old errors. I’m not sure what to think about that at the moment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s