sum of the joined partition

yxez · #1 11-22-2006, 03:37 AM

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

simply_dba · #2 11-22-2006, 03:51 AM

Lets make it simple:

Quote:

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

yxez · #3 11-22-2006, 04:01 AM

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?

Bore · #4 11-22-2006, 05:07 AM

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.

yxez · #5 11-22-2006, 05:38 AM

Thanks dear

...im glad you have been active in aswering posts here? When did u hear of this forum?

Bore · #6 11-22-2006, 05:52 AM

around 2 weeks ago :-)

yxez · #7 11-22-2006, 06:48 AM

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

Bore · #8 11-22-2006, 10:29 AM

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

slimdave · #9 11-22-2006, 01:38 PM

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>

yxez · #10 11-22-2006, 07:48 PM

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