Teiid Query engine modifies the optimize version of my Query to a un-optimized query when executing

Question

I am executing fairly large SQL select queries against a redshift DB using teiid. I have optimized my query to give a better response time by avoiding using inner queries and inner select statements in my query. How ever when I execute the query, the teiid query engine changes my query to a different version which uses inner queries and inner select statements. Is there any way of bypassing this behavior and directly use the query which i provide.

Her is the original teiid query that I execute

        CREATE VIRTUAL PROCEDURE GetTop() RETURNS (json clob) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'GetTop')
        AS
        /*+ cache(pref_mem ttl:14400000) */
        BEGIN
            execute immediate
            'SELECT  JSONOBJECT(
                JSONARRAY_AGG(
                    JSONOBJECT(
                       total_purchases,
                       total_invoice,
                       total_records,
                       period
                    )
                )
                AS "dd"

            ) as json FROM(
              SELECT SUM((CASE


            GROUP BY period

Teiid query engine converts above query into bellow version which has an inner SELECT Statement

SELECT SUM(v_0.c_1),
       COUNT(DISTINCT v_0.c_2),
       COUNT(v_0.c_2),
       v_0.c_0
FROM (SELECT CASE

GROUP BY v_0.c_0

I would like to knowhHow can I by pass this behavior and execute my original query?

Answer 1

Teiid will only create inline views for specific purposes - it generally removes them when possible. You'll need to provide more context of the user query, the processor plan, or debug plan so that we can understand why the inline views are needed.