Home » SQL & PL/SQL » SQL & PL/SQL » Problem with updating a table
Problem with updating a table [message #346038] Fri, 05 September 2008 12:56 Go to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
I have two tables A(with 800K records) and B(with 560K records).
None of them have any index.
Now i want to update a flag in A to 'Y' based on all the records present in B i.e if a record in A is present in B..then the flag in A should be set to 'Y'.
The only common col between them is order_id.
Now ,can any one suggest me a way of doing this in minimum possible time.
Any inputs would be appreciated.
Re: Problem with updating a table [message #346040 is a reply to message #346038] Fri, 05 September 2008 13:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
update ... where exists ...

But you should add indexes.

Regards
Michel

[Updated on: Fri, 05 September 2008 13:01]

Report message to a moderator

Re: Problem with updating a table [message #346042 is a reply to message #346040] Fri, 05 September 2008 13:06 Go to previous messageGo to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
actually i had added index..bt in d plan thr ws index full scan..so i wanted to do away with index..
what else can be done...?
Re: Problem with updating a table [message #346043 is a reply to message #346038] Fri, 05 September 2008 13:07 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>what else can be done...?

http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Re: Problem with updating a table [message #346044 is a reply to message #346042] Fri, 05 September 2008 13:16 Go to previous messageGo to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
Ok,
Now I actually had an index on the tables.
But the explain plan was showing that it was doing a index full scan. I did not want that.So I dropped it.
I am just wondering in what other ways it can be done?And even if i add an index how can I avoid index full scan?
Was the dropping of the index not a good idea?
Note:The order_id value is unique in both the tables.
Re: Problem with updating a table [message #346045 is a reply to message #346038] Fri, 05 September 2008 13:21 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

>I did not want that.
Why not?

about how many rows in A will be changed?
Re: Problem with updating a table [message #346046 is a reply to message #346045] Fri, 05 September 2008 13:25 Go to previous messageGo to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
All the rows in A should get updated to Y.Which means all 560K records in B which are present in A should get updated.
So it is 560K records.
Re: Problem with updating a table [message #346047 is a reply to message #346046] Fri, 05 September 2008 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't follow guidelines and don't want to post the requested information we can't help.

Regards
Michel
Re: Problem with updating a table [message #346098 is a reply to message #346038] Fri, 05 September 2008 23:54 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
Hi Aditi2008 -

I want to emphasize Antacedent's question concerning your statement:

Quote:

But the explain plan was showing that it was doing a index full scan. I did not want that.



WHY??

You are asking for instruction on process efficiency yet you are demonstrating in that statement that you have a pre-established conclusion that can only be validated after you have the solution.

I want to harp on this because I have learned so much from the s knowledge on this site from the experts critiques that I needed a change in how I approached problems like this.

I believe my first bright light bulb of insight came from rleishman; which, I have implemented into my thought process as doctrine on SQL performance:

(in my interpretation)

There are no absolutes for best approach except one - use the actual performance time of the query to measure what approach is best. While that may sound facetitious, it is not.

It is the only thing I can trust as a constant to benchmark success. Therfore my first suggestion is to execute the query as Oracle intended - with the fast full scan.

You state that you expect all rows to be updated so Oracle's decision seems quite logical -

It has an index it can read directly to evaluate the condition, It needs the majority of table B to do so,
It decided to therefore retrieve those index values in large
multi-block chunks instead of nested looping through it one block at a time.

If Oracle has fresh statistics on the table then I would
put my money on its choice being correct. That would be
a priority to check.

I'm just really trying to hit home that the quickest path to
the correct answer is to "try". Give Oracle the benefit of the doubt first (with analyzed table data).

My lesson on this came in discussion with Ross on plan cost where I was trying to "understand the magic bullet" behind it -
a bullet that I realized I was searching for across a wide spectrum
of problems - "What do I need to understand to find the correct factors to weight and compare that will always determine how to best do this or that...."

It quickly became apparent to me that I could become more intuitive in my decision making and more efficient in my problem resolution if I experimented first, looked at the results,
and then studied material involved in the access paths that
gave the best (or worst) performance to understand the "Why",
seeking guidance at that point.

Here are some queries to start testing performance with using SELECTS to identify the matching records. Just for curiosity
in comparing access paths with response time.


---Let Oracle go first
SELECT /*+ CHOOSE */ 
      A.order_id
FROM TABLE_A A, 
     TABLE_B B 
WHERE A.order_id = B.order_id;

---Repeat above with /*+ USE_HASH(a b) */

SELECT A.order_id from TABLE_A A 
   WHERE EXISTS (SELECT 1 FROM TABLE_B B 
                          WHERE A.order_id = 
                                B.order_id);

SELECT   A.order_id from TABLE_A A 
   WHERE A.order_id IN 
    (SELECT B.order_id from TABLE_B B);

SELECT A.order_id from TABLE_A A 
  WHERE A.order_id NOT IN
((SELECT order_id from TABLE_A where order_id is not null)
minus
 (SELECT order_id from TABLE_B)); --can't be null if 
                                 --Oracle could use a FFS
                                 -- on the index



Again I just want to encourage you to examine results from
hands on testing.

Your update statement is simple enough to start with Oracle's
advice.

Then - research different Index access methods and make it
prove that it's FFS is optimal (by forcing other scans).

Best Regards,
Harry


Re: Problem with updating a table [message #346104 is a reply to message #346098] Sat, 06 September 2008 00:54 Go to previous messageGo to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
Hi Harry,
I guess you are right.I did have pre-established conclusions.
Thanks a lot for your valuable inputs.
I will certainly try and test the performance.
Re: Problem with updating a table [message #346336 is a reply to message #346104] Mon, 08 September 2008 04:36 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you want to do the update without indexes, AND if you can guarantee that every record in table A maps to AT MOST one record in table B, there is quite an efficient way to do it.
create table test_0077 (col_1 number, col_2 varchar2(10));

create table test_0078 (col_1 number, col_2 varchar2(10));

insert into test_0077  select level,'AAAAA' from dual connect by level <=20;

insert into test_0078 select level*2,'BBBBB' from dual connect by level <=10;

commit;

update (select t1.col_1,t1.col_2 old_c2 ,t2.col_2 new_c2
        from   test_0077 t1, test_0078 t2
        where  t1.col_1 = t2.col_1)
set     old_c2 = new_c2;

ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

update (select /*+ bypass_ujvc */ t1.col_1,t1.col_2 old_c2 ,t2.col_2 new_c2
        from   test_0077 t1, test_0078 t2
        where  t1.col_1 = t2.col_1)
set     old_c2 = new_c2;

10 rows updated.

select * from test_0077;

     COL_1 COL_2
---------- ----------
         1 AAAAA
         2 BBBBB
         3 AAAAA
         4 BBBBB
         5 AAAAA
         6 BBBBB
         7 AAAAA
         8 BBBBB
         9 AAAAA
        10 BBBBB
        11 AAAAA

     COL_1 COL_2
---------- ----------
        12 BBBBB
        13 AAAAA
        14 BBBBB
        15 AAAAA
        16 BBBBB
        17 AAAAA
        18 BBBBB
        19 AAAAA
        20 BBBBB

20 rows selected.

{fix code tags}

[Updated on: Mon, 08 September 2008 04:43]

Report message to a moderator

Re: Problem with updating a table [message #346459 is a reply to message #346336] Mon, 08 September 2008 11:04 Go to previous messageGo to next message
aditi2008
Messages: 9
Registered: May 2008
Junior Member
Thanks everyone!
I am done with my work! Smile
Re: Problem with updating a table [message #346470 is a reply to message #346336] Mon, 08 September 2008 12:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
AND if you can guarantee that every record in table A maps to AT MOST one record in table B,

Do you think each one that will read this topic will understand what you said?
I don't think it is wise to publish an undocumented hint (even with a red bold size 4 and blinking warning) that bypasses securities, can lead to underterministic results and is not supported.
In your example, MERGE can legally do the trick (thanks for the test case):
SQL> merge into test_0077 t7 using test_0078 t8 on (t8.col_1=t7.col_1)
  2  when matched then update set col_2=t8.col_2
  3  /

10 rows merged.

SQL> select * from test_0077;
     COL_1 COL_2
---------- ----------
         1 AAAAA
         2 BBBBB
         3 AAAAA
         4 BBBBB
         5 AAAAA
         6 BBBBB
         7 AAAAA
         8 BBBBB
         9 AAAAA
        10 BBBBB
        11 AAAAA
        12 BBBBB
        13 AAAAA
        14 BBBBB
        15 AAAAA
        16 BBBBB
        17 AAAAA
        18 BBBBB
        19 AAAAA
        20 BBBBB

20 rows selected.

Regards
Michel
Re: Problem with updating a table [message #346612 is a reply to message #346470] Tue, 09 September 2008 02:34 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not completely undocumented - Tom Kyte broke his silence on the subject a couple of years ago after it got used in some Oracle publications.

Admitedly, Merge is a superior solution, in that it wil explicitly warn you when you have more than one value for a given row.
Re: Problem with updating a table [message #346623 is a reply to message #346612] Tue, 09 September 2008 02:53 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes he posted about this but here's one his warning:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1197999096334#17899534514537
If the table is in fact NOT KEY PRESERVED, having the same exact inputs can and will lead to
different results based on query plans, physical order of rows, the alignment of the sun with pluto
and other factors.

and
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1197999096334#17981809192550
That is why BYPASS_UJVC is going right back into my list
of EVIL hints, not to be used.

Regards
Michel

Previous Topic: How to convert select stmt into update
Next Topic: how many insert triggers can a table have?
Goto Forum:
  


Current Time: Fri Dec 02 12:34:54 CST 2016

Total time taken to generate the page: 0.10595 seconds