| Issue updating a partitioned table [message #283672] |
Tue, 27 November 2007 22:58  |
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 #283690 is a reply to message #283688] |
Wed, 28 November 2007 00:02  |
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 #283710 is a reply to message #283692] |
Wed, 28 November 2007 00:37 |
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 |
rleishman
Messages: 3728
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 |
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 #284150 is a reply to message #283769] |
Thu, 29 November 2007 00:56 |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
