OraFAQ Forum: Performance Tuning » SQL Tuning (Multicolumn Range Parttiion Pruning)

Home » RDBMS Server » Performance Tuning » SQL Tuning (Multicolumn Range Parttiion Pruning) (10.2.0.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL Tuning (Multicolumn Range Parttiion Pruning) [message #332434]

Tue, 08 July 2008 09:13

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

Report message to a moderator

Re: SQL Tuning (Multicolumn Range Parttiion Pruning) [message #332452 is a reply to message #332434]

Tue, 08 July 2008 09:59

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))

Report message to a moderator

Re: SQL Tuning (Multicolumn Range Parttiion Pruning) [message #332767 is a reply to message #332452]

Wed, 09 July 2008 10:52

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

Report message to a moderator

Re: SQL Tuning (Multicolumn Range Parttiion Pruning) [message #332772 is a reply to message #332767]

Wed, 09 July 2008 11:38

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.

Report message to a moderator

Previous Topic:	Query plan change
Next Topic:	ORACLE TRACE : maximum number of recursive SQL levels (50) exceeded

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 24 16:17:35 CST 2025