Teradata Multi PPI （多级分区表）

最新推荐文章于 2017-01-22 10:53:47 发布

cuikui4734

最新推荐文章于 2017-01-22 10:53:47 发布

阅读量412

点赞数

Some one ask me these Questions：
I have created MLPPI on two columns in a big table. First columns is a date column and second is area_code having 20 distinct values (1,2,3..)

I want to know that -
Do I need to use both MLPPI columns in my query in order to make use of PPI or partition will be used even if the first DATE column is used in the filter condition?
How can I find out from explain plan how many partitions are being used. When I am using DATE only as a filter condition, explain plan shows partitions as double the number of DATES used ("2 partitions", 4 partitions..). When DATE is used along with AREA_CODE (single or multiple), it says as many partitions as many DATEs. Does that mean that partitions are not used when only single column is used?

[@more@]

In my understood，the Answers should be：
Partition elimination can occur for the first, second or both.
The explain plan indicates the number of combined partitions out of the total combined partitions (the product of the number of partitions defined at each level) that are used. For example,if a single DATE is used as a filter, you should get 20 partitions (meaning 20 combined partitions). Not sure why you are getting multiples of 2 (the actual DDL and query with explain would need to be provided to understand this further). If a single DATE and a single AREA_CODE is used as the filter, should only be 1 partition.

Let's do a test:
CREATE MULTISET TABLE TEST.MULTI_PPI_TEST ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
GRP_ID BIGINT TITLE '集团编号',
GRP_NAME VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC TITLE '集团名称',
MEB_CNT INTEGER TITLE '集团成员数',
BILLING_CYCLE_ID INTEGER TITLE '帐务月',
LATN_ID INTEGER TITLE '分公司标识' )
PRIMARY INDEX ( GRP_ID )
PARTITION BY ( RANGE_N(LATN_ID BETWEEN 10001 AND 10013 EACH 1 ,
NO RANGE, UNKNOWN),RANGE_N(BILLING_CYCLE_ID BETWEEN 200701 AND 200812 EACH 12 ,
200901 AND 201212 EACH 1 , NO RANGE, UNKNOWN) );

Explain select * from TEST.MULTI_PPI_TEST

1) First, we lock a distinct TEST."pseudo table" for read on a
RowHash to prevent global deadlock for TEST.MULTI_PPI_TEST.
2) Next, we lock TEST.MULTI_PPI_TEST for read.
3) We do an all-AMPs RETRIEVE step from TEST.MULTI_PPI_TEST by way of
an all-rows scan with no residual conditions into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with low confidence to be 2 rows (98 bytes).
The estimated time for this step is 0.03 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.03 seconds.
全表扫描，没什么好说的了。

Explain select * from TEST.MULTI_PPI_TEST where billing_cycle_id = 201007

1) First, we lock a distinct TEST."pseudo table" for read on a
RowHash to prevent global deadlock for TEST.MULTI_PPI_TEST.
2) Next, we lock TEST.MULTI_PPI_TEST for read.
3) We do an all-AMPs RETRIEVE step from 15 partitions of
TEST.MULTI_PPI_TEST with a condition of (
"TEST.MULTI_PPI_TEST.BILLING_CYCLE_ID = 201007") into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with no confidence to be 1 row (49 bytes).
The estimated time for this step is 0.03 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.03 seconds.
限制了BILLING_CYCLE_ID为1个partition，则扫描的partion数目应为1*15，即为15个
partition，也没有问题。

Explain SELECT * FROM TEST.MULTI_PPI_TEST
WHERE billing_cycle_id = 201007
and latn_id = 10001

1) First, we lock a distinct TEST."pseudo table" for read on a
RowHash to prevent global deadlock for TEST.MULTI_PPI_TEST.
2) Next, we lock TEST.MULTI_PPI_TEST for read.
3) We do an all-AMPs RETRIEVE step from a single partition of
TEST.MULTI_PPI_TEST with a condition of (
"TEST.MULTI_PPI_TEST.BILLING_CYCLE_ID = 201007,
TEST.MULTI_PPI_TEST.LATN_ID = 10001") with a residual condition of
("(TEST.MULTI_PPI_TEST.LATN_ID = 10001) AND
(TEST.MULTI_PPI_TEST.BILLING_CYCLE_ID = 201007)") into Spool 1
(group_amps), which is built locally on the AMPs. The size of
Spool 1 is estimated with no confidence to be 1 row (49 bytes).
The estimated time for this step is 0.03 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.03 seconds.
两个分区均用到了，则扫描的partition数目应该为1*1，没有问题。