Update 9/29/2016

IBI confirmed that there was a bug and it appears that they have fixed the issue. The latest Gen is now 913 I believe. If you are using Clustered Master Joins and DBA security in 8104/8105 (Unknown to me when the issue was introduced, but sometime after 8008), then I would suggest upgrading to the latest 8105 gen.


Update 9/13/2016

I've heard an update back from IBI on this issue. They have confirmed that this is likely a bug as they continue to research the issue.

For anyone using DBA Security and/or Clustered Master joins on 8009M or above. I highly recommend running SQL traces on your queries that rely on the adapter. I do not know what version this was introduced in but I can confirm that it exists in gen 800 through the 8.2 release currently in beta testing. For me this is an extremely large issue and I am rolling back to 8008M. If you are using either of those (DBA Security/Clustered Master Joins), I believe that you are at high risk for erroneous results. There is no known workaround at this time.

Thanks!
Eric

***************************************
***************************************

I'm currently working through upgrading to WF8.1.05 from WF8.0.08. In 8.1.05 there has been code tightening which has forced me to rebuild the way I handle DBA security. I did that and ran a simple request and noticed it take much longer then it did under WF8.0.08. I ran a SQL trace and I found about 4 tables from my clustered master join, that for this request would not normally be included because the master files weren't invoked.
With this query:

TABLE FILE STUDENTS
SUM BANNER_ID
BY BANNER_ID
WHERE RECORDLIMIT EQ 10
END  

I am getting the below SQL query:

 SELECT
T4."BANNER_ID",
MAX(T4."BANNER_ID")
FROM
( ( ( ( ( ( ( IDR.TERM T1
INNER JOIN BI_SUMMARY.V_TERM_STUD_CURRICULUM T2
ON (T2."TENANT_ID" = T1."TENANT_ID") AND
(T1."START_DT" >= T2."START_DT") AND
(T1."START_DT" <= T2."END_DT") AND
(T2."LRNR_MOD" = 'LEARNER') AND
((T2."ACTIVE_IND" = 1) OR (-1 = 1)) )
INNER JOIN IDR.STUD_CURRICULUM T3
ON (T3."STUD_CURRICULUM_KEY" = T2."STUD_CURRICULUM_KEY") )
INNER JOIN IDR.PROFILE T4
ON (T4."PROFILE_KEY" = T3."PROFILE_KEY") )
LEFT OUTER JOIN IDR.PF_PROFILE_ALT_IDENTITY T12
ON T12."PROFILE_KEY" = T4."PROFILE_KEY" )
LEFT OUTER JOIN IDR.PF_DISABILITY T35
ON T35."PROFILE_KEY" = T4."PROFILE_KEY" )
LEFT OUTER JOIN IDR.RF_MASTER_CODE T36
ON T36."RF_MASTER_CODE_KEY" = T35."RF_MSTR_MEDI_COND_KEY" )
LEFT OUTER JOIN IDR.RF_MASTER_CODE T38
ON T38."RF_MASTER_CODE_KEY" = T35."RF_MSTR_DISA_TYP_KEY" )
WHERE
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T2."TENANT_ID" IN(-9, 8, 9)) AND
(T3."TENANT_ID" IN(-9, 8, 9)) AND
(T4."TENANT_ID" IN(-9, 8, 9))
GROUP BY
T4."BANNER_ID"
ORDER BY
T4."BANNER_ID"; 

Tables T12,T35,T36, and T38 are all child tables of T4. Since I am only invoking fields from T4, I would expect to only see joins from the top 4 segments (joins). Just the tables that are needed to create the query. Does anyone have any thoughts as to why I would be getting the extra 4 tables? This clustered join has some 160+ joins in it. So I'm confused why it would have decided it needed 4 more joins, but not the rest.

This message has been edited. Last edited by: eric.woerle,

Maybe because the translator isn't certain where this condition will be resolved?

OR (-1 = 1)

quote:

WHERE
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T2."TENANT_ID" IN(-9, 8, 9)) AND
(T3."TENANT_ID" IN(-9, 8, 9)) AND
(T4."TENANT_ID" IN(-9, 8, 9))

I suspect this bit of generated SQL. It seems that something in your master causes these conditions to NOT end up in the JOIN conditions, but instead end up as separate WHERE conditions. The optimizer sees references to all 4 tables in your WHERE clause and fails to see what those expressions do.

If the reason for those separate WHERE conditions originates from how the master is defined, then I can understand that the adapter is having difficulty detecting that all the information is in fact from a single table in that JOIN.

And apparently the query planner of your RDBMS is running into the same optimization issue, or there wouldn't have been much of a noticeable performance difference (in fact, it probably would have been faster).

Without knowing what's in your master exactly, it's hard to tell more. If you need more eyes on that query, post it here.

Dan / Wep,

The repetition of the T1."TENANT_ID" IN (-9,8,9)) comes from my DBA file. I have the same restriction in my first table, as I do in the clustered master join. Its a change I had to make because of "Code tightening" between 8008 and 8105.

In 8008 the adapter wasn't able to differentiate between which table the Tenant ID field belonged to. That has been fixed.

In the case of this example, what I have posted is with DBAJOIN=OFF. When I set DBAJOIN to on (which I will need to do once I figure out why I'm getting the extra fields) I get a bunch more fields. That is where I saw the loss of efficiency. I had 20 or more Left Outer joins added to this same query. which btw, DBAJOIN shouldn't be changing the tables needed for a query... but hey its happening. I know that oracle is working correctly because it caches the query, and when I run it a second time I'm getting my results back faster. After however much time Oracle holds that cache for, I have to run the query twice again for the results to come back in a reasonable amount of time.

What I'm saying is that the optimizer might understand your goals better if you'd change:

WHERE
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T2."TENANT_ID" IN(-9, 8, 9)) AND
(T3."TENANT_ID" IN(-9, 8, 9)) AND
(T4."TENANT_ID" IN(-9, 8, 9))

into something like:

WHERE
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T2."TENANT_ID" EQ T1."TENANT_ID") AND
(T3."TENANT_ID" EQ T1."TENANT_ID" ) AND
(T4."TENANT_ID" EQ T1."TENANT_ID" )

I realize this is not semantically the same condition, as the first allows for different values of TENANT_ID in the various tables, as long as they're each in (-9,8,9); That's 3^4 = 81 permutations total.

The version I suggest forces them to match the same id for the joined tables, which is only 3 permutations total, but I get the feeling that's what you actually intended.

BTW, I just noticed you have T1.TENANT_ID IN (-9, 8, 9) twice.

Wep,

The problem with what you're suggesting is that all of those conditions are "injected" through row level security. We are set up as SaaS and I need to have that as a part of every master file. Since it comes from DBA security, I don't have the luxury of accounting for it differently in every report.

I've been working on breaking things down more simply over the last day and I have been able to isolate one of the tables that is causing me a problem. Although for the life of me, I can't find anything wrong with it. I'll try to post some more information once I get an opportunity to break it down a little bit more.

Here are the master files that I am using in my clustered master join. I've cut a lot out. Taken things out put them back in etc... I can't find a reason. At the bottom of this is the SQL output. Table (RF_CITIZEN_TYP) is added to the join, but its not a part of the parent path. I'm requesting Banner ID which sits in the Profile Table. In this case RF_CITIZEN_TYP is a child table of Profile. But its the only child table that is being called. I kind find any attributes in my masterfile that sticks out as different. The joins aren't created any differently in the clustered master join. I'm very befuddled. And the other crazy part is, that all of the tables that are added to the query which aren't needed do not have the row level security included that they should.


*** Clustered Master Join

FILENAME=test_security, MFD_PROFILE=apolloglobal/apgglb9805,
 REMARKS='Term Based view of Student Information.  You can find such information about a student related to Curriculum, Cohort, Profile, Attributes, Grades, Attendance etc.', $
 VARIABLE NAME=&&VARACTIND,  PROMPT='Active Ind (1/0)', USAGE=I9, DEFAULT='1', $ This is why you see the (-1 = 1 ).  Its a toggle to allow inactive records for audit purposes
  SEGMENT=TERM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/TERM, CRINCLUDE=ALL, $
  SEGMENT=V_TERM_STUD_CURRICULUM, SEGTYPE=KU, PARENT=TERM, CRFILE=APOLLOGLOBAL/IDR/SIS/VIEWS/V_TERM_STUD_CURRICULUM, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=TERM.TENANT_ID EQ V_TERM_STUD_CURRICULUM.TENANT_ID
 AND TERM.START_DT GE V_TERM_STUD_CURRICULUM.START_DT
 AND TERM.START_DT LE V_TERM_STUD_CURRICULUM.END_DT
 AND LRNR_MOD EQ 'LEARNER'
 AND (V_TERM_STUD_CURRICULUM.ACTIVE_IND EQ 1) OR (ACTIND EQ &&VARACTIND)
 AND V_TERM_STUD_CURRICULUM.PROFILE_KEY EQ IDR_BASE_TABLES_PROFILE.PROFILE_KEY;, $
    DEFINE ACTIND/I4  WITH V_TERM_STUD_CURRICULUM.PROFILE_KEY=-1; $
  SEGMENT=IDR_BASE_TABLES_STUD_CURRICULUM, SEGTYPE=KU, PARENT=V_TERM_STUD_CURRICULUM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/STUD_CURRICULUM, CRSEGMENT=STUD_CURRICULUM, CRINCLUDE=ALL,
    JOIN_WHERE=V_TERM_STUD_CURRICULUM.STUD_CURRICULUM_KEY EQ IDR_BASE_TABLES_STUD_CURRICULUM.STUD_CURRICULUM_KEY;, $
  SEGMENT=IDR_BASE_TABLES_PROFILE, SEGTYPE=KU, PARENT=V_TERM_STUD_CURRICULUM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PROFILE, CRSEGMENT=PROFILE, CRINCLUDE=ALL,
    JOIN_WHERE=V_TERM_STUD_CURRICULUM.PROFILE_KEY EQ IDR_BASE_TABLES_PROFILE.PROFILE_KEY;,
    DESCRIPTION='IDR_BASE_TABLES_PROFILE', $
    DEFINE STUDENT_AGE/I4=DATEDIF(BIRTH_DT, '&DATEYYMD', 'Y'); $
  SEGMENT=IDR_BASE_TABLES_PF_FRGN_PERSON, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PF_FRGN_PERSON, CRSEGMENT=PF_FRGN_PERSON, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ IDR_BASE_TABLES_PF_FRGN_PERSON.PROFILE_KEY;, $
  SEGMENT=IDR_BASE_TABLES_PF_NAME, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PF_NAME, CRSEGMENT=PF_NAME, CRINCLUDE=ALL, CRJOINTYPE=INNER,
    JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ IDR_BASE_TABLES_PF_NAME.PROFILE_KEY AND IDR_BASE_TABLES_PF_NAME.PRIMARY_IND EQ 1 AND RF_MSTR_NM_TYP_KEY EQ -1;, $
    DEFINE STUDENT_NAME/A511=FIRST_NM | (' ' | LAST_NM); $
  SEGMENT=IDR_BASE_TABLES_PF_DISABILITY, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PF_DISABILITY, CRSEGMENT=PF_DISABILITY, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ IDR_BASE_TABLES_PF_DISABILITY.PROFILE_KEY;, $
    DEFINE DISAB_IND/A20=IF IDR_BASE_TABLES_PF_DISABILITY.PROFILE_KEY IS MISSING THEN 'NULL' ELSE 'Y'; $
  SEGMENT=PF_MEDICAL_INFO, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PF_MEDICAL_INFO, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ PF_MEDICAL_INFO.PROFILE_KEY;, $
    DEFINE MEDICAL_IND/A20=IF PF_MEDICAL_INFO.PROFILE_KEY IS MISSING THEN 'NULL' ELSE 'Y'; $
  SEGMENT=IDR_BASE_TABLES_STUD_DEGREE_MAJ_MIN, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/STUD_DEGREE_MAJ_MIN, CRSEGMENT=STUD_DEGREE_MAJ_MIN, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=IDR_BASE_TABLES_PROFILE.PROFILE_KEY EQ IDR_BASE_TABLES_STUD_DEGREE_MAJ_MIN.PROFILE_KEY;, $
  SEGMENT=IDR_BASE_TABLES_STUD_DEG_RF_ACAD_LVL, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_STUD_DEGREE_MAJ_MIN, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/RF_ACAD_LVL, CRSEGMENT=RF_ACAD_LVL, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=IDR_BASE_TABLES_STUD_DEGREE_MAJ_MIN.RF_ACAD_LVL_KEY EQ IDR_BASE_TABLES_STUD_DEG_RF_ACAD_LVL.RF_ACAD_LVL_KEY;,
    DESCRIPTION='STUD_DEG_RF_ACAD_LVL', $
  SEGMENT=RF_ETHNIC_TYP, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/RF_ETHNIC_TYP, CRINCLUDE=ALL,
    JOIN_WHERE=IDR_BASE_TABLES_PROFILE.RF_ETHNIC_TYP_KEY EQ RF_ETHNIC_TYP.RF_ETHNIC_TYP_KEY;, $
  SEGMENT=RF_CITIZEN_TYP, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROFILE, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/RF_CITIZEN_TYP, CRINCLUDE=ALL,
    JOIN_WHERE=IDR_BASE_TABLES_PROFILE.RF_CITIZEN_TYP_KEY EQ RF_CITIZEN_TYP.RF_CITIZEN_TYP_KEY;, $
  SEGMENT=IDR_BASE_TABLES_PROGRAM, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_STUD_CURRICULUM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PROGRAM, CRSEGMENT=PROGRAM, CRINCLUDE=ALL,
    JOIN_WHERE=IDR_BASE_TABLES_STUD_CURRICULUM.PROGRAM_KEY EQ IDR_BASE_TABLES_PROGRAM.PROGRAM_KEY;,
    DESCRIPTION='PROGRAM', $
  SEGMENT=PROGRAM_CUST_ULA, SEGTYPE=KU, PARENT=IDR_BASE_TABLES_PROGRAM, CRFILE=APOLLOGLOBAL/IDR/SIS/BASE_TABLES/PROGRAM_CUST_ULA, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=IDR_BASE_TABLES_PROGRAM.PROGRAM_KEY EQ PROGRAM_CUST_ULA.PROGRAM_KEY;, $
  FOLDER=TEST_SECURITY,
    DESCRIPTION='Students', $
  FOLDER=PROFILE, PARENT=TEST_SECURITY, $
    FIELDNAME=BANNER_ID, ALIAS=BANNER_ID, BELONGS_TO_SEGMENT=IDR_BASE_TABLES_PROFILE,
      TITLE='Banner Id', DESCRIPTION='Student id Banner',
      TITLE_EN='Banner ID',
      TITLE_UK='Banner Id',
      TITLE_ES='ID Banner',
      DESC_EN='Student ID Banner',
      DESC_UK='Student id Banner',
      DESC_ES='Identificador del estudiante en Banner', $
    FIELDNAME=STUDENT_NAME, ALIAS=FIRST_NM_LAST_NM_VRTL, BELONGS_TO_SEGMENT=IDR_BASE_TABLES_PF_NAME,
      TITLE='Student Name', DESCRIPTION='Student Name',
      TITLE_EN='Student Name',
      TITLE_UK='Student Name',
      TITLE_ES='Nombre del estudiante',
      DESC_EN='Student Name',
      DESC_UK='Student Name',
      DESC_ES='Nombre del estudiante', $
  FOLDER=CURRICULUM, PARENT=TEST_SECURITY, $
    FIELDNAME=SOR_MODIFIED_TS, ALIAS=SOR_MODIFIED_TS, BELONGS_TO_SEGMENT=IDR_BASE_TABLES_STUD_CURRICULUM,
      TITLE='Activity Date', DESCRIPTION='Program (Curriculum) Student Activity Date',
      TITLE_EN='Activity Date',
      TITLE_UK='Activity Date',
      TITLE_ES='Fecha de actividad',
      DESC_EN='Program (Curriculum) Student Activity Date',
      DESC_UK='Program (Curriculum) Student Activity Date',
      DESC_ES='Programa (Curriculo) fecha de actividad del estudiante', $
END
DBA=xxxxx, DBAFILE=aaaaa, $
  

Term .acx / .mas

/////.acx
 SEGNAME=TERM, TABLENAME=IDR.TERM, CONNECTION=IDR, 
   KEYS=1, $ 

/////.mas
FILENAME=term, SUFFIX=SQLORA  , MFD_PROFILE=apolloglobal/apgglb9805,$
  SEGMENT=TERM, SEGTYPE=S0, $
    FIELDNAME=TERM_KEY, ALIAS=TERM_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=TERM_CD, ALIAS=TERM_CD, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=TERM_DESC, ALIAS=TERM_DESC, USAGE=A255, ACTUAL=A255,
      MISSING=ON, $
    FIELDNAME=START_DT, ALIAS=START_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=END_DT, ALIAS=END_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=FA_PROC_YR, ALIAS=FA_PROC_YR, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=FA_TERM, ALIAS=FA_TERM, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=FA_BEG_PERIOD, ALIAS=FA_BEG_PERIOD, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=FA_END_PERIOD, ALIAS=FA_END_PERIOD, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=HOUSING_START_DT, ALIAS=HOUSING_START_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=HOUSING_END_DT, ALIAS=HOUSING_END_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=SUMMER_IND, ALIAS=SUMMER_IND, USAGE=P2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=TENANT_ID, ALIAS=TENANT_ID, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=REF_MSTR_ACAD_YR_KEY, ALIAS=REF_MSTR_ACAD_YR_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=REF_MSTR_TRM_TYP_KEY, ALIAS=REF_MSTR_TRM_TYP_KEY, USAGE=D10, ACTUAL=D8, $
END
DBA=xxxxxx, DBAFILE=bbbbbb, $
 

all of the acx files are basically the same so I'm not going to add all of them.
v_term_stud_curriculum .mas

 FILENAME=v_term_stud_curriculum, SUFFIX=SQLORA  , MFD_PROFILE=apolloglobal/apgglb9805, $
  SEGMENT=V_TERM_STUD_CURRICULUM, SEGTYPE=S0, $
    FIELDNAME=PROFILE_KEY, ALIAS=PROFILE_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=STUD_CURRICULUM_KEY, ALIAS=STUD_CURRICULUM_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=PREV_STUD_CURRICULUM_KEY, ALIAS=PREV_STUD_CURRICULUM_KEY, USAGE=D10, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=LRNR_MOD, ALIAS=LRNR_MOD, USAGE=A30V, ACTUAL=A30V,
      MISSING=ON, $
    FIELDNAME=STATUS, ALIAS=STATUS, USAGE=A30V, ACTUAL=A30V,
      MISSING=ON, $
    FIELDNAME=PREV_STATUS, ALIAS=PREV_STATUS, USAGE=A30V, ACTUAL=A30V,
      MISSING=ON, $
    FIELDNAME=START_DT, ALIAS=START_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=END_DT, ALIAS=END_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=ACTIVE_IND, ALIAS=ACTIVE_IND, USAGE=I2, ACTUAL=I8,
      MISSING=ON, $
    FIELDNAME=PRIORITY_NBR, ALIAS=PRIORITY_NBR, USAGE=D10, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=LAST_UPD_ACTIVITY_DT, ALIAS=LAST_UPD_ACTIVITY_DT, USAGE=HYYMDS, ACTUAL=HYYMDS,
      MISSING=ON, $
    FIELDNAME=RF_MSTR_CURR_ACT_STS_KEY, ALIAS=RF_MSTR_CURR_ACT_STS_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=RF_MSTR_PREV_ACT_STS_KEY, ALIAS=RF_MSTR_PREV_ACT_STS_KEY, USAGE=D10, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=PROGRAM_KEY, ALIAS=PROGRAM_KEY, USAGE=D10, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=PREV_PROGRAM_KEY, ALIAS=PREV_PROGRAM_KEY, USAGE=D10, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=PREV_PROGRAM_CD, ALIAS=PREV_PROGRAM_CD, USAGE=A64V, ACTUAL=A64V,
      MISSING=ON, $
    FIELDNAME=PREV_PROGRAM_DESC, ALIAS=PREV_PROGRAM_DESC, USAGE=A255V, ACTUAL=A255V,
      MISSING=ON, $
    FIELDNAME=TENANT_ID, ALIAS=TENANT_ID, USAGE=D10, ACTUAL=D8, $
END
DBA=xxxxxxx, DBAFILE=eeeeeeeeee, $ 

profile.mas

FILENAME=profile, SUFFIX=SQLORA  , MFD_PROFILE=apolloglobal/apgglb9805, $
  SEGMENT=PROFILE, SEGTYPE=S0, $
    FIELDNAME=PROFILE_KEY, ALIAS=PROFILE_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=GENDER_CD, ALIAS=GENDER_CD, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=HAIR_CD, ALIAS=HAIR_CD, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=EYE_CD, ALIAS=EYE_CD, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=BIRTH_DT, ALIAS=BIRTH_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=BIRTH_CITY_NM, ALIAS=BIRTH_CITY_NM, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=DRVR_LIC_NBR, ALIAS=DRVR_LIC_NBR, USAGE=A64, ACTUAL=A64,
      MISSING=ON, $
    FIELDNAME=DRVR_LIC_DT, ALIAS=DRVR_LIC_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=DECEASED_DT, ALIAS=DECEASED_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=DECEASED_IND, ALIAS=DECEASED_IND, USAGE=P2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=HEIGHT, ALIAS=HEIGHT, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=WEIGHT, ALIAS=WEIGHT, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=SPEC_DISABLE_VET_IND, ALIAS=SPEC_DISABLE_VET_IND, USAGE=P2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=INCARCERATION_IND, ALIAS=INCARCERATION_IND, USAGE=P2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=INTL_TAX_NBR, ALIAS=INTL_TAX_NBR, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=ACTV_DUTY_SEPR_DT, ALIAS=ACTV_DUTY_SEPR_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=RACE_ETHN_CONF_IND, ALIAS=RACE_ETHN_CONF_IND, USAGE=P2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=RACE_ETHN_CONF_DT, ALIAS=RACE_ETHN_CONF_DT, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=AF_SER_MEDAL_IND, ALIAS=AF_SER_MEDAL_IND, USAGE=P2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=CONFIDENTIAL_IND, ALIAS=CONFIDENTIAL_IND, USAGE=P2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=VET_NBR, ALIAS=VET_NBR, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=SSN, ALIAS=SSN, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=RELT_PRIORITY_NBR, ALIAS=RELT_PRIORITY_NBR, USAGE=D20.2, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=TENANT_ID, ALIAS=TENANT_ID, USAGE=D10, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=SOR_ID, ALIAS=SOR_ID, USAGE=A64, ACTUAL=A64,
      MISSING=ON, $
    FIELDNAME=SOR_FILE_ID, ALIAS=SOR_FILE_ID, USAGE=D10, ACTUAL=D8,
      MISSING=ON, $
    FIELDNAME=BANNER_ID, ALIAS=BANNER_ID, USAGE=A64, ACTUAL=A64,
      MISSING=ON, $
    FIELDNAME=RF_MSTR_MRTL_KEY, ALIAS=RF_MSTR_MRTL_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=RF_MSTR_BIRTH_STAT_KEY, ALIAS=RF_MSTR_BIRTH_STAT_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=RF_MSTR_DRVR_STAT_KEY, ALIAS=RF_MSTR_DRVR_STAT_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=RF_MSTR_RELG_KEY, ALIAS=RF_MSTR_RELG_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=RF_DRVR_LIC_CNTRY_KEY, ALIAS=RF_DRVR_LIC_CNTRY_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=RF_ETHNIC_TYP_KEY, ALIAS=RF_ETHNIC_TYP_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=RF_CITIZEN_TYP_KEY, ALIAS=RF_CITIZEN_TYP_KEY, USAGE=D10, ACTUAL=D8, $
END
DBA=xxxxxx, DBAFILE=ccccccccc, $
  

rf_citizen)typ.mas

 FILENAME=rf_citizen_typ, SUFFIX=SQLORA  , MFD_PROFILE=apolloglobal/apgglb9805,$
  SEGMENT=RF_CITIZEN_TYP, SEGTYPE=S0, $
    FIELDNAME=RF_CITIZEN_TYP_KEY, ALIAS=RF_CITIZEN_TYP_KEY, USAGE=D10, ACTUAL=D8, $
    FIELDNAME=CITIZEN_TYPE_CD, ALIAS=CITIZEN_TYPE_CD, USAGE=A64, ACTUAL=A64,
      MISSING=ON, $
    FIELDNAME=CITIZEN_TYPE_DESC, ALIAS=CITIZEN_TYPE_DESC, USAGE=A255, ACTUAL=A255,
      MISSING=ON, $
    FIELDNAME=EDI_EQUIV_CD, ALIAS=EDI_EQUIV_CD, USAGE=A30, ACTUAL=A30,
      MISSING=ON, $
    FIELDNAME=CITIZEN_IND, ALIAS=CITIZEN_IND, USAGE=P2, ACTUAL=P8,
      MISSING=ON, $
    FIELDNAME=TENANT_ID, ALIAS=TENANT_ID, USAGE=D10, ACTUAL=D8, $
END
DBA=xxxxxx, DBAFILE=ddddddddddd, $
  

procedure

-*SET DBAJOIN = ON
 SET TRACEOFF=ALL
 SET TRACEON=SQLDI
 SET TRACEON=SQLCALL
 SET TRACEON=SQLAGGR//CLIENT
 SET TRACEON=STMTRACE//CLIENT
 SET TRACEON=STMTRACE/2/CLIENT
 SET TRACEUSER=ON
 SET TRACESTAMP=OFF

TABLE FILE TEST_SECURITY
SUM BANNER_ID
BY BANNER_ID
WHERE RECORDLIMIT EQ 5
END
  

SQL Output

 SELECT
T6."BANNER_ID",
MAX(T6."BANNER_ID")
FROM
IDR.TERM T1,
BI_SUMMARY.V_TERM_STUD_CURRICULUM T2,
IDR.PROFILE T6,
IDR.RF_CITIZEN_TYP T8
WHERE
(((T1."TENANT_ID" = T2."TENANT_ID") AND (T1."START_DT" >=
T2."START_DT") AND (T1."START_DT" <= T2."END_DT") AND
(T2."LRNR_MOD" = 'LEARNER') AND (T2."ACTIVE_IND" = 1)) OR ((-1
= 1) AND (T2."PROFILE_KEY" = T6."PROFILE_KEY"))) AND
(T6."PROFILE_KEY" = T2."PROFILE_KEY") AND
(T8."RF_CITIZEN_TYP_KEY" = T6."RF_CITIZEN_TYP_KEY") AND
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T1."TENANT_ID" IN(-9, 8, 9)) AND
(T2."TENANT_ID" IN(-9, 8, 9)) AND
(T6."TENANT_ID" IN(-9, 8, 9))
GROUP BY
T6."BANNER_ID"
ORDER BY
T6."BANNER_ID"; 

All of the dba security is basically this. its generated at run time according to a procedure I created in Oracle. Each master file gets its own DBA file. This is where the "WHERE Tx.TENANT_ID = ..." comes from. DBA Security injects row level security as where statements. When you turn on DBAJOIN, then it injects it as part of the join clause, but only for left outer joins. Inner Joins are still a part of the where clause.

DBA Security

  
FILENAME=apgglb9805, SUFFIX=FIX     , $
 SEGMENT=DUMMY, SEGTYPE=S0, $
   FIELDNAME=DUMMY, USAGE=A1, ACTUAL=A1, $
END
USER={userpassword], ACCESS=R, $
RESTRICT=VALUE_WHERE, NAME= [segment name], VALUE=TENANT_ID IN (-9,8,9); ,$  

Assuming those *_KEY fields are your primary keys, the joins seem to be making use of the correct key. So far so good.

However, the row-level security apparently is on tenant_id instead of the PK. Do you have indexes on that field in your various child-tables? Or could you perhaps change row-level security to use the PK somehow?

Lastly, in the PROFILE table, TENANT_ID is - at least in the MFD - marked as NULLABLE. That's probably why we don't see a JOIN-condition on (T6.TENANT_ID = T1.TENANT_ID).
I think that's in error because:

• Judging from the fact that there is a WHERE-clause expression that limits TENANT_ID's in PROFILE to a specific IN-list, this is an INNER join definition
  
• The fact that 3 different TENANT_ID's from (T1 x T2) are matched to 3 different TENANT_ID's from T6 results in 3^2 = 9 permutations (instead of 3^3 = 27; I missed the JOIN-condition on (T1.TENANT_ID = T2.TENANT_ID) in your previous post) while those TENANT_ID's most likely should match between the different tables in the join.

As it is, it's hard for any query planner to see what kind of correlation exists between your PK's and the row-level security values on TENANT_ID.

I think there is a bug here in the way that query gets planned.

P.S. I notice you left out the join with STUD_CURRICULUM this time, which is probably just as well for understanding the problem.

Ya, I left out the STUD_CURRICULUM table. Mainly because I was toy'ing with the idea of using Join as Root with Profile, and when I reconnected it, I just connected it one table higher. Results wise it won't matter.

Yes the row level security is on tenant id. This table, for whatever reason doesn't actually have a primary key... I don't know why we did that... but either way, the Tenant ID is the Primary Key if we had defined it on the table. That table has less than 15 records in it.... Getting through it isn't an issue. There may be indexes on the tenant ID for all of our other tables. Not sure if we did that. But from an oracle stand point, the queries when constructed properly run fine. The tenant id is not where my efficiency issues lie. And if that does pop up in the future, it's something we will be able to take care of quickly.

You don't see a join condition on T6.TENANT_ID = T1.TENANT_ID because I didn't put it into the Clustered Master Join. (T6."PROFILE_KEY" = T2."PROFILE_KEY") is sufficient enough to ensure that only records from 1 tenant ID is selected. Also, the DBA security should be enforcing the records to the appropriate Tenant IDs, which also makes it unnecessary.

In this case I didn't use any fields from any left outer joins, but if I had you would find that the IN clause is not specifically an inner join definition. Oracle excepts that and WF translates it that way. I don't have any issues with this.

quote:

The fact that 3 different TENANT_ID's from (T1 x T2) are matched to 3 different TENANT_ID's from T6 results in 3^2 = 9 permutations (instead of 3^3 = 27; I missed the JOIN-condition on (T1.TENANT_ID = T2.TENANT_ID) in your previous post) while those TENANT_ID's most likely should match between the different tables in the join.

I understand your point here, but with DBA security, there is no way to achieve this. T1 won't always be term, and as such I can't just say term.tenant_id = [insert table here].tenant_id for my DBA security. Thats why it will always be Tx."TENANT_ID" in (n,n,n). The security needs to work whether I am accessing just that one table or joining it to multiple tables. You will never know which one your joining to. I have a process that builds that DBA file dynamically based on a users group, so where as my current security settings gives me access to tenant id's 9,8,-9, someone else will have 4,-9 or 11,-9. But whatever the values for tenant_id are, the values will be the same for all tables, so even though in theory I might have 27 permutations, there will always only be 3.

The more and more I look at this though, the more I think this is a bug. Maybe its something that happened when they did the code tightening on the "NAME" attribute in the DBA security. Thanks for being a second pair of eyes on this. Since the SQL generated properly in 7704m and 8008m, I think I need to open a case and see where that leads me.

received update from IBI on this issue.