You can translate the question and the replies:

Summary rewriting does not work when querying through an interface view

Hi, I'm making tests on Summary Rewrite Optimization using Denodo Express 8.0 for Windows 64 bits. I have a base view on an Oracle table (bv_dwhxn_facd). I have an interface view on this base view (iv_bv_dwhxn_facd). To keep it simple the interface view does not make any changes except changing the name of the base view. I build a summary on the base view (su_fn_facd_man006). When i execute a query on the base view, compatible with a query rewrite on the summary view everything works fine. The optimizer rewrite the query using the summary view. When i execute the same query on the interface view the query rewrite doesn’t take place. Is it normal? Pierre SANGUINETTI Below an extract of the source code CREATE OR REPLACE WRAPPER JDBC bv_dwhxn_facd DATASOURCENAME=ds_dwhxn SCHEMANAME='DWHXN' RELATIONNAME='FACD' OUTPUTSCHEMA ( fact_no = 'FACT_NO' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='20', sourcetypeid='12', sourcetypename='VARCHAR2') NOT NULL SORTABLE, facd_line_no = 'FACD_LINE_NO' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='38', sourcetypeid='2', sourcetypename='NUMBER') NOT NULL SORTABLE, sico_code = 'SICO_CODE' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='3', sourcetypeid='1', sourcetypename='CHAR') NOT NULL SORTABLE, sku_reference = 'SKU_REFERENCE' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='30', sourcetypeid='12', sourcetypename='VARCHAR2') NOT NULL SORTABLE, facd_mtt = 'FACD_MTT' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='2', sourcetyperadix='10', sourcetypesize='13', sourcetypeid='2', sourcetypename='NUMBER') NOT NULL SORTABLE, facd_qty = 'FACD_QTY' :'java.math.BigDecimal' (OPT) (sourcetyperadix='10', sourcetypeid='2', sourcetypename='NUMBER') NOT NULL SORTABLE ) CONSTRAINT 'PK_FACD' PRIMARY KEY ( 'FACT_NO' , 'FACD_LINE_NO' ) CONSTRAINT 'FK_FACD_FACT' FOREIGN KEY ( 'FACT_NO' ) REFERENCES 'DWHXN'.'FACT'( 'FACT_NO' ) ON DELETE RESTRICT NOT DEFERRABLE CONSTRAINT 'FK_FACD_SKU' FOREIGN KEY ( 'SICO_CODE' , 'SKU_REFERENCE' ) REFERENCES 'DWHXN'.'SKU'( 'SICO_CODE' , 'SKU_REFERENCE' ) ON DELETE RESTRICT NOT DEFERRABLE INDEX 'PK_FACD' CLUSTER UNIQUE PRIMARY ( 'FACT_NO' , 'FACD_LINE_NO' ) INDEX 'FK_FACD_SKU' CLUSTER ( 'SICO_CODE' , 'SKU_REFERENCE' ); CREATE OR REPLACE TABLE bv_dwhxn_facd I18N fr_euro ( fact_no:text (notnull, sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '20'), facd_line_no:decimal (notnull, sourcetypeid = '3', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '38'), sico_code:text (notnull, sourcetypeid = '1', sourcetyperadix = '10', sourcetypesize = '3'), sku_reference:text (notnull, sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '30'), facd_mtt:decimal (notnull, sourcetypeid = '3', sourcetyperadix = '10', sourcetypedecimals = '2', sourcetypesize = '13'), facd_qty:decimal (notnull, sourcetypeid = '3', sourcetyperadix = '10') ) CONSTRAINT 'PK_FACD' PRIMARY KEY ( 'fact_no' , 'facd_line_no' ) CACHE OFF BATCHSIZEINCACHE DEFAULT TIMETOLIVEINCACHE DEFAULT ADD SEARCHMETHOD bv_dwhxn_facd( I18N fr_euro CONSTRAINTS ( ADD fact_no (any) OPT ANY ADD facd_line_no (any) OPT ANY ADD sico_code (any) OPT ANY ADD sku_reference (any) OPT ANY ADD facd_mtt (any) OPT ANY ADD facd_qty (any) OPT ANY ) OUTPUTLIST (facd_line_no, facd_mtt, facd_qty, fact_no, sico_code, sku_reference ) WRAPPER (jdbc bv_dwhxn_facd) ) SMART_ONLY; CREATE OR REPLACE VIEWSTATSUMMARY bv_dwhxn_facd ( SET ENABLED = true SET NUMOFROWS = 24984165 SET STATS (fieldname, avgsize, maxvalue, minvalue, numofdistinctvalues, numofnullvalues) VALUES (fact_no,13.0,null,null,9936896,0), (facd_line_no,3.0,4,1,4,0), (sico_code,4.0,null,null,20,0), (sku_reference,19.0,null,null,5700,0), (facd_mtt,5.0,1100,100,20014,0), (facd_qty,4.0,1101,100,1002,0) ); CREATE OR REPLACE INTERFACE VIEW iv_bv_dwhxn_facd ( fact_no:text (sourcetyperadix = '10', sourcetypesize = '20', sourcetypeid = '12'), facd_line_no:decimal (sourcetypedecimals = '0', sourcetyperadix = '10', sourcetypesize = '38', sourcetypeid = '3'), sico_code:text (sourcetyperadix = '10', sourcetypesize = '3', sourcetypeid = '1'), sku_reference:text (sourcetyperadix = '10', sourcetypesize = '30', sourcetypeid = '12'), facd_mtt:decimal (sourcetypedecimals = '2', sourcetyperadix = '10', sourcetypesize = '13', sourcetypeid = '3'), facd_qty:decimal (sourcetyperadix = '10', sourcetypeid = '3') ) SET IMPLEMENTATION bv_dwhxn_facd; ALTER VIEW iv_bv_dwhxn_facd LAYOUT (bv_dwhxn_facd = [350, 50, 224, 145]); CREATE OR REPLACE WRAPPER JDBC su_fn_facd_man006 DATASOURCENAME=ds_dtmxn SCHEMANAME='DTMXN' RELATIONNAME='AV_FACD_MAN006' OUTPUTSCHEMA ( sico_code = 'SICO_CODE' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='3', sourcetypeid='1', sourcetypename='CHAR') SORTABLE, sku_reference = 'SKU_REFERENCE' :'java.lang.String' (OPT) (sourcetyperadix='10', sourcetypesize='30', sourcetypeid='12', sourcetypename='VARCHAR2') SORTABLE, facd_mtt = 'FACD_MTT' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='20', sourcetyperadix='10', sourcetypesize='38', sourcetypeid='2', sourcetypename='NUMBER') SORTABLE, facd_qty = 'FACD_QTY' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='20', sourcetyperadix='10', sourcetypesize='38', sourcetypeid='2', sourcetypename='NUMBER') SORTABLE, count = 'FIELD_0' :'java.math.BigDecimal' (OPT) (sourcetypedecimals='0', sourcetyperadix='10', sourcetypesize='20', sourcetypeid='2', sourcetypename='NUMBER') SORTABLE ); CREATE OR REPLACE SUMMARY VIEW su_fn_facd_man006 ( sico_code:text (sourcetypeid = '1', sourcetyperadix = '10', sourcetypesize = '3'), sku_reference:text (sourcetypeid = '12', sourcetyperadix = '10', sourcetypesize = '30'), facd_mtt:decimal (sourcetypeid = '3', sourcetyperadix = '10', sourcetypedecimals = '20', sourcetypesize = '38'), facd_qty:decimal (sourcetypeid = '3', sourcetyperadix = '10', sourcetypedecimals = '20', sourcetypesize = '38'), count:long (sourcetypeid = '3', sourcetyperadix = '10', sourcetypedecimals = '0', sourcetypesize = '20') ) ADD SEARCHMETHOD su_fn_facd_man006( I18N fr_euro CONSTRAINTS ( ADD sico_code (any) OPT ANY ADD sku_reference (any) OPT ANY ADD facd_mtt (any) OPT ANY ADD facd_qty (any) OPT ANY ADD count (any) OPT ANY ) OUTPUTLIST (count, facd_mtt, facd_qty, sico_code, sku_reference ) WRAPPER (jdbc su_fn_facd_man006) ) SMART_THEN_ATSOURCE_THROUGH_VDP AS SELECT bv_dwhxn_facd.sico_code AS sico_code, bv_dwhxn_facd.sku_reference AS sku_reference, facd_mtt AS facd_mtt, facd_qty AS facd_qty, count AS count FROM (SELECT sico_code, sku_reference, sum(facd_mtt) AS facd_mtt, sum(facd_qty) AS facd_qty, count(*) AS count FROM bv_dwhxn_facd GROUP BY sico_code, sku_reference); ALTER VIEW su_fn_facd_man006 DELEGATESTATSQUERY = true GET_TABLE_STATS = true SMART_THEN_ATSOURCE_THROUGH_VDP; CREATE OR REPLACE VIEWSTATSUMMARY su_fn_facd_man006 ( SET ENABLED = true SET NUMOFROWS = 6000 SET STATS (fieldname, avgsize, maxvalue, minvalue, numofdistinctvalues, numofnullvalues) VALUES (sico_code,3.0,'019','000',20,0), (sku_reference,18.0,'000000000000005999','000000000000000000',5700,0), (facd_mtt,132.0,1094250.42,908931,6000,0), (facd_qty,132.0,2656037,2347417,5893,0), (count,8.0,4394,3939,375,0) ); -- Summary Rewrite works with this query -- QR ok AV_FACD_006 SELECT MON_BEGIN, PROD_LABEL, SUM(FACD_QTY) FACD_QTY, SUM(FACD_MTT) FACD_MTT --COUNT(*) FACD_COUNT FROM BV_FV_FACD FACD GROUP BY MON_BEGIN, PROD_LABEL ; -- Summary Rewrite does not work with this query -- QR KO FV_FACD SELECT MON_BEGIN, PROD_LABEL, SUM(FACD_QTY) FACD_QTY, SUM(FACD_MTT) FACD_MTT --COUNT(*) FACD_COUNT FROM IV_BV_FV_FACD FACD GROUP BY MON_BEGIN, PROD_LABEL ;
user
16-01-2021 12:00:14 -0500

2 Answers

Hi, Generally, I would use the top level views while designing a Summary, i.e. the views which appear more often in my daily queries. This way, the Query Optimizer will be able to use the optimization more frequently. In this particular scenario, I see that the Summary is based on the view 'bv_dwhxn_facd' and not 'iv_bv_dwhxn_facd'. Therefore, whenever the query includes the view 'bv_dwhxn_facd', the Optimizer will consider using the Summary optimization which seems to be expected at the moment. Hence, I would make sure that I define the Summary with the **top** level views, i.e. the views which I expect to be executed more often. You could take a look at the [Smart Query Acceleration Using Summaries](https://community.denodo.com/docs/html/browse/8.0/vdp/administration/optimizing_queries/summary_views/summary_views) section under the Virtual DataPort Administration Guide for more details. Hope this helps!
Denodo Team
18-01-2021 09:30:10 -0500
I have a similar situation. Interface view simply mirroring a derived view with one additional projected column. Cache is built FULL on the derived view. Using VQL Shell, I cannot get Denodo to ignore the cache of the derived view and choose the summary. Even if I use the exact same query used to create the summary. **Also:** * I have verified that summary reqrite optimization is enabled at the global, database and query level. * I have unsuccessfully tried using: CONTEXT ('summary_rewrite'='on', 'consider_all_summaries'='on') * I have unsuccessfully tried using: CONTEXT ('summary_rewrite'='on', 'cache'='off', 'consider_all_summaries'='on)
user
28-04-2021 14:59:53 -0400
You must sign in to add an answer. If you do not have an account, you can register here