Query MLPPI

Database
Enthusiast

Query MLPPI

Hi All,
I would like to request a solution for this issue.
In APAC, there are Business dates in all the tables (MIS_DATE) which is partitioned by a month in a monthly larger tables.

However, there is a other date field (COMM_DATE) which is a common date across the regions to be stored in all the tables and need to use that for all the plaes.
PARTITION BY(RANGE_N(MIS_DATE BETWEEN DATE '2018-06-01' AND ADD_MONTHS(DATE,2) EACH INTERVAL '1' MONTH NO RANGE , UNKNOWN),
RANGE_N(COMM_DATE BETWEEN DATE '2018-06-01' AND ADD_MONTHS(DATE,2) EACH INTERVAL '1' MONTH NO RANGE, UNKNOWN))
In this scenario, MLPPI is created at the same grain (Date attributes and using Range partition) and it leads to have empty partitions. For example, 

Combined PartitionFirst LevelSecond LevelData
111Yes
212NULL
313NULL
421NULL
522Yes
623NULL
731NULL
832NULL
933Yes

  

As per my understanding, NULL partition will still be counted by the optimizer during the execution plan. Currently it is 2 bytes but it will increase to 8 bytes when the No of partitions crosses 64K.

Is there any way to handle the NULL partitions or can we change the definition of partition to limit the numer of partition created.

 

Thanks.


Accepted Solutions
Enthusiast

Re: Query MLPPI

Thanks alot Dave for your quick response.

I will definitely come back when I need any  clarification on this.

In case, if any other suggestion on this issue, then please help.

 

Thanks again.

1 ACCEPTED SOLUTION
6 REPLIES 6
Ambassador

Re: Query MLPPI

Hi,

 

There seems to be some confusion around terminology, so I'd like to address that first.

 

You talk about a 'null partition', but then seem to use 'NULL' a meaning 'no data'. Sorry if I've misunderstood your description.

 

With PPI (SL or ML) a partition can be defined to contain rows with NULL value for the partitioning column(s). This is the 'UNKNOWN' keyword that is used in your example definition.

 

If a partition contain no data then it is referred to as an 'empty partition' (not a 'null partition'). In the grid that you show below you have some 'empty partitions'.

 

With Teradata PPI processing, 'empty partitions' are not a problem. They take no space and the dbms ignores them during processing - because it knows that they are empty. Yes, empty partitions will probably (I haven't checked) be counted in the number of partitions being scanned which is shown in an explain plan, but as I said above during processing they are ignored.

 

On to your question "can we change the definition of partition to limit the numer of partition created.".

The number of partitions is controlled by the PARTITION BY clause. That is the only control that you have. In your example you'll have 25 partitions (5 at each level). Your PARTITION BY clause has controlled the number of partitions.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Query MLPPI

Hi Dave,

Thanks for your response on this.

 

I should have mentioned it as empty partition. Apologies for that.

 

I have also checked the explain plan that Optimizer counts the number of partitions though they are empty. And Query runs faster than the estimated time since it ignores empty partitions during the processing.

After 5 years, this might go upto 3600 combined partitions and only 60 will have the data. Will this have any performance impact on query processing.

I am not sure whether is it possible to drop or control the empty partitions created. 

Apologies if my query is wrong.

 

Thanks.

 

 

 

Ambassador

Re: Query MLPPI

Hi,

 

No problem with terminology, I just wanted to make sure that we were both talking about the same thing.

 

I wouldn't expect lots of empty partitions to cause a problem ("empty partitions are NOT a performance problem").

 

A couple of thoughts:

- If you've only got data in 60 partitions, is partitioning (at least this partitioning scheme) a sensible design for your table?

- In your original post you say that "However, there is a other date field (COMM_DATE) which is a common date across the regions to be stored in all the tables and need to use that for all the plaes." If column COM_DATE is used in 'all plans' (I'm assuming here that this last word should be 'plans' and not 'plaes') then it might be better to make COMM_DATE the first level partitioning rather than MIS_DATE. It is generally thought better to have your most commonly used selection column at a 'higher level' in a partitioning scheme.

 

HTH

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Query MLPPI

Hi,

 

Thank you so much for coming back on this issue. 

"If you've only got data in 60 partitions, is partitioning (at least this partitioning scheme) a sensible design for your table?"

        -- Understand your point here. The main reason for SI or JI is not used for this purpose because of the burden on system storage space,maintenance and number of tables involved.

Thanks for confirming that empty partitions will not create a problem in the performance. One of my doubts is cleared.

Now am just trying to see whether the second level partition definition can be defined in a better way so that number of partitions can be controlled.

Also checking if there is any way to suppress the empty partitions.

Thanks.

Ambassador

Re: Query MLPPI

No problem.

Come back with more question(s) if you have any.

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Query MLPPI

Thanks alot Dave for your quick response.

I will definitely come back when I need any  clarification on this.

In case, if any other suggestion on this issue, then please help.

 

Thanks again.