Home » RDBMS Server » Performance Tuning » SQL Tuning (Multicolumn Range Parttiion Pruning) (10.2.0.2)
SQL Tuning (Multicolumn Range Parttiion Pruning) [message #332434] Tue, 08 July 2008 09:13 Go to next message
sujitdba
Messages: 6
Registered: June 2008
Location: United Kingdom
Junior Member
Hi All,

We are facing huge performance issue with the below query,

select *
from dwh_sample.week_base_rfm wbr
where (wbr.rfm_year*100)+wbr.rfm_int_week <= ((2008*100) + 27)
and (wbr.rfm_year*100)+wbr.rfm_int_week > ((2007*100) + 28)

----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4273K| 436M| 737K (25)| 02:02:54 | | |
| 1 | PARTITION RANGE ALL| | 4273K| 436M| 737K (25)| 02:02:54 | 1 | 311 |
| 2 | PARTITION LIST ALL| | 4273K| 436M| 737K (25)| 02:02:54 | 1 | LAST |
|* 3 | TABLE ACCESS FULL| WEEK_BASE_RFM | 4273K| 436M| 737K (25)| 02:02:54 | 1 | 8653 |
----------------------------------------------------------------

Problem:

This query is taking over 3 hours to complete.

Investigated and found that this query is not able to take the advantage of Partition Pruning. Although we are reading only a year data (2007 - 2008) but Oracle is reading all the Partitions since 2002 (see Pstart and Pstop value in the above explain plan).

This table is multicolumn range partitioned on (rfm_year,rfm_int_week )

I tried to add following condition at the end of the statement to specify the range for Partition Pruning but hitting with an issue,

explain plan for
select *
from dwh_common.week_base_rfm wbr
where (wbr.rfm_year*100)+wbr.rfm_int_week <= ((2008*100) + 27)
and (wbr.rfm_year*100)+wbr.rfm_int_week > ((2007*100) + 28)
and (wbr.rfm_year,wbr.rfm_int_week) > (2007,28)
and (wbr.rfm_year,wbr.rfm_int_week) <= (2008,27);

and (wbr.rfm_year,wbr.rfm_int_week) > (2007,28)
*
ERROR at line 6:
ORA-01796: this operator cannot be used with lists

Is there any better way write this query to use Partition Pruning.

Thanks in Advance,
Sujit
Re: SQL Tuning (Multicolumn Range Parttiion Pruning) [message #332452 is a reply to message #332434] Tue, 08 July 2008 09:59 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is why you should always hold date based data in DATE datatypes. Still, too late for that to help us now....

Assuming that RFM_YEAR is the 4 digit year, and RFM_WEEK is the week (1-52 or 53), what happens if you try:
select *
from dwh_sample.week_base_rfm wbr
where wbr.rfm_year in (2007,2008)
and ((wbr.rfm_year = 2008 and wbr.rfm_int_week <= 27)
  or (wbr.rfm_year = 2007 and wbr.rfm_int_week > 28)


Or, you could try
select *
from dwh_sample.week_base_rfm wbr
where (wbr.rfm_year, wbr.rfm_week) in (select to_char(dte,'yyyy')
                                             ,to_char(dte,'ww')
                                       from  (select to_date('08-jul-2007','dd-mon-yyyy') + level*7 dte 
                                              from dual connect by level<= 52))



Re: SQL Tuning (Multicolumn Range Parttiion Pruning) [message #332767 is a reply to message #332452] Wed, 09 July 2008 10:52 Go to previous messageGo to next message
sujitdba
Messages: 6
Registered: June 2008
Location: United Kingdom
Junior Member
Hi JRowbottom,

Thanks for your great help in solving this performance issue.
Your suggestion to include following lines of code in the query for partition pruning has really worked.

       and ((wbr.rfm_year = 2008 and wbr.rfm_int_week <= 27)
       or (wbr.rfm_year = 2007 and wbr.rfm_int_week > 28)) 


SQL> explain plan for
  2  select *
  3  from dwh_sample.week_rfm wbr
  4  where (wbr.rfm_year*100)+wbr.rfm_int_week <= ((2008*100) + 27)
  5   and   (wbr.rfm_year*100)+wbr.rfm_int_week > ((2007*100) + 28)
  6  and ((wbr.rfm_year = 2008 and wbr.rfm_int_week <= 27)
  7    or (wbr.rfm_year = 2007 and wbr.rfm_int_week > 28));

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 3743072257

-----------------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |     1 |   107 |   245K (22)| 00:40:52 |       |       |
|   1 |  PARTITION RANGE OR |               |     1 |   107 |   245K (22)| 00:40:52 |KEY(OR)|KEY(OR)|
|   2 |   PARTITION LIST ALL|               |     1 |   107 |   245K (22)| 00:40:52 |     1 |  LAST |
|*  3 |    TABLE ACCESS FULL| WEEK_RFM |     1 |   107 |   245K (22)| 00:40:52 |   KEY |   KEY |
-----------------------------------------------------------------------------------------------------


Once again appreciate your help.

You guys are really doing great job.

Regards,
Sujit
Re: SQL Tuning (Multicolumn Range Parttiion Pruning) [message #332772 is a reply to message #332767] Wed, 09 July 2008 11:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can get rid of these two lines from the query if you like
  4  where (wbr.rfm_year*100)+wbr.rfm_int_week <= ((2008*100) + 27)
  5   and   (wbr.rfm_year*100)+wbr.rfm_int_week > ((2007*100) + 28)

The two lines following them do exactly the same job.
Previous Topic: Query plan change
Next Topic: ORACLE TRACE : maximum number of recursive SQL levels (50) exceeded
Goto Forum:
  


Current Time: Sat Dec 03 18:08:23 CST 2016

Total time taken to generate the page: 0.10005 seconds