Home » SQL & PL/SQL » SQL & PL/SQL » Inequality slowing down UPDATE (Oracle 10g)
Inequality slowing down UPDATE [message #432973] Fri, 27 November 2009 16:12 Go to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
Hi,

just wondering if anybody has any ideas on this.

I have a straightforward UPDATE statement, which is running VERY slowly. The Execution plan says that it is using the relevant index, which I thought wouldn't work when there is an inequality involved, but hey, I'm not an expert.

The inner table is very big (6 million rows). When I take out the inequality, the update takes < 1 second. When I include it, it takes 15 mins.


UPDATE US_PERFORMANCE_INFO p
SET form_source = 1
WHERE p.race_date = '7 NOV 2009'
AND p.horse_name = 'ZENYATTA'
AND EXISTS 
(
SELECT 1
FROM US_PERFORMANCE p2
WHERE p2.horse_code = p.horse_code
AND p2.race_date < p.race_date
)


Thx,

Guy
Re: Inequality slowing down UPDATE [message #432974 is a reply to message #432973] Fri, 27 November 2009 16:20 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

Please post EXPLAIN PLAN for query.

UPDATE us_performance_info p
SET    form_source = 1
WHERE  p.race_date = '7 NOV 2009'
       AND p.horse_name = 'ZENYATTA'
       AND EXISTS (SELECT 1
                   FROM   us_performance p2
                   WHERE  p2.horse_code = p.horse_code
                          AND p2.race_date < p.race_date) 

Are all columns in the WHERE clause indexed?

In Oracle characters between single quote marks are STRINGS
'this is a string, 2009-10-02, not a Date'
When you need to convert STRING to DATE use TO_DATE() function.
Re: Inequality slowing down UPDATE [message #432975 is a reply to message #432974] Fri, 27 November 2009 16:27 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
Hi, thx for the quick reply.

Yes, they are all indexed.

I tried the TO_DATE(), but it's still running, it doesn't seem to help.

The funny thing is, if I take out the DATE inequality, and add the HINT to do a full table scan, it doesn't take very long either.
Re: Inequality slowing down UPDATE [message #432976 is a reply to message #432975] Fri, 27 November 2009 18:05 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
one of two realities exist.
Either the SQL is as efficient as it can be or something can be changed to make it run faster.

You've provided NOTHING meaningful to provide you with anything more than idle speculation.

You were requested to provide an EXPLAIN PLAN; which you declined to produce.

We don't have your tables.
We don't have your data.
We don't know your indexes.

using sqlplus do the following:

SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
-- run UPDATE

CUT results & PASTE results back here

[Updated on: Fri, 27 November 2009 18:05]

Report message to a moderator

Re: Inequality slowing down UPDATE [message #432992 is a reply to message #432975] Sat, 28 November 2009 04:51 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
- What happens if you replace the p.race_date with a to_date('07-11-2009', 'dd-mm-yyyy')?

- Is horse_code indexed (as 1st column in an index)?
- How many horse_codes are there in us_performance?
- Are the stats up to date?
- What index is used for the inner query? It should probably not use the index on the race_date, since I bet at least 90% of the records have a race_date < 7 nov.
Re: Inequality slowing down UPDATE [message #433031 is a reply to message #432973] Sun, 29 November 2009 04:30 Go to previous messageGo to next message
manu_jariwala
Messages: 20
Registered: August 2005
Location: Surat
Junior Member

Hi,

Try like this,


UPDATE us_performance_info p SET form_source = 1
WHERE p.race_date = to_date('7-NOV-2009')
AND p.horse_name = 'ZENYATTA'
AND EXISTS (SELECT 1
FROM us_performance p2
WHERE p2.horse_code = p.horse_code
AND to_date(p2.race_date) <
to_date(p.race_date));

or

UPDATE us_performance_info p SET form_source = 1
WHERE p.race_date = trunc('7-NOV-2009')
AND p.horse_name = 'ZENYATTA'
AND EXISTS (SELECT 1
FROM us_performance p2
WHERE p2.horse_code = p.horse_code
AND trunc(p2.race_date) <
trunc(p.race_date));

Regards,
Manish Jariwala
Re: Inequality slowing down UPDATE [message #433034 is a reply to message #433031] Sun, 29 November 2009 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Fri, 27 November 2009 23:20
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

It seems you didn't follow this advice.
I add:
read "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: Inequality slowing down UPDATE [message #433042 is a reply to message #433031] Sun, 29 November 2009 07:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
manu_jariwala wrote on Sun, 29 November 2009 11:30
Hi,

Try like this,

UPDATE us_performance_info p  SET    form_source = 1
WHERE  p.race_date = to_date('7-NOV-2009')
       AND p.horse_name = 'ZENYATTA'
       AND EXISTS (SELECT 1
                   FROM   us_performance p2
                   WHERE  p2.horse_code = p.horse_code
                   AND to_date(p2.race_date) < 
                                        to_date(p.race_date)); 

Either p.race_date is a date, in which case you should not use to_date(p.race_date), or p.race_date is not a date, in which case you should not compare it to a to_date('7-NOV-2009')

In either case, you should not use to_date without a date format.
Re: Inequality slowing down UPDATE [message #433063 is a reply to message #432974] Sun, 29 November 2009 11:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
UPDATE us_performance_info p
SET    form_source = 1
WHERE  p.race_date = '7 NOV 2009'
       AND p.horse_name = 'ZENYATTA'
       AND EXISTS (SELECT 1
                   FROM   us_performance p2
                   WHERE  p2.horse_code = p.horse_code
                          AND p2.race_date < p.race_date) 

The following index would be best for this query's existential component.

us_performance(horse_code,race_date).

The idea is that your exists should be able to find one row fast without having to visit the us_performance table. Once the exists finds one row it will stop looking. But to find that row fast you need the right index and to not visit the underlying table the index is built on your index needs all columns referenced in the relevant query part.

Others would have told you this if you had listed the indexes on this table as was requested.

Additionally it looks like you are committing one of the ten sins of Oracle, implicit data type conversion. us_performance_info.RACE_DATE is I suspect a date? If so then why are you comparing it to character string? You should always use the TO_DATE function to convert your string to a date. If this code is generated by a tool then look into the tool for a parameter to set to make this happen. If this is code generated from a home grown program then fix the program to generate the correct sql.

Quote:
If RACE_DATE is not a date then fire your DBA/MODELER for being stupid enough to create a column named date that does not store a date.

Just so there is not confusion, this is a date

create table has_a_date (race_date date);
insert into has_a_date values (to_date('7 NOV 2009','dd-mon-rrrr'));

this is not a date.

create table not_a_date (race_date varchar2(30));
insert into not_a_date values ('7 NOV 2009');

Good luck, Kevin

[Updated on: Sun, 29 November 2009 11:49]

Report message to a moderator

Re: Inequality slowing down UPDATE [message #433193 is a reply to message #433063] Mon, 30 November 2009 10:34 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
Race_Date is a date. The update statement itself gets called by a SP that takes race_date as a date parameter.

It tried the TO_DATE() but it didn't seem to help.

Actually it looks like the problem occurs because Oracle is using an index that DOES HAVE race_date in it.

Frank suggested this, which is why played around with it, and it seems to be the key the problem. I can't understand why. Most of the dates are indeed before 7 Nov 2009, and there are about 2000 records for each race_date.
Re: Inequality slowing down UPDATE [message #433210 is a reply to message #432973] Mon, 30 November 2009 12:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
you may be diagnosing the wrong problem then.

The existintial query is best served to my mind by the index I provided.

Again however, you have not provided indexes for your two tables so at this point we can't really help you any more.

Kevin
Re: Inequality slowing down UPDATE [message #433497 is a reply to message #432973] Wed, 02 December 2009 09:57 Go to previous messageGo to next message
Bill B
Messages: 1458
Registered: December 2004
Senior Member
Try disabling the date index (assuming race_date is a trunc'ed date value)

UPDATE us_performance_info p
SET    form_source = 1
WHERE  p.race_date = '7 NOV 2009'
       AND p.horse_name = 'ZENYATTA'
       AND EXISTS (SELECT 1
                   FROM   us_performance p2
                   WHERE  p2.horse_code = p.horse_code
                          AND trunc(p2.race_date) < p.race_date)

Re: Inequality slowing down UPDATE [message #433501 is a reply to message #432973] Wed, 02 December 2009 10:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
What index?

I have seen no index.

We have repeatedly asked for the DDL that creates the indexes so we can review the definitions of indexes on these tables and have yet to receive anything.

OP, where are the table creates and index creates we have been asking for? I want to see the table definitions and all index definitions on these tables as well as the query plan.

So far I am impressed with this thread. I am impressed by how much performance fixing suggestions have been generated without having the basic information necessary to make these suggestions.

My suggestion is that we make no more suggestions until the OP provides some this info.

I am getting impatient. Kevin
Re: Inequality slowing down UPDATE [message #433505 is a reply to message #432973] Wed, 02 December 2009 10:28 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
As mentioned, I deleted the one that I was using before. I tried adding a hint for the index without RACE_DATE but Oracle wouldn't have it and kept using the index with RACE_DATE in it.

This is the one that is use now.

CREATE INDEX GMR.US_PERFORMANCE_INFO_IX2 ON GMR.US_PERFORMANCE_INFO
(HORSE_CODE)
LOGGING
TABLESPACE GMR
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          80M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;





It's much better now. I don't fully understand the reason - but poster Frank clearly did.

(I use horse_code in the real db, instead of horse_name, that was just there for illustration purposes)

[Updated on: Wed, 02 December 2009 10:32]

Report message to a moderator

Re: Inequality slowing down UPDATE [message #433515 is a reply to message #432973] Wed, 02 December 2009 11:02 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
OK so now we have all including you learned a few things. Let me recap them:

1) you posted fake code and expected people to figure out a real problem?

2) you did not provide the needed info and figured people could work without it?

Now for some more on my part...

You still do not have the right solution in my opinion.
You still have not posted the information I requested.

Here is a hint:

set linesize 80
set pagesize 999

desc GMR.US_PERFORMANCE_INFO
desc us_performance

set linesize 999

select table_Name,index_name,column_name
from all_ind_columns
where table_name in ('US_PERFORMANCE','US_PERFORMANCE_INFO')
and table_owner = 'GMR'
order by table_name,index_name,column_position
/

set autotrace on

run the problem query twice

send us the plan with its stats

You should put back the original indexes, collect statistics on the tables using CASCADE=>true, run your real query twice and send us the query plan and stats that autotrace produces from its second run. Then we can get you the right set of indexes maybe.

Quote:
Or you can just assume you have the right answer because it seems to work better though you have no clue why and neither does anyone else.

Please do not take my post here as my trying to put you down or be mean. In truth it is not your fault. The fault is with the people providing solutions here because we let this thread continue on its way without requiring you to provide what is really needed. This forum is here to help you and people will always be ready to help you. But as you can see, so far you have been several days with lots of posting, and you still don't have what you need in my opinion and the reason is the necessary information has not been provided. Indeed, once you do the things I have suggested above, you may infact figure it out for yourself what you need to do to fix your performance issue.

Another point to consider also is that OraFaq is about teaching. Your post will be used by others when they are looking to solve their performance problems that are index related. In order for this to work, we need your problem clearly articulated with the necessary info in place so that whatever solution is arrived at will make sense to others looking for that solution. AS you can see, this thread does not provide this value beacuse no-one who has participated in it can make a claim to have understood how your fix was arrived at or if it even works.

So, please provide what I have asked for and lets try to see if we can get you what you need, or explain why your current solution does the trick.

Kevin
Re: Inequality slowing down UPDATE [message #433517 is a reply to message #433515] Wed, 02 December 2009 11:26 Go to previous messageGo to next message
rolifantje
Messages: 18
Registered: September 2009
Junior Member
OK - I think you are being a bit harsh.

The code IS real, it's the SQL that I was focusing on myself. I run these updates for a full day (rather than one horse) on my live server, but I was using this very update statement to find a solution.

Also, I could never make you reproduce the actual problem because you would need a table with > 5 million rows.


I am aware that people will be reading this when they are googling for a solution to a similar problem. I have stated what seems to have fixed it - but I don't understand why it would. Frank made the suggestion, and he must have done so for a reason. I hope he can help me out here. If I knew why using an index with race_date is so slow, I would definitely have taken the time to explain it.

I will have to deal with the rest of your query tonight - I don't have access to that db right now.

[Updated on: Wed, 02 December 2009 11:27]

Report message to a moderator

Re: Inequality slowing down UPDATE [message #433521 is a reply to message #432973] Wed, 02 December 2009 12:27 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
I accept that I was tough with the last post.

I want your solution to have meaning for you and others. We will wait till you have access to the database and can post the details requested.

It has been my experience over the years that good solutions are solutions that we understand and solutions that we do not understand are solutions that will be broken again in a month. It is not an unreasonable thing to expect that we can understand why a fix actually works. I want to get you to that point or at the very least have some plausible reasons why.

Again, my disappointment is mostly with all of us here on OraFAQ who kept on tossing out ideas without actually looking at the details of the problem. For example, if RACE_DATE is the first column in your index, that would explain why it is slow and why removing it would help. But if HORSE_CODE is the first column in the index, then it is not clear why dropping RACE_DATE would result in such a huge difference. When you supply all table and index definitions we will have something to go on. When you supply the query plan we will see how the index is even being used.

Kevin
Previous Topic: Set Alert on a Table
Next Topic: HELP on How create a STORED PROCEDURE
Goto Forum:
  


Current Time: Thu Sep 29 19:15:40 CDT 2016

Total time taken to generate the page: 0.16584 seconds