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
;
