STJI is not using....

Database
l_k
Enthusiast

STJI is not using....

Hi,

The below query INSERT SEL is running more than 1 hour daily..so i introduced Single table Join Index when we create for the column Party_id for the table Dp_TEDW.T0502_EVENT_PARTY... due to no index for that column but PE is not using STJI after creation.......

Query is :

INSERT INTO Dp_tcmdm.M0502_EVENT_PARTY
SEL
Event_Id
,B1000.SVC_Party_ID
,Contact_Comment_Txt
,Party_Role_Cd
,Party_Event_Start_Dt
,Party_Event_End_Dt
,Ext_Identification_Type_Cd
,Your_Reference
,Our_Reference
FROM
Dp_bcmdm.V0502_EVENT_PARTY T0502
INNER JOIN
Dp_wcmdm.B1000_SVC_MAP B1000
ON
T0502.Party_ID = B1000.Party_ID

'B' Layer view is :
----------------------
SEL
Event_Id
,B1000.SVC_Party_ID
,Contact_Comment_Txt
,Party_Role_Cd
,Party_Event_Start_Dt
,Party_Event_End_Dt
,Ext_Identification_Type_Cd
,Your_Reference
,Our_Reference
FROM
Dp_vedw.V0502_EVENT_PARTY T0502
INNER JOIN
Dp_cmdm.data_sources_filter dsf
ON
coalesce(T0502.data_source_cd,'')=coalesce(dsf.data_source_cd,'')
where end_dt is null;

DDL is :
---------
table 1:

CREATE SET TABLE DP_TEDW.T0502_EVENT_PARTY ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Event_Id DECIMAL(18,0),
Party_Id INTEGER,
Contact_Comment_Txt VARCHAR(250) CHARACTER SET LATIN NOT CASESPECIFIC,
Party_Role_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
Party_Event_Start_Dt DATE FORMAT 'YY/MM/DD',
Party_Event_End_Dt DATE FORMAT 'YY/MM/DD',
Ext_Identification_Type_Cd CHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
Your_Reference VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
Our_Reference VARCHAR(1000) CHARACTER SET LATIN NOT CASESPECIFIC,
END_DT DATE FORMAT 'YYYY-MM'DD')
PRIMARY INDEX ( Event_Id );

Table 2:
----------
CREATE TABLE Dp_wcmdm.SB1000_SVC_MAP
(
Party_ID INT
,SVC_Party_ID INT
) UNIQUE PRIMARY INDEX( Party_ID)
;

Above VEDW is a view layer,TEDW is a target layer .....

I don't know why the above insert..select query is not using STJI and also Data_source_cd is not an index column in the 'B' layer also?

I am expecting your valuable suggestion for the above STJI and 'B' layer review also....

Thanks for your help.

5 REPLIES
l_k
Enthusiast

Re: STJI is not using....

Anyone can help me to resolve this problem?
Senior Apprentice

Re: STJI is not using....

How does the JI look like?
There's no where-condition in your query, so why do you think the optimizer should use it?

Dieter
l_k
Enthusiast

Re: STJI is not using....

Join Index is :

Create Join Index Event_Partyid
as
Select party_id
from
Dp_Tedw.T0502_Event_Party
Primary Index(Party_Id);

Party_id is not an Index column,so i created STJI for Party_id in the table
Dp_Tedw.T0502_EVENT_PARTY to join party_id=Party_id...
There is no where clause condition because of Delta load....

How to improve the performance of the above INSERT..SEL query? Any other option is there if STJI is not useful?

Thanks.
Enthusiast

Re: STJI is not using....

If I'm reading your SQL correctly, the join index cannot be used to satisfy the query. You're referencing fields from the base table that do not appear in the join index. In this case, the optimizer can't use the join index.

One possible solution would be to re-write the join index to include all of the fields necessary to satisfy the query. Another option could be to inlcude the ROWID keyword which will allow the optimizer to join using the STJI and look back into the base table for the data values it needs.

And of course, make sure you have good statistics collected.
Senior Apprentice

Re: STJI is not using....

There's no WHERE-condition, so even if you included more columns/ROWID in the JI, the optimizer will no use it unless it's fully covering the query.
Do you actually need that join using COALESCE?

Did you check (using DBQL) which step is the slowest?
Might be the INSERT because of duplicate row checks, what's the number of rows per Event_Id?

Dieter