OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » partition pruning (oracle 10.2)

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

partition pruning [message #559445]

Tue, 03 July 2012 06:22

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi

i have a table which is partitioned, when i am running the below query for latest data it does full table scan instead of partition scan. How can i write this sql so that it will hit the partition only.

select count(*) from ERS_RESP_TIME_FACT where trunc(date_loaded) = to_date('03/07/2012','dd/mm/yyyy')

My table structure is as below
[code]
CREATE TABLE ERS_RESP_TIME_FACT
(
IND_ID NUMBER(6) NULL,
TRANSACTION_ID VARCHAR2(50 BYTE) NULL,
SERVER_ID VARCHAR2(50 BYTE) NULL,
CMD_ID NUMBER(6) NULL,
BTD_ID NUMBER(6) NULL,
LCD_ID NUMBER(6) NULL,
TRS_IN_RECD_DATETIME DATE NULL,
DTD_CAL_DATE_IN_RECD DATE NULL,
TMD_ID_IN_RECD NUMBER(4) NULL,
TRS_IN_RECD_SEC NUMBER(12, Cool

NULL,
TRS_IN_CALC_DATETIME DATE NULL,
DTD_CAL_DATE_IN_CALC DATE NULL,
TMD_ID_IN_CALC NUMBER(4) NULL,
TRS_IN_CALC_SEC NUMBER(12, Cool

NULL,
TRS_OUT_DATETIME DATE NULL,
DTD_CAL_DATE_OUT DATE NULL,
TMD_ID_OUT NUMBER(4) NULL,
TRS_OUT_SEC NUMBER(12, Cool

NULL,
APPLICATION_RESPONSE_TIME NUMBER(10,3) NULL,
NRT_MULTIPLIER NUMBER(3) NULL,
NRT_TIME NUMBER(10,3) NULL,
WAIT_TIME NUMBER(10,3) NULL,
INTERNAL_TIME NUMBER(10,3) NULL,
EXTERNAL_SERVICE_TIME NUMBER(10,3) NULL,
LOCAL_SERVICE_TIME_INCLUDED NUMBER(10,3) NULL,
LOCAL_SERVICE_TIME_EXCLUDED NUMBER(10,3) NULL,
LOCAL_SERVICE_TIME_UNKNOWN NUMBER(10,3) NULL,
SPINE_TIME_DEDUCTED VARCHAR2(1 BYTE) NULL,
SPINE_TIME NUMBER(10,3) NULL,
COMPONENT_RESPONSE_TIME NUMBER(10,3) NULL,
DETAIL_REC_COUNT NUMBER(3) NULL,
SLA_TRS_TYPE VARCHAR2(10 BYTE) NULL,
TRS_SIZE NUMBER(14,3) NULL,
TRANSFER_RATE NUMBER(17,5) NULL,
DATE_LOADED DATE NULL,
DEFAULT_NRT_USED VARCHAR2(1 BYTE) NULL,
IE_TIME NUMBER(10,3) NULL
)
PARTITION BY RANGE (DATE_LOADED)
[code/]
date_loaded column is partitioned for every day.

explain plan

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	232674  	 	      	             	 
  SORT AGGREGATE		1  	8  	 	 	      	             	 
    PARTITION RANGE ALL		  	 	 	 	      	            1	367
      TABLE ACCESS FULL	ERS_DATA.ERS_RESP_TIME_FACT	3 M	24 M	232674  	 	      	            1	367

Report message to a moderator

Re: partition pruning [message #559454 is a reply to message #559445]

Tue, 03 July 2012 06:33

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

The function trunc(date_loaded) disables the index/pruning because the value needs to be recalculated for every date_loaded item.

replace
where trunc(date_loaded) = to_date('03/07/2012','dd/mm/yyyy')
to
where date_loaded >= trunc(to_date('03/07/2012','dd/mm/yyyy')) and date_loaded <trunc(to_date('03/07/2012','dd/mm/yyyy'))+1

or add a functionbased index trunc(date_loaded)

[Updated on: Tue, 03 July 2012 06:35]

Report message to a moderator

Re: partition pruning [message #559457 is a reply to message #559454]

Tue, 03 July 2012 06:44

guddu_12
Messages: 227
Registered: April 2012
Location: UK

Senior Member

Hi,

Thanks a lot , it is working and 1000 times fast

Report message to a moderator

Previous Topic:	Slow db performance on Oracle 11.2.0.3
Next Topic:	use_hash hint without arguments

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 13:42:56 CST 2025