Chain /*+MAKEDEP*/ hints or controlling what gets pushed when
bpiepers May 31, 2016 3:17 AMI'm currently trying out the /*+MAKEDEP*/ query hint as described here: teiid: Tech Tip: Teiid SQL Language MAKEDEP Hint Explained and Federated Optimizations - Teiid 9.0 (draft) - Project Documentation Editor. I see some behavior of JDV that I would like to influence for better performance. Basically what I'm trying to do, but don't know if it is possible, is to "chain" /*+MAKEDEP*/ hints. Consider the following example:
In my view model I have the following:
SELECT (fields)
FROM
nz.USER_MACHINE AS S INNER JOIN nz.MACHINE AS M ON M.MACHINE_ID = S.MACHINE_ID INNER JOIN /*+MAKEDEP*/ ora.MACHINE AS PM ON M.field = PM.field INNER JOIN /*+ MAKEDEP */ ora.LARGE_FACT_TABLE AS L ON L.MACHINE_ID = PM.MACHINE_ID INNER JOIN /*+ MAKEDEP*/ ora.MASTER_DATA_TABLE AS I ON L.MASTER_DATA_TABLE_ID=I.ID INNER JOIN nz.MASTER_TABLE AS NM ON NM.field = I.field
Obviously above query is fictitious but a representation of a real situation I have at a customer.
This query gets its data from two separate sources: a Netezza and an Oracle database. Tables that come from Netezza are indicated with the "nz" pre-fix, tables coming from Oracle are prepended with "ora". So in the above example I'm trying to have the first machine query to deliver a subset of MACHINE records in Netezza that must then be joined with the MACHINE table in the Oracle database. This join happens on a different field, assume that this is a unique identifier for this table (M.field = PM.field).
Then, this subset of oracle machine records must be joined with the large fact table from Oracle. Finally I want to additionally join the subset of that large fact table with a master data table in Oracle (ora.MASTER_DATA_TABLE) and I want to join another master table from Netezza with that. Again based on a field in both master data tables from both datasources that correspond (NM.field = I.field).
The behavior I observe is as follows:
JDV first tries to query all the tables on the Netezza end without any criteria. As these tables do not match but no constraints are given this results in an enormous resultset (more than 9 million records). This is obviously very slow. When that's finished it queries the oracle tables in one query, using the results it gets from the master tables as "IN" statements. The resulting set is correct but because the initial query is very slow, this leads to a very long waiting time. What I would like to prevent JDV from doing is to fetch ALL the records from the Netezza end in one query. Rather, I would like to split the MACHINE queries from the MASTER_DATA_TABLE queries since they are unrelated.
Is this possible to do? I played around with more /*+MAKEDEP*/ instructions and tried to set the last part of the join as /*+ MAKEIND*/ hoping it would separate that query from the rest. This did not lead to the behavior I would like to trigger.
Teiid version 8.7.5, JDV version 6.2 with the latest patch.