Home » RDBMS Server » Performance Tuning » Single operation slows down a query (Oracle9i, 9.2.0.6.0, Win 2003)
Single operation slows down a query [message #303953] Mon, 03 March 2008 07:27 Go to next message
irremediable
Messages: 38
Registered: December 2007
Member

Hello!
Could anybody please exaplain
why each of these operations
< or <= in "AND BF.DT_OPEN >= WH.DT_CLOSE"
dramatically slows down a query, whereas
any other operation like <>, > or >= doesnt
halt the query!?

SELECT BF.ACC_CHARGE_PRC,BF.CODE_EVENT,BF.DATE_CHARGE_PRC,WH.DT_CLOSE,BF.DT_OPEN,BF.ID_ACCOUNT,
BF.ID_CONOPER,BF.ID_CONTRACTS,BF.ID_FILE,BF.ID_FINSTR,BF.ID_ORD_LIAB,BF.ID_TACC,BF.ID_TREST,BF.SALDOPRCMAIN,BF.SALDOPRCOVN,
BF.SALDOPRCOVPRC,BF.SUM_ALL_CALC,BF.SUM_CHARGE_PRC,BF.SUM_PRC_M_DELAY,BF.SUM_PRC_M_PRCD,BF.SYSMOMENT,SYSDATE 
FROM DWH.FCT_CHARGE_PRC WH, LDR_BF.FCT_CHARGE_PRC BF WHERE 
WH.ID_ORD_LIAB = BF.ID_ORD_LIAB 
AND WH.ID_CONTRACTS = BF.ID_CONTRACTS 
AND WH.ID_FINSTR = BF.ID_FINSTR 
AND WH.ID_CONOPER = BF.ID_CONOPER 
AND WH.ID_TACC = BF.ID_TACC 
AND BF.DT_OPEN > WH.DT_OPEN 
AND BF.DT_OPEN >= WH.DT_CLOSE
AND BF.REC_STATUS IN(0)
 
Re: Single operation slows down a query [message #304047 is a reply to message #303953] Mon, 03 March 2008 19:59 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Post the Explain Plan for a slow version and one for the fast version.

One of four things is probably happening:
- The <= query is using an index and the other is (wisely) not. The index is a bad choice.
- The <= query is NOT using an index and the other IS (and it is a good index to use.
- Both queries are using the same index, but there are heaps more candidate matches to scan for <=.
- The plans are completely and utterly different.

Ross Leishman
Re: Single operation slows down a query [message #304081 is a reply to message #303953] Tue, 04 March 2008 00:04 Go to previous messageGo to next message
irremediable
Messages: 38
Registered: December 2007
Member
Here is the exaplin plan and it is the same for both
versions of the query.

  SELECT STATEMENT 
    DWH.FCT_CHARGE_PRC TABLE ACCESS [BY INDEX ROWID] 
     NESTED LOOPS 
      LDR_BF.FCT_CHARGE_PRC TABLE ACCESS [FULL] 
      DWH.AK_CHARGE_PRC_FCT_CHAR INDEX [RANGE SCAN] 
  


Seems this is the reason
" Both queries are using the same index, but there are heaps more candidate matches to scan for <=."
isnt it?
Re: Single operation slows down a query [message #304325 is a reply to message #304081] Tue, 04 March 2008 20:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Trace both of them and post the Tkprof output.
Re: Single operation slows down a query [message #304339 is a reply to message #303953] Tue, 04 March 2008 23:25 Go to previous message
irremediable
Messages: 38
Registered: December 2007
Member



It works.
I gathered statistics on the LDR_BF.FCT_CHARGE_PRC
and it woked fine.
Thank you.
Previous Topic: Can you fix this query?
Next Topic: query hangs on order by
Goto Forum:
  


Current Time: Sat Dec 03 17:47:53 CST 2016

Total time taken to generate the page: 0.04267 seconds