Home » RDBMS Server » Performance Tuning » Issue updating a partitioned table (Oracle 10 g, Windows XP)
Issue updating a partitioned table [message #283672] Tue, 27 November 2007 22:58 Go to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
We have a table that is list partitioned on column (YEAR_QUARTER)

I run an update statment like this.

Update EDW.INVOICE_FACT SET GO_TO_MARKET_KEY =1 
where  OPPORTUNITY_NO ='OPP-33359' 


OPPORTUNITY_NO is indexed. But the explain plan shows that a full table scan is being done!

 
Plan	Obj_name	Obj_type	Byte	Cost	Cardinality	CPU COST	IO COST	Acces pred	Filter pred	Partition Start	Part stop
Update Statement			17,884.00	26,533.00	1,052.00	5,654,592,556.00	26,167.00				
  1.1 Update	INVOICE_FACT										
    2.1 Partition List (All)			17,884.00	26,533.00	1,052.00	5,654,592,556.00	26,167.00			1	28
      3.1 Table Access (Full)	INVOICE_FACT	TABLE	17,884.00	26,533.00	1,052.00	5,654,592,556.00	26,167.00		"OPPORTUNITY_NO"='OPP-33359'	1	28


What can I do to force the usage of the index? Is there something that I need to do with respect to the partition?

Regards
Prem
Re: Issue updating a partitioned table [message #283675 is a reply to message #283672] Tue, 27 November 2007 23:03 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member

>Is there something that I need to do with respect to the partition?
Probably not.

>What can I do to force the usage of the index?
Possibly nothing.

What datatype is OPPORTUNITY_NO?

How many rows exist in this table?

How many rows get selected for UPDATE?
Re: Issue updating a partitioned table [message #283685 is a reply to message #283675] Tue, 27 November 2007 23:51 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
Thanks for the reply.

What datatype is OPPORTUNITY_NO?
VARCHAR2(30 BYTE)

How many rows exist in this table?
4.8 Million

How many rows get selected for UPDATE?
About 0.5 Million.

Is that helpful?

regards
Prem
Re: Issue updating a partitioned table [message #283688 is a reply to message #283672] Tue, 27 November 2007 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Are statistics current for both table & index?
Re: Issue updating a partitioned table [message #283690 is a reply to message #283688] Wed, 28 November 2007 00:02 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
If you mean if the tables have been analyzed, I guess yes. And should be within the past 3 weeks.

But the fact I forgot to add is that the index on OPPORTUNITY_NO was created only today by me.

Does that suggest anything to you?

Thanks a million for the help. Really appreciate it.

regards
Prem
Re: Issue updating a partitioned table [message #283692 is a reply to message #283672] Wed, 28 November 2007 00:08 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>But the fact I forgot to add is that the index on OPPORTUNITY_NO was created only today by me.
>Does that suggest anything to you?
I suspect Oracle does not know enough to use the index; assuming the use of the index would be beficial.

What happens after gathering statistics on the index?

Re: Issue updating a partitioned table [message #283710 is a reply to message #283692] Wed, 28 November 2007 00:37 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi,
Well, I wish I could do it now..but its night here and I can try this only tomorrow in office.

Is there something specific that you suggest?
Like performing an analyze on the table?

Thanks for your suggestion. It helps me a lot.
regards
Prem
Re: Issue updating a partitioned table [message #283717 is a reply to message #283710] Wed, 28 November 2007 00:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
0.5 mill of 5m rows to be updated. That's 10% of the table. Oracle has correctly identified that the index is useless. If you force this query to use the index it will probably be SLOWER. Not much slower, but in the unlikely event that it is faster, it won't be by very much at all.

I know you don't believe me, so try it for yourself:

UPDATE /*+ INDEX(EDW.INVOICE_FACT)*/ EDW.INVOICE_FACT 
SET GO_TO_MARKET_KEY =1 
WHERE  OPPORTUNITY_NO ='OPP-33359' 


Believe it or not, the fastest way to do this is to rebuild the table.

CREATE TABLE new_table AS
SELECT .....
,      DECODE(opportunity_no, 'OPP-33359', 1, GO_TO_MARKET_KEY) AS GO_TO_MARKET_KEY
,      .....
FROM   EDW.INVOICE_FACT


Then drop the old table, rename the new, create indexes and constraints.

Obviously its a little more complicated because you have partitions, but the same principle applies.

Ross Leishman
Re: Issue updating a partitioned table [message #283766 is a reply to message #283717] Wed, 28 November 2007 01:58 Go to previous messageGo to next message
kpremsagar
Messages: 26
Registered: June 2007
Junior Member
Hi Ross,
SOmething interesting to me!!

I was under the impression that if it fetches 20% records or lesser of the table, its better to use index.

And that thought now needs to be revisited!

Some new learnt today. Thanks
Regards
Prem
Re: Issue updating a partitioned table [message #283769 is a reply to message #283766] Wed, 28 November 2007 02:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You will have advantage to read How to Identify Performance Problem and Bottleneck, especially the links on indexes in the second post.

Regards
Michel
Re: Issue updating a partitioned table [message #284150 is a reply to message #283769] Thu, 29 November 2007 00:56 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is no magic percentage. This is the third greatest religious war in IT (after indentation (tab or space) and bracing (same or next line) standards).

It varies with the size, width, and content of the table and the index in question, the quality of your disk, the physical distribution of data over the disk, memory and CPU, the load on the machine, and probably the shade of your Great-Aunt's facial hair.

In practice, I find that it rarely is outside the 1% to 10% range regardless of the other factors.

Ross Leishman
Previous Topic: Tuning look-up tables : Suggestions needed
Next Topic: how to increase the performance
Goto Forum:
  


Current Time: Wed Dec 07 06:54:30 CST 2016

Total time taken to generate the page: 0.05849 seconds