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....
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.