-
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
-
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
-
-
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.
-
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
-
-
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
-
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
-
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>
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|