Home » SQL & PL/SQL » SQL & PL/SQL » Problem with data (too long query execution)
Problem with data (too long query execution) [message #261211] Wed, 22 August 2007 02:59 Go to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
I've got such query:
SELECT KSTGR.SYMBOL GRUPA,SUM(EL.WARTOSC) SUMA, SUM(EL.UMORZENIE)+SUM(PLA.WARTOSC) UMORZENIE
FROM ST.ST_ELEMENT EL, ST.ST_SRODEK SR, ST.ST_SLOWNIK_KST KST, ST.ST_SLOWNIK_KST KSTGR,
ST.ST_PLAN_AMORTYZACJI PLA
WHERE EL.SRODEK_ID = SR.SRODEK_ID
AND PLA.SRODEK_ID = SR.SRODEK_ID
AND SR.KST = KST.SYMBOL AND INSTR(:grupa,SUBSTR(KST.SYMBOL,1,1)) <> 0
AND SUBSTR(KST.SYMBOL,1,1) = KSTGR.SYMBOL
AND EL.STATUS = 'A'
AND PLA.RATA_DATA < TO_DATE('2180-08-22','YYYY-MM-DD')
AND PLA.RATA_STATUS = 'A'
GROUP BY KSTGR.SYMBOL

problem is with the line
AND PLA.RATA_DATA < TO_DATE('2180-08-22','YYYY-MM-DD')

I've got index created on RATA_DATA and without that line the execution is about half a second. With this line the query execution does not want to end. Data in column RATA_DATA are of the same format as 2180-08-22. Please help.
Re: Problem with data (too long query execution) [message #261215 is a reply to message #261211] Wed, 22 August 2007 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ What is the type of PLA.RATA_DATA?
2/ Get the execution plan for both statements and see what changed

Regards
Michel
Re: Problem with data (too long query execution) [message #261217 is a reply to message #261211] Wed, 22 August 2007 03:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
embopazb wrote on Wed, 22 August 2007 09:59
Data in column RATA_DATA are of the same format as 2180-08-22. Please help.

I was following you up to this point. You got me wondering: what data type is RATA_DATA? If it is a date, then your remark makes no sense. The DATE data type does not have a format. You format it for display purposes, but internally it is always stored the same way. If RATA_DATA is a VARCHAR2 then you'd have to rewrite your condition.

MHE
Re: Problem with data (too long query execution) [message #261218 is a reply to message #261211] Wed, 22 August 2007 03:09 Go to previous messageGo to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
1/Type is data.
2/How do I get an execution plan in Toad?
Re: Problem with data (too long query execution) [message #261221 is a reply to message #261211] Wed, 22 August 2007 03:12 Go to previous messageGo to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
Maaher thx...
Still I do not understand why it isn't working... I compare date to date so it should?
Re: Problem with data (too long query execution) [message #261226 is a reply to message #261211] Wed, 22 August 2007 03:18 Go to previous messageGo to next message
embopazb
Messages: 10
Registered: August 2007
Junior Member
SELECT PLA.RATA_DATA FROM ST.ST_PLAN_AMORTYZACJI PLA
WHERE PLA.RATA_DATA < TO_DATE('2180-08-22','YYYY-MM-DD')

this works really quickly...
Razz
Re: Problem with data (too long query execution) [message #261227 is a reply to message #261221] Wed, 22 August 2007 03:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
EDIT: I just realize you meant 'DATE'.

So, what does an explain plan of your query tell? In other words: follow Michel's advice.

SELECT   kstgr.symbol grupa
       , SUM (el.wartosc) suma
       , SUM (el.umorzenie) + SUM (pla.wartosc) umorzenie
FROM     st.st_element el
       , st.st_srodek sr
       , st.st_slownik_kst kst
       , st.st_slownik_kst kstgr
       , st.st_plan_amortyzacji pla
WHERE    el.srodek_id = sr.srodek_id
AND      pla.srodek_id = sr.srodek_id
AND      sr.kst = kst.symbol
AND      INSTR (:grupa, SUBSTR (kst.symbol, 1, 1)) <> 0
AND      SUBSTR (kst.symbol, 1, 1) = kstgr.symbol
AND      el.status = 'A'
AND      pla.rata_data < TO_DATE ('2180-08-22', 'YYYY-MM-DD')
AND      pla.rata_status = 'A'
GROUP BY kstgr.symbol


MHE

[Updated on: Wed, 22 August 2007 03:27]

Report message to a moderator

Re: Problem with data (too long query execution) [message #261244 is a reply to message #261227] Wed, 22 August 2007 03:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How would you think an index on a date column would help if you search for dates before the year 2180?
The vast majority of your data will be in this range, so you definitely do NOT want the optimizer to use this index!
Re: Problem with data (too long query execution) [message #261256 is a reply to message #261211] Wed, 22 August 2007 04:09 Go to previous messageGo to next message
darshanmeel
Messages: 44
Registered: June 2007
Location: India
Member
Hi

Please try pla.rata_data < '2180-08-22' alone.This migyht or might not help you.

Secodn thing you need to look on your explain plan both with rat_data line and without it and see what is the difference.Why it is taking so much time?

It would be great if you post both explain plan here.

Darshan Singh Meel
Re: Problem with data (too long query execution) [message #261261 is a reply to message #261256] Wed, 22 August 2007 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
pla.rata_data < '2180-08-22'

NEVER ever use that.
Always compare data of same type: date with date, string with string.

For the rest of your post, this has already been asked twice.

Regards
Michel
Re: Problem with data (too long query execution) [message #261421 is a reply to message #261211] Wed, 22 August 2007 09:42 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
I'm just curious as to what the significance of August 22nd, 2180 is other than being 173 years from today's date.
Re: Problem with data (too long query execution) [message #261472 is a reply to message #261421] Wed, 22 August 2007 12:37 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Could be a fake value representing "we don't know" or something. If 90% of the rows has this value, than an index would be very helpful as long as you have histograms on it (if not, the optimizer will think the data is evenly spread between the first date in this column up till 173 years from now, that is sort of funny when you think of it, poor optimizer...).

I do realize it's no use trying to "crystal ball" about the requirements and actual data in the OP's table... So it's just a suggestion.
Previous Topic: Changing Character Sets in PL/SQL
Next Topic: SQL Help
Goto Forum:
  


Current Time: Sun Dec 04 06:20:33 CST 2016

Total time taken to generate the page: 0.11417 seconds