Function-based indexes and query rewrite
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Function-based indexes and query rewrite

  1. #1
    Join Date
    Feb 2000
    Location
    Brisbane, Australia
    Posts
    46
    Can anyone explain the following:

    -- Create two tables:
    CREATE TABLE TEST1(
    ID NUMBER,
    TIMESTAMP DATE,
    FLAG VARCHAR2(1))
    ;
    create table TEST2(
    ID NUMBER)
    ;

    -- Analyze the tables
    EXEC DBMS_STATS_GATHER_TABLE_STATS (USER,'test1');
    EXEC DBMS_STATS_GATHER_TABLE_STATS (USER,'test2');

    -- Create a materialized view on these tables:
    CREATE MATERIALIZED VIEW testmv
    REFRESH FORCE ON DEMAND
    ENABLE QUERY REWRITE
    AS
    SELECT test1.timestamp
    FROM test1,test2
    WHERE test1.id = test2.id;

    -- Enable Auto Trace
    SET AUTOT TRACE EXP

    -- Execute the query.
    SELECT test1.timestamp
    FROM test1,test2
    WHERE test1.id = test2.id;
    -- Query rewrite is used

    -- What happens if the query is not exactly the same?
    SELECT test1.timestamp
    FROM test1,test2
    WHERE test1.id = test2.id
    order by 1;
    -- Query rewrite is still used

    -- Create a function based index on a column THAT IS NOT IN THE MV or VIEW
    CREATE INDEX test1_i
    ON test1(nvl(flag,A));

    -- Execute the query again.
    SELECT test1.timestamp
    FROM test1,test2
    WHERE test1.id = test2.id
    ;
    -- Query rewrite is used.

    However, ANY deviation from the exact text, including
    case-sensitivity, results in query-rewrite being abandoned.

    I have tried creating stats on the index.

    Oracle Release: 8.1.7.0.0

    Anyone?

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Would be more appropriate in the development rather than in the administration forum. So moving it to development forum...

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Dec 2000
    Posts
    126
    We do not experience the problem in HP 11 8.1.7.1

    SELECT
    sum(ord_units),
    e.YR_cal1_yr
    ,b.TV_type_variant_descr
    ,c.SA_sales_acct_descr
    ,d.RG_compy_reg_descr
    FROM fact a,
    product b,
    customer c,
    site d,
    ttime e
    WHERE a.DT_cal_date = e.DT_cal_date
    AND a.PR_prod_no = b.PR_prod_no
    AND a.CU_db_cust_no = c.CU_db_cust_no
    AND a.DR_db_run_no = d.DR_db_run_no
    GROUP BY
    e.YR_cal1_yr
    ,b.TV_type_variant_descr
    ,c.SA_sales_acct_descr
    ,d.RG_compy_reg_descr

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=89846724 Card=1 Byte
    s=162)

    1 0 SORT* (GROUP BY) (Cost=89846724 Card=1 Bytes=162) :Q206009
    2 1 MERGE JOIN* (Cost=24504 Card=1358644193 Bytes=2201003592 :Q206008
    66)

    3 2 SORT* (JOIN) (Cost=21781 Card=666149 Bytes=87931668) :Q206008
    4 3 HASH JOIN* (Cost=421 Card=666149 Bytes=87931668) :Q206007
    5 4 HASH JOIN* (Cost=50 Card=1862 Bytes=158270) :Q206006
    6 5 HASH JOIN* (Cost=8 Card=30 Bytes=1890) :Q206005
    7 6 VIEW* (Cost=6 Card=37 Bytes=222) :Q206000
    8 7 SORT (UNIQUE) (Cost=6 Card=37 Bytes=222)
    9 8 TABLE ACCESS (FULL) OF 'TTIME' (Cost=2 Car
    d=364 Bytes=2184)

    10 6 TABLE ACCESS* (FULL) OF 'DWMV_INV_DAILY_WK_PRO :Q206004
    _CUS_PRF' (Cost=1 Card=82 Bytes=4674) <<< QUERY RE-WRITTEN TO USE MVIEWS

    **** ANOTHER VARIANCE OF QUERY
    SQL> l
    1 SELECT
    2 SUM(ORD_UNITS), << UPPER CASE
    3 e.YR_cal1_yr
    4 ,c.SA_sales_acct_descr << swap these 2 cols
    5 ,b.TV_type_variant_descr
    6 ,d.RG_compy_reg_descr
    7 FROM fact a,
    8 product b,
    9 customer c,
    10 site d,
    11 ttime e
    12 WHERE a.DT_cal_date = e.DT_cal_date
    13 AND a.PR_prod_no = b.PR_prod_no
    14 AND a.CU_db_cust_no = c.CU_db_cust_no
    15 AND a.DR_db_run_no = d.DR_db_run_no
    16 GROUP BY
    17 e.YR_cal1_yr
    18 ,b.TV_type_variant_descr
    19 ,c.SA_sales_acct_descr
    20* ,d.RG_compy_reg_descr


    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=89846724 Card=1 Byte
    s=162)

    1 0 SORT* (GROUP BY) (Cost=89846724 Card=1 Bytes=162) :Q207009
    2 1 MERGE JOIN* (Cost=24504 Card=1358644193 Bytes=2201003592 :Q207008
    66)

    3 2 SORT* (JOIN) (Cost=21781 Card=666149 Bytes=87931668) :Q207008
    4 3 HASH JOIN* (Cost=421 Card=666149 Bytes=87931668) :Q207007
    5 4 HASH JOIN* (Cost=50 Card=1862 Bytes=158270) :Q207006
    6 5 HASH JOIN* (Cost=8 Card=30 Bytes=1890) :Q207005
    7 6 VIEW* (Cost=6 Card=37 Bytes=222) :Q207000
    8 7 SORT (UNIQUE) (Cost=6 Card=37 Bytes=222)
    9 8 TABLE ACCESS (FULL) OF 'TTIME' (Cost=2 Car
    d=364 Bytes=2184)

    10 6 TABLE ACCESS* (FULL) OF 'DWMV_INV_DAILY_WK_PRO :Q207004
    _CUS_PRF' (Cost=1 Card=82 Bytes=4674) <<< QUERY RE-WRITTEN TO USE MVIEWS


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width