sum of the joined partition
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: sum of the joined partition

  1. #1
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681

    sum of the joined partition

    Hi Friends,

    I am confused how to add paritions group summation.
    Example:
    SALES table: partitioned by sales_date (by year)
    Number of rows: 40 Million average of 4 million a year (per partition)
    One of its columns is product_type. (not indexed)

    Problem:

    I want to count products_type sold from june 2005 to june 2006
    group by product type;

    I have to use the partition coz the column product_type is not indexed.

    select product_type,count(*) from SALES partition(SALES2005)
    where sales_date>='01-JUN-05' group by product_type
    UNION ALL
    select product_type,count(*) from SALES partition(SALES2006)
    where sales_date<='30-JUN-06' group by product_type
    /

    Is the above code valid? can i use union all to add the two groups?


    Thanks
    Behind The Success And Failure Of A Man Is A Woman

  2. #2
    Join Date
    Nov 2004
    Location
    Mumbai, India
    Posts
    452
    Lets make it simple:

    select product_type,count(*) from SALES where trunc(sales_date)>='01-JUN-05' and trunc(sales_date)<='30-JUN-06'
    group by product_type
    IMHO this will automatically access the respective partitions automatically
    There are three kinds of lies: Lies, damned lies, and benchmarks...

    Unix is user friendly. It's just very particular about who it's friends are.

    Oracle DBA

  3. #3
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    really? i keep on including partitions to all of my queries when
    it is automatic detected....ok I'll try to test it....to see is to believe
    Why do u have to put "trunc"? I think "BETWEEN" is better?
    Does it matter if i use to_char(sales_date) rather than trunc(sales_date)?
    what is "IMHO" again?
    Last edited by yxez; 11-22-2006 at 04:34 AM.
    Behind The Success And Failure Of A Man Is A Woman

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Between should be OK - leads to no other difference than the syntax, to_char - I wouldn't.
    Partition pruning is well known feature and is one of the major reasons for partitioning.

  5. #5
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    Thanks dear ...im glad you have been active in aswering posts here? When did u hear of this forum?
    Behind The Success And Failure Of A Man Is A Woman

  6. #6
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    around 2 weeks ago :-)

  7. #7
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    good ...i hope dbasupport.com will encourage more brilliant dba's
    to join the room by giving some tokens...im thinking of something like
    monthly competition...like giving online exams to all the tough dba member's here...and the top scorer will win a prize. will that be a good idea dear
    Behind The Success And Failure Of A Man Is A Woman

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Well, don't really know. I do not belive that any of us is here for a prize ( btw I do not think I am a tough DBA I am not a DBA at all, but I know a lot about Oracle DB. Just my job is related to studying all the features of the database.)
    Just the forum is great, there are lots of questions and answers by qualified people and that attracts more and more members.
    That's my feeling

  9. #9
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Placing a predicate on trunc(sales_date) instead of sales_date will prevent the optimizer from pruning on sales_date-based partitions, unless there is a check constraint in place to indicate that trunc(sales_date) = sales_date -- although in that case there is not much point in using trunc(sales_date) in the predicate anyway.

    Test script ...
    Code:
    drop table t_sales
    /
    
    create table t_sales (sales_date not null)
    partition by range (sales_date)
    (partition Y2000 values less than (date '2001-01-01'),
    partition Y2001 values less than (date '2002-01-01'),
    partition Y2002 values less than (date '2003-01-01'))
    as
    select date '2000-01-01' + rownum - 1
    from dual
    connect by level <= date '2003-01-01' - date '2000-01-01'
    /
    
    begin
    dbms_stats.gather_table_stats(
       ownname => user,
       tabname => 't_sales',
       granularity => 'all');
    end;
    /
    
    select partition_name, num_rows
    from user_tab_partitions
    where table_name = 'T_SALES'
    order by 1
    /
    
    explain plan for
    select * from t_sales
    where sales_date between date '2000-01-01' and date '2000-12-31'
    /
    select * from table(dbms_xplan.display)
    /
    
    explain plan for
    select * from t_sales
    where trunc(sales_date) between date '2000-01-01' and date '2000-12-31'
    /
    select * from table(dbms_xplan.display)
    /
    
    alter table t_sales
    add constraint trunc_sales_date check (sales_date = trunc(sales_date));
    
    explain plan for
    select * from t_sales
    where trunc(sales_date) between date '2000-01-01' and date '2000-12-31'
    /
    select * from table(dbms_xplan.display)
    /
    Result ... notice the change in the filter predicate of the explain plan and the extra clause introduced in the third select statement when the constraint is present, and notice also the pstart and pstop values for the partition pruning.

    For SALES_DATE BETWEEN you get 1 and 1
    For TRUNC(SALES_DATE) BETWEEN you get 1 and 3
    For TRUNC(SALES_DATE) BETWEEN and the constraint in place you get 1 and 1 again

    Code:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    
    SQL> set linesize 200
    SQL> set pagesize 200
    SQL> drop table t_sales
      2  /
    
    Table dropped.
    
    SQL>
    SQL> create table t_sales (sales_date not null)
      2  partition by range (sales_date)
      3  (partition Y2000 values less than (date '2001-01-01'),
      4  partition Y2001 values less than (date '2002-01-01'),
      5  partition Y2002 values less than (date '2003-01-01'))
      6  as
      7  select date '2000-01-01' + rownum - 1
      8  from dual
      9  connect by level <= date '2003-01-01' - date '2000-01-01'
     10  /
    
    Table created.
    
    SQL>
    SQL> begin
      2  dbms_stats.gather_table_stats(
      3     ownname => user,
      4     tabname => 't_sales',
      5     granularity => 'all');
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    
    SQL>
    SQL> select partition_name, num_rows
      2  from user_tab_partitions
      3  where table_name = 'T_SALES'
      4  order by 1
      5  /
    
    PARTITION_NAME                   NUM_ROWS
    ------------------------------ ----------
    Y2000                                 366
    Y2001                                 365
    Y2002                                 365
    
    SQL>
    SQL> explain plan for
      2  select * from t_sales
      3  where sales_date between date '2000-01-01' and date '2000-12-31'
      4  /
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display)
      2  /
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------
    --------------------------------------------------
    Plan hash value: 2932426231
    
    --------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |         |   366 |  2928 |     2   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE|         |   366 |  2928 |     2   (0)| 00:00:01 |     1 |     1 |
    |*  2 |   TABLE ACCESS FULL    | T_SALES |   366 |  2928 |     2   (0)| 00:00:01 |     1 |     1 |
    --------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter("SALES_DATE">=TO_DATE('2000-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
                  "SALES_DATE"<=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
    
    15 rows selected.
    
    SQL>
    SQL> explain plan for
      2  select * from t_sales
      3  where trunc(sales_date) between date '2000-01-01' and date '2000-12-31'
      4  /
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display)
      2  /
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------
    --------------------------------------------------
    Plan hash value: 722415528
    
    -----------------------------------------------------------------------------------------------
    | Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |         |     3 |    24 |     2   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE ALL|         |     3 |    24 |     2   (0)| 00:00:01 |     1 |     3 |
    |*  2 |   TABLE ACCESS FULL | T_SALES |     3 |    24 |     2   (0)| 00:00:01 |     1 |     3 |
    -----------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(TRUNC(INTERNAL_FUNCTION("SALES_DATE"))>=TO_DATE('2000-01-01 00:00:00',
                  'yyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("SALES_DATE"))<=TO_DATE('2000-12-3
                  1 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
    
    16 rows selected.
    
    SQL>
    SQL> alter table t_sales
      2  add constraint trunc_sales_date check (sales_date = trunc(sales_date));
    
    Table altered.
    
    SQL>
    SQL> explain plan for
      2  select * from t_sales
      3  where trunc(sales_date) between date '2000-01-01' and date '2000-12-31'
      4  /
    
    Explained.
    
    SQL> select * from table(dbms_xplan.display)
      2  /
    
    PLAN_TABLE_OUTPUT
    ----------------------------------------------------------------------------------------------------------
    --------------------------------------------------
    Plan hash value: 2932426231
    
    --------------------------------------------------------------------------------------------------
    | Id  | Operation              | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT       |         |     1 |     8 |     2   (0)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE SINGLE|         |     1 |     8 |     2   (0)| 00:00:01 |     1 |     1 |
    |*  2 |   TABLE ACCESS FULL    | T_SALES |     1 |     8 |     2   (0)| 00:00:01 |     1 |     1 |
    --------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - filter(TRUNC(INTERNAL_FUNCTION("SALES_DATE"))>=TO_DATE('2000-01-01 00:00:00',
                  'yyyy-mm-dd hh24:mi:ss') AND TRUNC(INTERNAL_FUNCTION("SALES_DATE"))<=TO_DATE('2000-12-31
                  00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "SALES_DATE">=TO_DATE('2000-01-01 00:00:00',
                  'yyyy-mm-dd hh24:mi:ss') AND "SALES_DATE"<=TO_DATE('2000-12-31 00:00:00', 'yyyy-mm-dd
                  hh24:mi:ss'))
    
    18 rows selected.
    
    SQL>
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  10. #10
    Join Date
    May 2005
    Location
    Boracay
    Posts
    681
    yes dear...thats my thought also but i just tired proving it thanks anyways...i did use BETWEEN condition and I got a good
    result and the performance I exptected
    Behind The Success And Failure Of A Man Is A Woman

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