When the data source that the IP is developed for can perform optimized join processing between tables, the data source should be execute these joins. To support this, the OpenAccess SDK SQL engine supports a mode that allows the IP to build joined result sets for the tables that allows the data source to process the joins. This processing is optional. By default, the OpenAccess SDK SQL engine takes care of all join processing.
This feature is useful in the following cases:
• If your data is stored in a hierarchical format.
• If your database is object-oriented such that the relationships between tables are actually object to object(s) pointers.
• If a single physical record is exposed as multiple logical tables.
Join pushdown support works in the following way:
• To process a join query, the OpenAccess SDK SQL engine determines the order in which each of the tables is queried. If the Join requires processing of tables t1, t2, t1 is referred to as the outer table and t2 as the inner table.
• When the IP is called to process the query on one of the outer tables in the join, the IP can identify the next table (the inner table) in the join. When it is building the result rows for the outer table, the IP can build the matching rowset for the inner table. For each record of the outer table, the IP builds a rowset of the next inner table. The rowset can contain zero or more rows of the inner table. The IP then associates this rowset with the outer table row.
• When the OpenAccess SDK SQL engine requires the inner table records for the join processing, it first checks if the outer table already contains the rowset of the inner table, and uses it for the join processing instead of calling the IP for the inner table records.
An existing IP can be enhanced to take advantage of this by adding logic to the processing of the outer table. You should still maintain the code that was there for handling direct queries to the inner table (for example, SELECT * FROM t2).
