[teiid-issues] [JBoss JIRA] (TEIID-4395) Oracle translator should pushdown clob conversion

[Tom Arnold (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-4395?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13281714#comment-13281714 ] 

Tom Arnold commented on TEIID-4395:
-----------------------------------

Thanks!

> Oracle translator should pushdown clob conversion
> -------------------------------------------------
>
>                 Key: TEIID-4395
>                 URL: https://issues.jboss.org/browse/TEIID-4395
>             Project: Teiid
>          Issue Type: Enhancement
>          Components: JDBC Connector
>    Affects Versions: 9.0.2
>            Reporter: Tom Arnold
>            Assignee: Steven Hawkins
>             Fix For: 9.1, 9.0.3
>
>
> We have an "attributes" table defined like this
> {code:sql}
> CREATE TABLE "ATTRS"
> (
>    NAME varchar2(50) NOT NULL,
>    ATTR_NAME varchar2(50) NOT NULL,
>    ATTR_VALUE clob,
>    CONSTRAINT ATTRS_PK PRIMARY KEY (NAME,ATTR_NAME)
> );
> {code}
> The value column is unfortunately defined as a clob, but most of the data is small enough to fit in varchar (4000 chars). We have a somewhat complex query that ends up grouping by the clob value and with Teiid it is causing N-many queries.
> Simplified to this
> {code:sql}
> select cast(attr_value as varchar) from attrs
> {code}
> {code}
> ProjectNode
>   + Relational Node ID:0
>   + Output Columns:expr1 (string)
>   + Statistics:
>     0: Node Output Rows: 293
>     1: Node Next Batch Process Time: 5
>     2: Node Cumulative Next Batch Process Time: 5
>     3: Node Cumulative Process Time: 11
>     4: Node Next Batch Calls: 2
>     5: Node Blocks: 1
>   + Cost Estimates:Estimated Node Cardinality: 293.0
>   + Child 0:
>     AccessNode
>       + Relational Node ID:1
>       + Output Columns:ATTR_VALUE (clob)
>       + Statistics:
>         0: Node Output Rows: 293
>         1: Node Next Batch Process Time: 0
>         2: Node Cumulative Next Batch Process Time: 0
>         3: Node Cumulative Process Time: 6
>         4: Node Next Batch Calls: 2
>         5: Node Blocks: 1
>       + Cost Estimates:Estimated Node Cardinality: 293.0
>       + Query:SELECT g_0.ATTR_VALUE FROM foo.ATTRS AS g_0
>       + Model Name:foo
>   + Select Columns:convert(foo.ATTRS.ATTR_VALUE, varchar)
>   + Data Bytes Sent:6783
>   + Planning Time:2
> {code}
> Ideally on Oracle it would just get pushed down like this
> {code:sql}
> SELECT TO_CHAR(g_0.ATTR_VALUE) FROM foo.ATTRS AS g_0
> {code}
> From looking in the Oracle translator it looks like it should do this already... I added a test case which passes, but the plan seems to disagree.
> {code:java}
>     @Test public void testClobToString() throws Exception {
>     	Column column = new Column();
>     	column.setNativeType("CLOB");
>     	column.setNameInSource("dt");
>         helpTest(LANG_FACTORY.createColumnReference("dt", LANG_FACTORY.createNamedTable("x", null, null), column, Clob.class), "varchar", "to_char(x.dt)"); //$NON-NLS-1$ //$NON-NLS-2$
>     }
> {code}
> As a workaround I added a CLOB_TO_CHAR foreign function in the model DDL (using teiid_rel:native-query 'TO_CHAR($1)') and changed my query to use that instead of cast, but I'd prefer to not have to do that in the long term.



--
This message was sent by Atlassian JIRA
(v6.4.11#64026)