Teradata Multi PPI (多级分区表)

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,没有问题。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16723161/viewspace-1037071/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/16723161/viewspace-1037071/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值