Details
-
Enhancement
-
Resolution: Obsolete
-
Major
-
9.x
-
None
Description
It would be helpful to pushdown SQL/XML functions for JDBC databases that support them.
Databases:
- Oracle
- Postgres
Functions:
- XMLELEMENT
- XMLATTRIBUTES
- XMLAGG
- XMLCONCAT
We have an application that uses these functions and we support Oracle, Postgres, and Teiid (to integrate external data sources). Here's an example query generated by this application:
select "ITEMS"."ITEM_ID" "ITEM_ID", ( select xmlelement ( name "attributes", xmlagg( xmlelement( name "attribute", xmlattributes( "ITEM_ATTRIBUTES"."ATTR_NAME" as "name", "ITEM_ATTRIBUTES"."ATTR_VALUE" as "value" ) ) ) ) from "ITEM_ATTRIBUTES" "ITEM_ATTRIBUTES" where ( "ITEMS"."ITEM_ID" = "ITEM_ATTRIBUTES"."ITEM_ID" ) ) "ITEM_ATTRIBUTES"
ITEM_ID ITEM_ATTRIBUTES 3 <attributes><attribute name="foo" value="bar"></attribute><attribute name="baz" value="blah"></attribute></attributes> 4 <attributes><attribute name="foo" value="123"></attribute><attribute name="baz" value="456"></attribute></attributes>
When we are using Teiid queries like this do not work well because they cannot be pushed down, so we get N-many queries (one per row) and the query ends up taking much longer than if performed directly against Oracle/Postgres.
Since SQL/XML is standard and supported by both Oracle/Postgres it would be good if queries like this could be pushed in their entirety so that there would be no performance difference between going through Teiid and native.
Attachments
Issue Links
- incorporates
-
TEIID-979 Add pushdown support for sql/xml functions
- Resolved