Issue with "Directed Queries" in Vertica when queries are executed from Oracle BI EE

ErickErick ✭
in General Discussion

Hello, we are currently integrating Vertica 9.2.1 to Oracle BI EE (OBIEE) 11.1.1.9.0 as a backend database, but we are having an issue related to new feature "Directed Queries". When "input queries" are executed from OBIEE "directed queries" are not working in Vertica becasue "input queries" are not translated to "annotated queries" and query plan for "Input Queries" are different from query plan for "Annotated Queries".
Weirdly when we execute the "input queries" from VSQL they are translated to "annotated queries" sucessfully so this issue seems to be related to Vertica integration with OBIEE or any specific configuration in OBIEE.

  • Does anybody have already be experienced this issue related to Vertica integration with OBIEE?
  • Considering that OBIEE uses unixODBC 2.3.1 && Vertica ODBC Driver 9.2.1 to connect to Vertica database, this issue could be related to ODBC Driver ?
  • Does anybody know if OBIEE could be use JDBC to connect to Vertica backend database?
  • Do you know if this new feature (Directed Queries) is fully supported in Oracle OBIEE 11.1.1.9?

1). SQL commands to create and enable one Directed Query:
SAVE QUERY
select 0 as c1, D1.c2 as c2, D1.c3 as c3, D1.c1 as c4 from ( select sum(T1949."QT_P_G_RESP_I") as c1, T495."CD_EVCT" as c2, T219."NM_TPOL" as c3 from "DIM_EVCT" T495, "DIM_TPOL" T219, "FAT_PMED_TPOL_PAGEN_MES_MCP" T1949 where ( T219."ID_TPOL" = T1949."ID_TPOL" and T219."NM_TPOL" = 'CANA BRAVA - SALVADOR'/+IGNORECONST(1)/ and T495."IC_CRGA_FNLZ" = 'S' and T495."CD_EVCT" = 201907001000.0 and T495."ID_EVCT" = T1949."ID_EVCT" and T495."NM_EVCT" <> 'GENÉRICO'/+IGNORECONST(2)/ and (T495."CD_CNTX" in (4, 5, 6, 7, 34, 35, 36, 39, 44, 51, 52, 53)) and T495."NM_EVCT" <> 'N/A' ) group by T219."NM_TPOL", T495."CD_EVCT" ) D1;

CREATE DIRECTED QUERY CUSTOM 'DQ_MEDTPOL001_TESTE'
select /+ SYNTACTIC_JOIN/ 0 as c1, D1.c2 as c2, D1.c3 as c3, D1.c1 as c4 from ( select sum(T1949."QT_P_G_RESP_I") as c1, T495."CD_EVCT" as c2, T219."NM_TPOL" as c3 from "DIM_EVCT" T495, "FAT_PMED_TPOL_PAGEN_MES_MCP" T1949 JOIN /+JTYPE(FM)/ "DIM_TPOL" T219 ON (T1949."ID_TPOL"=T219."ID_TPOL") where ( T219."NM_TPOL" = 'CANA BRAVA - SALVADOR'/+IGNORECONST(1)/ and T495."IC_CRGA_FNLZ" = 'S' and T495."CD_EVCT" = 201907001000.0 and T495."ID_EVCT" = T1949."ID_EVCT" and T495."NM_EVCT" <> 'GENÉRICO'/+IGNORECONST(2)/ and (T495."CD_CNTX" in (4, 5, 6, 7, 34, 35, 36, 39, 44, 51, 52, 53)) and T495."NM_EVCT" <> 'N/A' ) group by T219."NM_TPOL", T495."CD_EVCT" ) D1;

ACTIVATE DIRECTED QUERY 'DQ_MEDTPOL001_TESTE';

2). Query plan (Explain) for the "Annotated Query". Please note that JOIN type is MERGE (Hint used in Annotated Query)
QUERY PLAN DESCRIPTION:
EXPLAIN select /+ SYNTACTIC_JOIN/ 0 as c1, D1.c2 as c2, D1.c3 as c3, D1.c1 as c4 from ( select sum(T1949."QT_P_G_RESP_I") as c1, T495."CD_EVCT" as c2, T219."NM_TPOL" as c3 from "DIM_EVCT" T495, "FAT_PMED_TPOL_PAGEN_MES_MCP" T1949 JOIN /+JTYPE(FM)/ "DIM_TPOL" T219 ON (T1949."ID_TPOL"=T219."ID_TPOL") where ( T219."NM_TPOL" = 'CANA BRAVA - SALVADOR'/+IGNORECONST(1)/ and T495."IC_CRGA_FNLZ" = 'S' and T495."CD_EVCT" = 201907001000.0 and T495."ID_EVCT" = T1949."ID_EVCT" and T495."NM_EVCT" <> 'GENÉRICO'/+IGNORECONST(2)/ and (T495."CD_CNTX" in (4, 5, 6, 7, 34, 35, 36, 39, 44, 51, 52, 53)) and T495."NM_EVCT" <> 'N/A' ) group by T219."NM_TPOL", T495."CD_EVCT" ) D1

Access Path:
+-GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 18K, Rows: 17K] (PATH ID: 2)
| Aggregates: sum(FAT_PMED_TPOL_PAGEN_MES_MCP.QT_P_G_RESP_I)
| Group By: T219.NM_TPOL, DIM_EVCT.CD_EVCT
| Execute on: All Nodes
| +---> JOIN MERGEJOIN(inputs presorted) [Cost: 18K, Rows: 17K] (PATH ID: 3) Outer (SORT ON JOIN KEY)
| | Join Cond: (FAT_PMED_TPOL_PAGEN_MES_MCP.ID_TPOL = T219.ID_TPOL)
| | Materialize at Input: FAT_PMED_TPOL_PAGEN_MES_MCP.ID_EVCT, FAT_PMED_TPOL_PAGEN_MES_MCP.ID_TPOL, FAT_PMED_TPOL_PAGEN_MES_MCP.QT_P_G_RESP_I
| | Execute on: All Nodes
| | +-- Outer -> JOIN HASH [Cost: 11K, Rows: 3M] (PATH ID: 4) Inner (BROADCAST)
| | | Join Cond: (DIM_EVCT.ID_EVCT = FAT_PMED_TPOL_PAGEN_MES_MCP.ID_EVCT)
| | | Execute on: All Nodes
| | | +-- Outer -> STORAGE ACCESS for FAT_PMED_TPOL_PAGEN_MES_MCP [Cost: 6K, Rows: 22M] (PATH ID: 5)
| | | | Projection: DMMCP.FAT_PMED_TPOL_PAGEN_MES_MCP_MANUAL_MEDTPOL_1_b0
| | | | Materialize: FAT_PMED_TPOL_PAGEN_MES_MCP.ID_EVCT
| | | | Execute on: All Nodes
| | | | Runtime Filters: (SIP2(HashJoin): FAT_PMED_TPOL_PAGEN_MES_MCP.ID_EVCT), (SIP1(MergeJoin): FAT_PMED_TPOL_PAGEN_MES_MCP.ID_TPOL)
| | | +-- Inner -> STORAGE ACCESS for DIM_EVCT [Cost: 267, Rows: 1K] (PATH ID: 6)
| | | | Projection: DMCORP.DIM_EVCT_DBD_26_seg_07082019_inc_3_b0
| | | | Materialize: DIM_EVCT.ID_EVCT, DIM_EVCT.CD_EVCT
| | | | Filter: (DIM_EVCT.IC_CRGA_FNLZ = 'S')
| | | | Filter: (DIM_EVCT.CD_CNTX = ANY (ARRAY[4, 5, 6, 7, 34, 35, 36, 39, 44, 51, 52, 53]))
| | | | Filter: (DIM_EVCT.CD_EVCT = 201907001000.0)
| | | | Filter: ((DIM_EVCT.NM_EVCT <> 'GENÉRICO' /+IgnoreConst(2)/) AND (DIM_EVCT.NM_EVCT <> 'N/A'))
| | | | Execute on: All Nodes
| | +-- Inner -> STORAGE ACCESS for T219 [Cost: 179, Rows: 25] (PATH ID: 7)
| | | Projection: DMCORP.DIM_TPOL_DBD_26_rep_06082019_inc_1
| | | Materialize: T219.NM_TPOL, T219.ID_TPOL
| | | Filter: (T219.NM_TPOL = 'CANA BRAVA - SALVADOR' /+IgnoreConst(1)/)
| | | Execute on: All Nodes

3). Query plan for one "Input Query" run from Oracle OBIEE. Please, note that Join type is HASH and not JOIN MERGE because this query plan is different from "Annotated Query".

+-GROUPBY HASH (GLOBAL RESEGMENT GROUPS) (LOCAL RESEGMENT GROUPS) [Cost: 2M, Rows: 573K (NO STATISTICS)] (PATH ID: 2)
| Aggregates: sum(FAT_PMED_TPOL_PAGEN_MES_MCP.QT_P_G_RESP_I), max(T219.NM_TPOL)
| Group By: collation(T219.NM_TPOL, 'en_US'), DIM_EVCT.CD_EVCT
| Execute on: All Nodes
| +---> JOIN (CROSS JOIN) [Cost: 2M, Rows: 820K (NO STATISTICS)] (PATH ID: 3) Inner (BROADCAST)
| | Filter: (VAL(6) OR (DIM_EVCT.IC_BLOQ_ADMV = 2))
| | Execute on: All Nodes
| | +-- Outer -> JOIN HASH [Cost: 2M, Rows: 820K (NO STATISTICS)] (PATH ID: 4) Outer (LOCAL ROUND ROBIN)
| | | Join Cond: (T219.ID_TPOL = FAT_PMED_TPOL_PAGEN_MES_MCP.ID_TPOL)
| | | Execute on: All Nodes
....
| | | | Execute on: Query Initiator

Many thanks for your help.

Tagged:

Comments

  • siddyashsiddyash Employee

    Hello,
    We tested OBIEE version 11.1.1.7, it seems to be working fine. However, we are using the natively supported (DataDirect) ODBC instead of unixODBC.
    The results that we saw, is that the query in OBIEE seems to select the correct plan similar to what the same query is executed in VSQL.
    Would you be able to use OBIEE natively support ODBC (using datadirect instead of unixODBC) and let us know if that worked?
    Thanks.

  • Vertica_CurtisVertica_Curtis Employee

    This should be as easy as just running the query in OBIEE, and checking query_profiles or query_requests to see which query it's actually running. I wouldn't imagine that Vertica would care what kind of connection the client is using. The query is the query. The query that shows up in query_requests, is the query that you'd use as the basis for your directed query.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...