Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2004
    Posts
    3

    Talking Unanswered: A big trouble with Cost-Based-Optimizer - simple query

    Oracle version 9.2.0.1.0. I have a simple table, see below:

    create table sales
    (dept_id integer,
    sale_month integer,
    total_sum number);


    There are about 100000 rows in this table, column dept_id has 10 distinct values, column sale_month - 12 distinct values. Then I have to run the following query against this table:

    Query 1:
    select dept_id, sale_month, sum(total_sum) as total from sales
    group by grouping sets (dept_id, sale_month);


    Official Orace documentation claims that via hint EXPAND_GSET_TO_UNION previous query can be transformed into:

    Query2 (it realy gives the same result!):
    select null, sale_month, sum(total_sum) as total from sales group by sale_month
    union all
    select dept_id, null, sum(total_sum) as total from sales group by dept_id;


    Then, as we can read in documentation, for every branch in compound Query 2 an optimizer tries to rewrite it using an appropriate materialized view, provided it exists. Well, let's create such views:

    create materialized view sales_1 enable query rewrite as select dept_id, sum(total_sum) as total
    from sales group by dept_id;

    create materialized view sales_2 enable query rewrite as select sale_month, sum(total_sum) as total
    from sales group by sale_month;


    Then I gathered schema statistics with dbms_stats and, of course, ran:

    alter session set query_rewrite_enabled = TRUE;
    alter session set query_rewrite_integrity = TRUSTED;


    Ooops!... But even after all this manipulations plan for Query 1 remains unchanged, regardless of hint EXPAND_GSET_TO_UNION. Btw, Query 2 now uses materialized views for query rewrite (as you can see from execution plan below).

    Code:
    PLAN_TABLE_OUTPUT
    --------------------------------------------------------------------------------
    
    -------------------------------------------------------------------------
    | Id  | Operation            |  Name       | Rows  | Bytes | Cost (%CPU)|
    -------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |             |    22 |   550 |     8  (63)|
    |   1 |  UNION-ALL           |             |       |       |            |
    |   2 |   TABLE ACCESS FULL  | SALES_2     |    12 |   300 |     4  (25)|
    |   3 |   TABLE ACCESS FULL  | SALES_1     |    10 |   250 |     4  (25)|
    -------------------------------------------------------------------------
    
    9 rows selected
    Help me, please, where I went wrong???

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    patch to 9204 or 9205
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2004
    Posts
    3
    Quote Originally Posted by The_Duck
    patch to 9204 or 9205
    Thanks.

    By the way, I asked the same question at www.oracle-base.com and one member of that forum answered he tried my example in Oracle 10g - everything went Ok

    Looks like it is time to upgrade....

    Cheers.

Posting Permissions

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