Need clarification in PI.....

General
Enthusiast

Need clarification in PI.....

I am having a landing table and a target table with primary index on same column at landing table and target table.
When I am loading target table from landing table with Stored Procedure with left outer join on the PI column.

My intention is it should use the Hash join to load the data.
But it is going for the full scan of the table.
Can anybody help me.
Thnx in advance.....

Lnd table st:

CREATE MULTISET TABLE SA_TEST.LND_DEL_VOICE_DATA ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
DTSTRTCHRG DATE FORMAT 'YY/MM/DD',
PLMNID VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
SERVICE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'VOICE',
PLMNID_OPER VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CIRCLE_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
SUBSCRIPTIONTYPE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CDRTYPE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSIMSI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSIMEI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYNO VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
TIMEOFCHRG TIME(0),
CHARGEDURN INTEGER,
CELLIDFIRSTCELL VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
CELLIDLASTCELL VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
SMSCENTERNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
MSCID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
INCOMINGROUTE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
OUTGOINGROUTE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
FILE_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
JOB_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
LOAD_DATE TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0),
RECSEQUENCENUMBER VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYIMSI VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYIMEI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
REDIRECTNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
MOBSTNROAMNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
ORIGLOCNNO VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
TRMLOCNNO VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
CUGINTERLOCKCODE VARCHAR(24) CHARACTER SET LATIN NOT CASESPECIFIC,
CUGINDEX VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CALLCONFERENCEINDI VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
SERVICEINFO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
DIALEDDIGITS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
VISITED_LOCATION VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
PI_COLUMN VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( PI_COLUMN );

Target table st:

CREATE MULTISET TABLE SA_TEST.DEL_VOICE_DATA ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
DTSTRTCHRG DATE FORMAT 'YY/MM/DD',
PLMNID VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
SERVICE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC DEFAULT 'VOICE',
PLMNID_OPER VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CIRCLE_CODE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
SUBSCRIPTIONTYPE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CDRTYPE VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSIMSI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
AIRCELSUBSIMEI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYNO VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
TIMEOFCHRG TIME(0),
CHARGEDURN INTEGER,
CELLIDFIRSTCELL VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
CELLIDLASTCELL VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
SMSCENTERNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
MSCID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
INCOMINGROUTE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
OUTGOINGROUTE VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
FILE_NAME VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
JOB_ID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
LOAD_DATE TIMESTAMP(0) DEFAULT CURRENT_TIMESTAMP(0),
RECSEQUENCENUMBER VARCHAR(40) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYIMSI VARCHAR(15) CHARACTER SET LATIN NOT CASESPECIFIC,
OTHERPRTYIMEI VARCHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC,
REDIRECTNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
MOBSTNROAMNO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
ORIGLOCNNO VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
TRMLOCNNO VARCHAR(5) CHARACTER SET LATIN NOT CASESPECIFIC,
CUGINTERLOCKCODE VARCHAR(24) CHARACTER SET LATIN NOT CASESPECIFIC,
CUGINDEX VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
CALLCONFERENCEINDI VARCHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC,
SERVICEINFO VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
DIALEDDIGITS VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
VISITED_LOCATION VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
PI_COLUMN VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( PI_COLUMN )
PARTITION BY RANGE_N(DTSTRTCHRG BETWEEN DATE '2008-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY );

stored procedure :

INSERT INTO DEL_VOICE_DATA
SEL
A.DTSTRTCHRG
,A.PLMNID
,A.SERVICE
,A.PLMNID_OPER
,A.CIRCLE_CODE
,A.SUBSCRIPTIONTYPE
,A.CDRTYPE
,A.AIRCELSUBSNO
,A.AIRCELSUBSIMSI
,A.AIRCELSUBSIMEI
,A.OTHERPRTYNO
,A.TIMEOFCHRG
,A.CHARGEDURN
,A.CELLIDFIRSTCELL
,A.CELLIDLASTCELL
,A.SMSCENTERNO
,A.MSCID
,A.INCOMINGROUTE
,A.OUTGOINGROUTE
,A.FILE_NAME
,A.JOB_ID
,A.LOAD_DATE
,A.RECSEQUENCENUMBER
,A.OTHERPRTYIMSI
,A.OTHERPRTYIMEI
,A.REDIRECTNO
,A.MOBSTNROAMNO
,A.ORIGLOCNNO
,A.TRMLOCNNO
,A.CUGINTERLOCKCODE
,A.CUGINDEX
,A.CALLCONFERENCEINDI
,A.SERVICEINFO
,A.DIALEDDIGITS
,A.VISITED_LOCATION
,A.PI_COLUMN
,B.PI_COLUMN
FROM LND_DEL_VOICE_DATA A
LEFT OUTER JOIN
DEL_VOICE_DATA B
ON A.PI_COLUMN = B.PI_COLUMN
WHERE B.DTSTRTCHRG BETWEEN :MIN_CURR_LOAD AND :MAX_CURR_LOAD AND
cast(cast((a.dtstrtchrg (format 'YYYY-MM-DD')) as char(10))||' '||cast(a.timeofchrg as char(8)) as timestamp(0))<=:max_prev_load;
7 REPLIES
Teradata Employee

Re: Need clarification in PI.....

Why are you using a left outer join? Do you not want all the rows that match the criteria?

Re: Need clarification in PI.....

Hi,
See if you can define partition on LND_DEL_VOICE_DATA,in same way as DEL_VOICE_DATA also if you can introdue a new column in LND_DEL_VOICE_DATA for expression [cast(cast((a.dtstrtchrg (format 'YYYY-MM-DD')) as char(10))||' '||cast(a.timeofchrg as char(8)) as timestamp(0))]
so that no need to do on fly calculation.Ensure you collect stats on partition column (dtstrtchrg) and column PARTITION along with PI of the both tables.I am sure query performance will improve.
You can put the explain plan also along with no.of rows in each table so we will have clear picture of situation.

regards,
Rupesh
Enthusiast

Re: Need clarification in PI.....

David :: Actually this left outer join i am doing for the duplicate check.
As mentioned above PI_COLUMN is the combination of 10 columns....
So if i am using left outer join and insert in to a temp table,,, i can identify the new record with PI_COLUMN as null and old record with exact value from the main table.... I guess you got it what i am trying to say......

Rupesh :::

As we are using mload on the landing table the combination of two columns in a single is not accepting....
is it possible?

Help

Junior Contributor

Re: Need clarification in PI.....

If this is the actual query you submit, then it will result in an Inner Join (check explain), because there's a where-condition on the inner table, which removes all the NULLed rows:
B.DTSTRTCHRG BETWEEN :MIN_CURR_LOAD AND :MAX_CURR_LOAD

Could you post the Explain so we can check if the plan is actually bad.

Dieter
Enthusiast

Re: Need clarification in PI.....

Query + Plan :-
--------------------

Query :-
-----------
USING
_spVV2 (DATE),
_spVV1 (DATE),
_spVV0 (TIMESTAMP(0))INSERT INTO DEL_VOICE_DATA_TEMP SELECT A.DTSTRTCHRG ,A.PLMNID ,A.SERVICE ,A.PLMNID_OPER ,A.CIRCLE_CODE ,A.SUBSCRIPTIONTYPE ,A.CDRTYPE ,A.AIRCELSUBSNO ,A.AIRCELSUBSIMSI ,A.AIRCELSUBSIMEI ,A.OTHERPRTYNO ,A.TIMEOFCHRG ,A.CHARGEDURN ,A.CELLIDFIRSTCELL ,A.CELLIDLASTCELL ,A.SMSCENTERNO ,A.MSCID ,A.INCOMINGROUTE ,A.OUTGOINGROUTE ,A.FILE_NAME ,A.JOB_ID ,A.LOAD_DATE ,A.RECSEQUENCENUMBER ,A.OTHERPRTYIMSI ,A.OTHERPRTYIMEI ,A.REDIRECTNO ,A.MOBSTNROAMNO ,A.ORIGLOCNNO ,A.TRMLOCNNO ,A.CUGINTERLOCKCODE ,A.CUGINDEX ,A.CALLCONFERENCEINDI ,A.SERVICEINFO ,A.DIALEDDIGITS ,A.VISITED_LOCATION ,A.PI_COLUMN ,B.PI_COLUMN FROM LND_DEL_VOICE_DATA A LEFT OUTER JOIN DEL_VOICE_DATA B ON A.PI_COLUMN = B.PI_COLUMN
WHERE (B.DTSTRTCHRG BETWEEN :_spVV2 AND :_spVV1 ) AND ((CAST((((CAST((a.dtstrtchrg ( FORMAT 'YYYY-MM-DD')) AS CHAR(10) NOT CASESPECIFIC))||' ')||(CAST((a.timeofchrg ) AS CHAR(8) NOT CASESPECIFIC))) AS TIMESTAMP(0)))<= :_spVV0 );

Plan:-
--------
1 0:00.00 0:00.00 0 1 First, lock SA_TEST."pseudo table" for write on a row hash.
2 0:00.00 0:00.00 0 1 Next, we lock SA_TEST."pseudo table" for read on a row hash.
3 0:00.00 0:00.00 0 1 We lock SA_TEST."pseudo table" for read on a row hash.
4 0:00.00 0:00.00 0 62 We lock SA_TEST.DEL_VOICE_DATA_TEMP for write, we lock SA_TEST.LND_DEL_VOICE_DATA for read and we lock SA_TEST.DEL_VOICE_DATA for read.
5 0:52.93 5993999 We do an All-AMPs JOIN step from SA_TEST.DEL_VOICE_DATA accessing 3 partitions, which is joined to table LND_DEL_VOICE_DATA. table DEL_VOICE_DATA and table LND_DEL_VOICE_DATA are joined using a merge join . The result goes into Spool 6153, which is built locally on the AMPs.
6 0:00.00 0 We do a MERGE into table DEL_VOICE_DATA_TEMP from Spool 6153.
7 0:00.00 0 We Spoil the parser's dictionary cache for the table.
8 0:00.00 0 We send out an END TRANSACTION step to all AMPs involved in processing the request.
Enthusiast

Re: Need clarification in PI.....

I think, if you make below changes, join should be direct merge join and optimized. Make sure that you have required STATS collected.

1) Change LND_DEL_VOICE_DATA to PPI table and choose same column DTSTRTCHRG as Partitioning column
2) Chnage JOIN logic as below

FROM LND_DEL_VOICE_DATA A
LEFT OUTER JOIN
DEL_VOICE_DATA B
ON A.PI_COLUMN = B.PI_COLUMN
AND A.DTSTRTCHRG = B.DTSTRTCHRG /* New Condition */
AND B.DTSTRTCHRG BETWEEN :MIN_CURR_LOAD AND :MAX_CURR_LOAD /* Change WHERE clause to ON */
WHERE a.dtstrtchrg <= CAST(:max_prev_load AS DATE) /* For any possible Partion elimination for table A */
AND cast(cast((a.dtstrtchrg (format 'YYYY-MM-DD')) as char(10))||' '||cast(a.timeofchrg as char(8)) as timestamp(0))<=:max_prev_load;

Junior Contributor

Re: Need clarification in PI.....

Why do you insist on a Hash Join?
The plan is as good as possible.
It's a direct merge join using the existing PPI without any preparation.

There's no need to change the PI, but you should move the condition on B.DTSTRTCHRG from WHERE to ON.

Btw, this is not an Explain, it's a cut&paste from PMon :-)

Dieter