Home » SQL & PL/SQL » SQL & PL/SQL » table UPDATE with ANALYTIC function (ORA 10.2.0.1.0)
table UPDATE with ANALYTIC function [message #350495] Thu, 25 September 2008 03:50 Go to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
have to update a table with an analytic function like in the example code the row ORDN with the order in IDAT.
The UPDATE ist very slow, found a bypass with MERGE here in the forum, that runs "fast".
SET TIMING ON;

DROP TABLE TEST;

CREATE TABLE TEST AS SELECT 0 ordn, SYSDATE+ROUND(DBMS_RANDOM.VALUE(1,90),3) idat
   FROM DUAL CONNECT BY LEVEL<=2000;

--with UPDATE
UPDATE TEST
   SET ordn = 
     (SELECT dr FROM 
       (SELECT DENSE_RANK() OVER(ORDER BY idat) dr, ROWID rid FROM TEST SOURCE)
      WHERE TEST.ROWID = rid);
   
--with MERGE
--since ORACLE 10 possible without merge_insert_clause
MERGE INTO TEST
   USING (SELECT DENSE_RANK() OVER(ORDER BY idat) dr, ROWID rid FROM TEST) SOURCE
   ON (TEST.ROWID = SOURCE.rid) 
   WHEN MATCHED THEN UPDATE SET TEST.ordn = SOURCE.dr;


The UPDATE needs
Elapsed: 00:00:07:14
The MERGE only
Elapsed: 00:00:00:04

I'm wondering is there a way to use only "straight" UPDATE ?
Re: table UPDATE with ANALYTIC function [message #350507 is a reply to message #350495] Thu, 25 September 2008 04:30 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
A really interesting point. I've never considered the merge operation as useful and I always found the update statement very limited in cases like this. I think I that merge statement may cover that limit.

I really appreciated your post.

Thanks

Bye Alessandro

[Updated on: Thu, 25 September 2008 04:31]

Report message to a moderator

Re: table UPDATE with ANALYTIC function [message #350511 is a reply to message #350495] Thu, 25 September 2008 04:38 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you do the operations the other way round, you'll see that part of the speed fo the Merge is that the update is reading the data from disk, and the Merge is reading it again, from memory this time.
Een so, I get the Merge to be faster both ways round.

I can't think of a way to use Update to be as fast as merge in this case - you can't use an Update View here because of the analytic function.
Re: table UPDATE with ANALYTIC function [message #350530 is a reply to message #350511] Thu, 25 September 2008 05:28 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Take a look at the section on Updateable Join Views in this article.

The method shown demonstrates the problem with non-key-preserved joins and proposes a work-around using a GTT. There is another work-around that uses an undocumented feature of Oracle called "Bypass Updateable Join View Constraint". Since you should never use undocumented features, I will not describe it further.


Ross Leishman
Re: table UPDATE with ANALYTIC function [message #350551 is a reply to message #350530] Thu, 25 September 2008 06:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I cant' get an Update Join View to work for this query even with the hint tha shall not be named.
Re: table UPDATE with ANALYTIC function [message #350555 is a reply to message #350530] Thu, 25 September 2008 07:02 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
thanks for the feedback and esp. to @rleishman for the great article.
Checked the GTT version too (for 5000 rows)
CREATE GLOBAL TEMPORARY TABLE test_upd (
        dr,
        rid
) ON COMMIT PRESERVE ROWS
AS
  SELECT DENSE_RANK() OVER(ORDER BY idat) dr, ROWID rid FROM TEST;


UPDATE TEST
   SET ordn = (SELECT dr FROM test_upd WHERE TEST.ROWID = rid);

here the results:
UPDATE
00:00:37.92
MERGE
00:00:00.09
GTT CREATE
00:00:00.04
GTT UPDATE
00:00:04.59
So I'd use the underestimated MERGE in future Cool
Re: table UPDATE with ANALYTIC function [message #350556 is a reply to message #350555] Thu, 25 September 2008 07:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're still getting an artificially low time for the merge, as all the blocks it needs are already in the cache.

Try repeating your timings with the Merge first, and you'll see a different picture. Merge will still be wuicker, but not by as much.
Re: table UPDATE with ANALYTIC function [message #350567 is a reply to message #350556] Thu, 25 September 2008 07:56 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
JRowbottom wrote on Thu, 25 September 2008 14:12
You're still getting an artificially low time for the merge, as all the blocks it needs are already in the cache.


You probably didn't understand why merge does it better.

Look here.

SQL> set autotrace on
SQL>
SQL>
SQL>
SQL>
SQL> MERGE INTO TEST
  2     USING (SELECT DENSE_RANK() OVER(ORDER BY idat) dr, ROWID rid FROM TEST) SOURCE
  3     ON (TEST.ROWID = SOURCE.rid)
  4     WHEN MATCHED THEN UPDATE SET TEST.ordn = SOURCE.dr
  5  /

2000 rows merged.


Execution Plan
----------------------------------------------------------
Plan hash value: 2342969278

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | MERGE STATEMENT        |      |  2000 | 30000 |     8  (25)| 00:00:01 |
|   1 |  MERGE                 | TEST |       |       |            |          |
|   2 |   VIEW                 |      |       |       |            |          |
|*  3 |    HASH JOIN           |      |  2000 | 70000 |     8  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL  | TEST |  2000 | 20000 |     3   (0)| 00:00:01 |
|   5 |     VIEW               |      |  2000 | 50000 |     4  (25)| 00:00:01 |
|   6 |      WINDOW SORT       |      |  2000 | 20000 |     4  (25)| 00:00:01 |
|   7 |       TABLE ACCESS FULL| TEST |  2000 | 20000 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("TEST".ROWID="SOURCE"."RID")


Statistics
----------------------------------------------------------
          0  recursive calls
       2297  db block gets
         35  consistent gets
          0  physical reads
     561776  redo size
        537  bytes sent via SQL*Net to client
        694  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
          2  sorts (memory)<----------------------------------THIS ONE IS IMPORTANT
          0  sorts (disk)
       2000  rows processed

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> DROP TABLE TEST purge
  2  /

Table dropped.

SQL>
SQL> CREATE TABLE TEST AS SELECT 0 ordn, SYSDATE+ROUND(DBMS_RANDOM.VALUE(1,90),3) idat
  2     FROM DUAL CONNECT BY LEVEL<=2000
  3  /

Table created.

SQL> UPDATE TEST
  2     SET ordn = (SELECT dr FROM
  3         (SELECT DENSE_RANK() OVER(ORDER BY idat) dr, ROWID rid FROM TEST SOURCE)
  4        WHERE TEST.ROWID = rid)
  5  /

2000 rows updated.


Execution Plan
----------------------------------------------------------
Plan hash value: 2103161032

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | UPDATE STATEMENT     |      |  2000 | 50000 |     5   (0)| 00:00:01 |
|   1 |  UPDATE              | TEST |       |       |            |          |
|   2 |   TABLE ACCESS FULL  | TEST |  2000 | 50000 |     5   (0)| 00:00:01 |
|*  3 |   VIEW               |      |  2000 | 50000 |     6  (17)| 00:00:01 |
|   4 |    WINDOW SORT       |      |  2000 | 42000 |     6  (17)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| TEST |  2000 | 42000 |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("RID"=:B1)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
        117  recursive calls
       4561  db block gets
      28352  consistent gets
          5  physical reads
    1040728  redo size
        536  bytes sent via SQL*Net to client
        662  bytes received via SQL*Net from client
          6  SQL*Net roundtrips to/from client
       2002  sorts (memory)<----------------------------------THIS ONE IS IMPORTANT
          0  sorts (disk)
       2000  rows processed

SQL>


You can notice (I based it on the number of sorts) that with the merge operator the query with analytics is executed only once and all its results are used for the update, while with a classic update we had an execution of that query once for each row to update.

So the update is slower because it executes
SELECT dr 
FROM (
		SELECT DENSE_RANK() OVER(ORDER BY idat) dr, ROWID rid 
		FROM TEST SOURCE
	)
WHERE TEST.ROWID = rid


2000 times while for the merge it is enough to execute

SELECT DENSE_RANK() OVER(ORDER BY idat) dr, ROWID rid 
FROM TEST


only once.

Bye Alessandro
Re: table UPDATE with ANALYTIC function [message #350570 is a reply to message #350555] Thu, 25 September 2008 08:02 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
_jum wrote on Thu, 25 September 2008 22:02
here the results:
UPDATE
00:00:37.92
MERGE
00:00:00.09
GTT CREATE
00:00:00.04
GTT UPDATE
00:00:04.59
So I'd use the underestimated MERGE in future Cool

The GTT update looks a little overstated to me - suspect it was using a sub-optimal plan.

The point is valid though - MERGE is the best way to achieve this.

@JR, I haven't done it for a while, but I have had the hint-that-shall-not-be-named working in the past - even as recently as 10.1 I think. PM me the SQL and error if you are still interested.

Ross Leishman
Re: table UPDATE with ANALYTIC function [message #350572 is a reply to message #350570] Thu, 25 September 2008 08:08 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
rleishman wrote on Thu, 25 September 2008 15:02

the hint-that-shall-not-be-named working in the past



That's why I was getting ORA-01732: data manipulation operation not legal on this view on my 10.2.0.4.

Bye Alessandro

Previous Topic: SP : expression can't be used as assignment target
Next Topic: Data Pulling
Goto Forum:
  


Current Time: Fri Dec 09 21:33:20 CST 2016

Total time taken to generate the page: 0.09773 seconds