Let's talk about Multilevel Partitioned Primary Indexes (MLPPI)

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.

I still remember when PPI was first introduced in V2R5 and all the questions that came up in my mind in terms of utilization and performance impact. Long time has passed and most of the Teradata systems nowadays have at least one large PPI table defined. Also most of the questions in terms of performance impact are already gone especially with the use of "partition elimination" and "dynamic partition elimination".  Now in TD12, a multilevel PPI was introduced and again the same kind of questions came up in my mind in terms of utilization and performance impact, as listed below.

  • Which tables are good candidates for this new feature (MLPPI)?
  • What will be the performance impact of this new feature (MLPPI) on the overall system utilization?

After thinking about it, I came up with a good candidate (a PPI table) where I could apply MLPPI and I want to share the test case with you. But before I present the test case, there are two important factors that need to be considered first:

  • The Maximum Number of Partitions allowed is still 65,535 for a MLPPI table
  • Which order the partition expressions need to be defined on the MLPPI table?

So, make sure that the total number of partitions on a MLPPI table will not go above the limit before creating it.

Here are some of the best practices recommendations in terms of how to define the order of the partition expressions on a MLPPI table.

"To achieve optimal performance, you should specify a partitioning expression that is more likely to evoke partition elimination for queries at a higher level and specify those expressions that are not as likely to evoke partition elimination either at a lower level, or not at all. You should also consider specifying the partitioning expression with the greatest number of partitions at the lowest level."

This means that it is better to define the partitioning expressions on a MLPPI table by descending order based on the total number of partitions of each set. The set with the greatest number of partitions needs to be defined first or the partition column that is most frequently used needs to be defined first.

 Test Case

The original PPI table associated with this test case contains rebate information for multiple vendors and the associated total sales by item. This PPI table was originally partitioning by business date and it is loaded three times a day with three different files containing the following information:

  • regular rebate data (incremental - a new business date partition)
  • unallocated rebate data (full refresh of a business date partition) 
  • rebate adjustments data (full refresh of a business date partition)

There are also two AJI's associated with this PPI table with the same business date partition. These AJI's were created to improve the performance of critical reports and it is also affected by the load process because the AJI's are not dropped/re-created during the load processes.

 Let's take a look on the original structures.

Original PPI Table

CREATE TABLE DATABASENAME.REBATE
(.....)
PRIMARY INDEX REBATE_PI ( VENDOR_ID ,PROGRAM_ID ,ITEM_ID ,BUSINESS_UNIT_CD ,SALES_LOCATION_ID ,BUSINESSDAY_DT )
PARTITION BY RANGE_N(BUSINESSDAY_DT BETWEEN
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
2012011 AND 2012527 EACH 1 ,
2013011 AND 2013527 EACH 1 ,
NO RANGE)
INDEX ( ITEM_ID ,BUSINESSDAY_DT );

  Original AJI's (PPI)

CREATE JOIN INDEX DATABASENAME.REBATE_AJI1
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
.....
PRIMARY INDEX ( AREA_ID ,ITEM_ID ,BUSINESSDAY_DT ,VENDOR_ID )
PARTITION BY RANGE_N(BUSINESSDAY_DT BETWEEN
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
2012011 AND 2012527 EACH 1 ,
2013011 AND 2013527 EACH 1 ,
NO RANGE)
INDEX ( ITEM_ID ,BUSINESSDAY_DT )
INDEX ( AREA_ID ,BUSINESSDAY_DT )
INDEX ( BUSINESSDAY_DT ,PROGRAM_ID );
CREATE JOIN INDEX DATABASENAME.REBATE_AJI2
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar )
.....
PRIMARY INDEX ( AREA_ID ,ITEM_CLASS_ID ,BUSINESSDAY_DT ,VENDOR_ID )
PARTITION BY RANGE_N(BUSINESSDAY_DT BETWEEN
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
2012011 AND 2012527 EACH 1 ,
2013011 AND 2013527 EACH 1 ,
NO RANGE)
INDEX ( ITEM_CLASS_ID ,BUSINESSDAY_DT )
INDEX ( AREA_ID ,BUSINESSDAY_DT )
INDEX ( BUSINESSDAY_DT ,PROGRAM_ID );

The major CPU impact on the system is during the load of the unallocated rebate file and the load of the rebate adjustments file. Because the business date partition is already populated and a full refresh of the partition is necessary on each load. This means the data from a business date partition is deleted from & inserted into the table and AJI's on each load.

The solution to minimize the CPU impact of the load processes on the system was to create a new column PROGRAM_SOURCE_CD to be able to identify each load and to be able to add a second partition expression based on this new column in the table and AJI's. Here is the correlation of the PROGRAM_SOURCE_CD partition values with the load files.

  • PROGRAM_SOURCE_CD = 1 ==> regular rebate file
  • PROGRAM_SOURCE_CD = 2 ==> unallocated rebate file
  • PROGRAM_SOURCE_CD = 3 ==> rebate adjustments file

By doing this, every time a new file is loaded for the same business date, the data is inserted into an empty partition on the table & AJI's and there is no need to delete the data from the business date partition first. There was a 90% reduction in terms of CPU utilization associated with the load processes after this change was implemented. There was no impact on the reporting side because the new partition expression was also added to the AJI's and it was added as a filter criteria on the reports. The reports always had a filter criteria based on business date and an automatic filter criteria for program source code was added. This means that when a program source code filter criteria is not selected by the user, then the report automatically select all the program source code values. 

Let's take a look on the new structures.

New MLPPI Table

CREATE TABLE DATABASENAME.REBATE
(.....)
PRIMARY INDEX REBATE_PI ( VENDOR_ID ,PROGRAM_ID ,ITEM_ID ,BUSINESS_UNIT_CD ,SALES_LOCATION_ID ,BUSINESSDAY_DT )
PARTITION BY (
RANGE_N(BUSINESSDAY_DT BETWEEN
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
2012011 AND 2012527 EACH 1 ,
2013011 AND 2013527 EACH 1 ,
NO RANGE),
CASE_N(
PROGRAM_SOURCE_CD = 1 ,
PROGRAM_SOURCE_CD = 2 ,
PROGRAM_SOURCE_CD = 3 ,
NO CASE, UNKNOWN) )
INDEX ( ITEM_ID ,BUSINESSDAY_DT );

New AJI's (MLPPI)

CREATE JOIN INDEX DATABASENAME.REBATE_AJI1
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
.....
PRIMARY INDEX ( AREA_ID ,ITEM_ID ,BUSINESSDAY_DT ,VENDOR_ID )
PARTITION BY (
RANGE_N(BUSINESSDAY_DT BETWEEN
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
2012011 AND 2012527 EACH 1 ,
2013011 AND 2013527 EACH 1 ,
NO RANGE),
CASE_N(
PROGRAM_SOURCE_CD = 1 ,
PROGRAM_SOURCE_CD = 2 ,
PROGRAM_SOURCE_CD = 3 ,
NO CASE, UNKNOWN) )
INDEX ( ITEM_ID ,BUSINESSDAY_DT )
INDEX ( AREA_ID ,BUSINESSDAY_DT )
INDEX ( BUSINESSDAY_DT ,PROGRAM_ID );
CREATE JOIN INDEX DATABASENAME.REBATE_AJI2
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar )
.....
PRIMARY INDEX ( AREA_ID ,ITEM_CLASS_ID ,BUSINESSDAY_DT ,VENDOR_ID )
PARTITION BY (
RANGE_N(BUSINESSDAY_DT BETWEEN
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
2012011 AND 2012527 EACH 1 ,
2013011 AND 2013527 EACH 1 ,
NO RANGE),
CASE_N(
PROGRAM_SOURCE_CD = 1 ,
PROGRAM_SOURCE_CD = 2 ,
PROGRAM_SOURCE_CD = 3 ,
NO CASE, UNKNOWN) )
INDEX ( ITEM_CLASS_ID ,BUSINESSDAY_DT )
INDEX ( AREA_ID ,BUSINESSDAY_DT )
INDEX ( BUSINESSDAY_DT ,PROGRAM_ID );

Please notice that the partitioning expression (BUSINESSDAY_DT) with the greatest number of partitions was defined first and it is also the partition column that it is the most frequently used on the reports. After that comes the second partition expression (PROGRAM_SOURCE_CD) with the lowest number of partitions. The maximum number of partitions for this new MLPPI table (6 years * 365 days * 3 files) = 6,570. There is still a lot room to grow based on the maximum number of partitions allowed (65,535).

New Test Case

Let's extrapolate this solution into a new test case. Let's consider a large PPI table also with a partitioning expression based on a business date containing three years of Sales data. An example for that will be a large Sales Detail PPI table. Let's consider that this table is being loaded once a day during the batch cycle but there is a new requirement and this table will need to be loaded every half an hour every day. Let's also consider that there are multiple AJI's associated with this table and all AJI's have the same partitioning expression based on a business date. Let's also consider that the AJI's are not dropped/re-created during the load processes. The main goal is to minimize the CPU utilization impact on the system when the load process starts loading this table every half an hour every day.

Let's take a look on the original structures.

Original PPI Table

CREATE TABLE DATABASENAME.SALES_DETAIL 
(.....)
PRIMARY INDEX SALES_DETAIL_NUPI ( CUST_ORD_ID )
PARTITION BY RANGE_N(BUSINESSDAY_DT BETWEEN
2007011 AND 2007527 EACH 1 ,
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
NO RANGE)
INDEX SALES_DETAIL_NUSI1 ( BUSINESSDAY_DT ,ITEM_ID )
INDEX SALES_DETAIL_NUSI2 ( BUSINESSDAY_DT ,SALES_LOCATION_ID )
INDEX SALES_DETAIL_NUSI3 ( BUSINESSDAY_DT ,INVENTORY_LOCATION_ID );

Original AJI's (PPI)

CREATE JOIN INDEX DATABASENAME.SALES_DETAIL_AJI1 
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
.....
PRIMARY INDEX ( ITEM_ID ,BUSINESSDAY_DT ,SLS_TYP_CD )
PARTITION BY RANGE_N(BUSINESSDAY_DT BETWEEN
2007011 AND 2007527 EACH 1 ,
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
NO RANGE)
INDEX ( ITEM_ID ,BUSINESSDAY_DT );
CREATE JOIN INDEX DATABASENAME.SALES_DETAIL_AJI2 
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
......
PRIMARY INDEX ( LOCATION_SOURCE_REF_NUM ,DISTRICT_ID ,ITEM_CLASS_ID ,BUSINESSDAY_DT ,VENDOR_ID ,SLS_TYP_CD )
PARTITION BY RANGE_N(BUSINESSDAY_DT BETWEEN
2007011 AND 2007527 EACH 1 ,
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
NO RANGE)
INDEX ( ITEM_CLASS_ID ,BUSINESSDAY_DT );
CREATE JOIN INDEX DATABASENAME.SALES_DETAIL_AJI3 
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
......
PRIMARY INDEX ( REGION_ID, AREA_ID ,ITEM_CLASS_ID ,BUSINESSDAY_DT ,VENDOR_ID ,SLS_TYP_CD )
PARTITION BY RANGE_N(BUSINESSDAY_DT BETWEEN
2007011 AND 2007527 EACH 1 ,
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
NO RANGE)
INDEX ( ITEM_CLASS_ID ,BUSINESSDAY_DT )
INDEX ( AREA_ID ,BUSINESSDAY_DT );

By using the original structures, the first load process will insert data into an empty partition but after that, the second load process for the same business day will create a transient journal, because the partition is not empty anymore. All the following load processes for the same business day will also create a transient journal for the same reason. This will have a major impact on the CPU utilization associated with the load processes.

A similar solution based on the results of the last test case can be applied into this new test case. Let's create a new column SOURCE_FILE_NBR to be able to identify each load and to be able to add a second partition expression based on this new column in the table and AJI's. The maximum number of partitions for this new partition expression will be 48 (2 partitions per hour * 24 hours).

By doing this, every time a new file is loaded for the same business date, the data is inserted into an empty partition on the table & AJI's and no transient journal is created. The CPU utilization impact should be minimum because every load process will be inserting data into an empty partition.

Let's take a look on the new structures.

New MLPPI Table

CREATE TABLE DATABASENAME.SALES_DETAIL 
(.....)
PRIMARY INDEX SALES_DETAIL_NUPI ( CUST_ORD_ID )
PARTITION BY (
RANGE_N(BUSINESSDAY_DT BETWEEN
2007011 AND 2007527 EACH 1 ,
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
NO RANGE),
RANGE_N(SOURCE_FILE_NBR BETWEEN
1 AND 48 EACH 1 ,
NO RANGE, UNKNOWN) )
INDEX SALES_DETAIL_NUSI1 ( BUSINESSDAY_DT ,ITEM_ID )
INDEX SALES_DETAIL_NUSI2 ( BUSINESSDAY_DT ,SALES_LOCATION_ID )
INDEX SALES_DETAIL_NUSI3 ( BUSINESSDAY_DT ,INVENTORY_LOCATION_ID );

New AJI's (MLPPI)

CREATE JOIN INDEX DATABASENAME.SALES_DETAIL_AJI1 
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
.....
PRIMARY INDEX ( ITEM_ID ,BUSINESSDAY_DT ,SLS_TYP_CD )
PARTITION BY (
RANGE_N(BUSINESSDAY_DT BETWEEN
2007011 AND 2007527 EACH 1 ,
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
NO RANGE),
RANGE_N(SOURCE_FILE_NBR BETWEEN
1 AND 48 EACH 1 ,
NO RANGE, UNKNOWN) )
INDEX ( ITEM_ID ,BUSINESSDAY_DT );
CREATE JOIN INDEX DATABASENAME.SALES_DETAIL_AJI2 
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
......
PRIMARY INDEX ( LOCATION_SOURCE_REF_NUM ,DISTRICT_ID ,ITEM_CLASS_ID ,BUSINESSDAY_DT ,VENDOR_ID ,SLS_TYP_CD )
PARTITION BY (
RANGE_N(BUSINESSDAY_DT BETWEEN
2007011 AND 2007527 EACH 1 ,
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
NO RANGE),
RANGE_N(SOURCE_FILE_NBR BETWEEN
1 AND 48 EACH 1 ,
NO RANGE, UNKNOWN) )
INDEX ( ITEM_CLASS_ID ,BUSINESSDAY_DT );
CREATE JOIN INDEX DATABASENAME.SALES_DETAIL_AJI3 
AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),
......
PRIMARY INDEX ( REGION_ID, AREA_ID ,ITEM_CLASS_ID ,BUSINESSDAY_DT ,VENDOR_ID ,SLS_TYP_CD )
PARTITION BY (
RANGE_N(BUSINESSDAY_DT BETWEEN
2007011 AND 2007527 EACH 1 ,
2008011 AND 2008527 EACH 1 ,
2009011 AND 2009527 EACH 1 ,
2010011 AND 2010527 EACH 1 ,
2011011 AND 2011527 EACH 1 ,
NO RANGE),
RANGE_N(SOURCE_FILE_NBR BETWEEN
1 AND 48 EACH 1 ,
NO RANGE, UNKNOWN) )
INDEX ( ITEM_CLASS_ID ,BUSINESSDAY_DT )
INDEX ( AREA_ID ,BUSINESSDAY_DT );

Please notice that the partitioning expression (BUSINESSDAY_DT) with the greatest number of partitions was defined first again and it is also the partition column that it is the most frequently used. After that comes the second partition expression (SOURCE_FILE_NBR) with the lowest number of partitions. The maximum number of partitions for this new MLPPI table is (48 files per day * 365 days * 3 years) = 52,560. The maximum number of partitions for this case is close to the maximum number of partitions allowed (65,535). This new table will not be able to store four years of data because it will go over the maximum number of partitions allowed (48 files per day * 365 days * 4 years) = 70,080. Also, if the requirements change for this table to be loaded every 15 minutes all day long, then the maximum feasible number of partitions will be (96 files per day * 365 days * 1.75 years) = 61,320. The bottom line is that the number of years will decrease as the numbers of files per day increases because of the maximum number of partitions allowed (65,535).

21 Comments
N/A
Very slick way to begin implementing a near real-time datawarehouse. What is the impact on the reporting queries while these half hour loads are going on--especially during work hours?
It will depend on the overall system utilization while the load is running and also the volume of the data. It will also depend on how the reporting views are setup. Is dirty reads are allowed or not? The best option is "Lock for Access" on the views to avoid blocking on the system.
Enthusiast
This seems to be a good way to leverage the MLPPI for pure data appends. What has been the impact of using the AJI during the load, especially during the exceptional occurence of a rollback situation.
the process is loading into an empty partition on the table and on the AJI's every time and also deleting an entire partition on the table and on the AJI's. Therefore, the impact of a rollback is minimum.
Hi,
I have good experience with Symtex and would like to share my experiences.
SymTex is leading provider of Database Development, Business Intelligence, Software Development and Network Security. One can contact them in this regard to discover their consultancy and support services for IT solutions.
http://www.symtex.co.uk
SymTex
Eliot Park Innovation Centre
Barling Way
Nuneaton
Warwickshire
CV10 7RH
UK
0800 58 70 949
Welcome back to the series of blogs on cool Viewpoint features. pass4sure 642-504 Hopefully by now, you've heard about the Teradata "time travel" feature called rewind. Rewind allows one to pass4sure 642-524
easily and seamlessly view portlet data and interactions by going back in time for analysis, comparison, or just general reporting. Rewind, dare I say, is a paradigm shift in systems analysis and management. If interested in learning more specifically about RewindWelcome back to the series of blogs on cool Viewpoint features. Hopefully by now, you've heard about the Teradata "time travel" feature called rewind. Rewind allows one pass4sure 646-363 to easily and seamlessly view portlet data and interactions by going back in time for analysis, comparison, or just general reporting. Rewind, dare I pass4sure 642-982 say, is a paradigm shift in systems analysis and management. If interested in learning more specifically about Rewind.
Enthusiast
I have a performance tuning problem related to loading ~ 25 million records / day over 8 different source systems. Multi Loading to base table consumes the majority of resources on this warehouse..the majority of resources appear to be on index updates, there are about 10 secondary indexes on table which contains data for 6 months. The data consists of CDR's and is Primary Indexed on Calling_Number, Call_Date and Call_Time, Primary Partition Call_Date by day.

The article suggests changing base table to use MLPPI partitioned on Date and Source System ID so that loads go into empty partitions, the main benefit being that no Transient Journals are generated when inserting into empty partitions. Does this apply when Inserting via Multiload, as I have been informed by DBA that Multiload does not generate any transient journals ?
There is no TJ when using a MLOAD but it will be inserting into an empty for every source system. But the process will still have to maintain the NUSI's.
Hi mtmoura,
I have some questions regarding Teradata, can u share ur mail id with me or your chat id so i can talk with u.......I hope u will do some needful.....Thanks

Regards
Bhanwar
bpshekhawat@gmail.com
benifits of MLPPI over PPI?
The benefits will depend on each case. The MLPPI provides more granularity for a partition and this can improve the report and load processes. My email is mtmoura@us.ibm.com
Teradata Employee
Great information Moura.
I'm not clear. Why no TJ is generated when data is loaed into empty partition.
it is the same as loading into an empty table. It does not create a TJ.
Enthusiast
I've been struggling to get fast path delete when trying to delete all the data in a paritition when using MLPPI. Even if I directly reference the partition number, I still get "delete FROM partition" instead of "delete OF partition" in the explain which tells me I am deleting rows one at a time and incurring the logging that goes with that.
Dropping the partitions is not really an option in this case. I still need the partition, I just want to empty it and do a complete reload. Any input on that?
N/A
Hi,
Currently we have a database with 15 tables which we are trying to migrate from Oracle to Teradata. In oracle these tables are partitioned on Check_out_Dt and this is a reservation database. The check_out_dt can keep changing, in Oracle it was not a problem to still use that as a partitioning column, where as in Teradata i came to know that a volatile column is not recommended to be used as a PPI. Just so you know the value of this column changes only on 1 to 3% of the data in the table on a daily basis. And also our where clause will contain a constraint on the Check_out_dt. So please let us know your recommendations

Thanks,
Lathav
Enthusiast
Lathav,

If check_out_dt is often used to access data, then I would go ahead and partition on it. One thing to note is that if you are using MERGE to update your data, you will want to update the PI and Parition columns first where they have changed, then apply updates for all other columns. This will get the affected rows into the correct amps and partitions before trying to update them with MERGE. if you don't do this and include the columns in the SET clause of a MERGE statement, you'll get the old approach to using MERGE which won't perform as well. There is a white paper or Orange Book on this.
can i create PPI on PI column?
Enthusiast
Yes Den_Lo_King, and in fact if the PI is a UPI then the partitioning expression has to be on only those columns of the PI as the performance of the system would be heavily degraded without the system enforcing it. This is because it would need to probe all partitions for hash values to ensure uniqueness if it was not provided the partition value to do a row key look-up.
Hi..,

when table partitioned on MLPPI(4 columns used in partition), whenever we are running update SQL on table,do we need to use all the Partitioned columns in condition or ( one or two) column in condition is fine or not , what would be the impact if we are not using all the columns which are part MLPPI?
Is the CPU consumption will be more?
Enthusiast
Hi All

As Marcio has mentioned above

"This means that it is better to define the partitioning expressions on a MLPPI table by descending order based on the total number of partitions of each set. The set with the greatest number of partitions needs to be defined first or the partition column that is most frequently used needs to be defined first."

I am trying to clear my understanding in this regard.

1.

Say, I have a table with two columns DATE column and ID column. The date column has RANGE_N(DT BETWEEN DATE '2013-01-01' AND DATE '2013-12-31' EACH INTERVAL '7' DAY) this will create 52 partitions. The ID column has RANGE_N(ID BETWEEN 1 AND 100 EACH '10') this will create 10 partitions.

So shall I define DT as the higher level partition and ID as the lower level partition, as DT is creating greatest number of partitions?

Is my understanding correct?

2.

How the "typical rows/value" for a column is related to the ordering of MLPPI? 

For example, if my DT column has typically 50000 rows/value and ID column has 100 distinct values ranging from 1 to 100, is it still recommanded to create DT partition at the higher level and ID at the lower level?

Actually, I have read in the TD manual that higher level partition column should have more contiguous partitions to scan and skip. I am just trying to relate that with some practical questions which I am facing in my regular work.

It is appreciated If any one can clear my understanding.

Thanking You

Santanu

Enthusiast

I have a table that has 200 million records and has BUS_DT as a partition . CAn i alter the table and create partition on a column COL_ID ? Is that possible via a ALTER stmt. Can anybodu provide me witht the query.