Home » RDBMS Server » Performance Tuning » partition pruning (oracle 10.2)
partition pruning [message #559445] Tue, 03 July 2012 06:22 Go to next message
guddu_12
Messages: 172
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

Re: partition pruning [message #559454 is a reply to message #559445] Tue, 03 July 2012 06:33 Go to previous messageGo to next message
Flyby
Messages: 144
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 Go to previous message
guddu_12
Messages: 172
Registered: April 2012
Location: UK
Senior Member
Hi,

Thanks a lot , it is working and 1000 times fast
Previous Topic: Slow db performance on Oracle 11.2.0.3
Next Topic: use_hash hint without arguments
Goto Forum:
  


Current Time: Thu Aug 21 19:58:12 CDT 2014

Total time taken to generate the page: 0.09944 seconds