How to update a very lage table with a set of rows

How to update a very lage table with a set of rows

Post by m.. » Wed, 05 Nov 1997 04:00:00



I am working on a project for Oracle v8.0.3 on Windows NT.  One of
functions has to update a very large table witch has ten million rows
every half hour with about ten thousand rows which are stored in a
temporary table.
I tried update it row by row. The performance is very slow.
I also tried to use a SQL statement to update it. It always uses full
table scan on the big table instead of using index even though I
created primary key on it. The SQL statement looks like:

UPDATE bigtable BT
SET ( col1, col2, col3 )
=
(
SELECT col1, col2, col3
FROM  smalltmp tmp
WHERE BT.key1 = TMP.key1
  AND BT.key2 = TMP.key2
)

It took very very long time to be done because it used two full table
scan even they have indexes on the keys.

Can anybody help me to figure out how to avoid full table scan on the
big table and speed up the performance?

Thanks

Jianrong  M

 
 
 

How to update a very lage table with a set of rows

Post by terryg » Wed, 05 Nov 1997 04:00:00



> I am working on a project for Oracle v8.0.3 on Windows NT.  One of
> functions has to update a very large table witch has ten million rows
> every half hour with about ten thousand rows which are stored in a
> temporary table.
> I tried update it row by row. The performance is very slow.
> I also tried to use a SQL statement to update it. It always uses full
> table scan on the big table instead of using index even though I
> created primary key on it. The SQL statement looks like:

> It took very very long time to be done because it used two full table
> scan even they have indexes on the keys.

> Can anybody help me to figure out how to avoid full table scan on the
> big table and speed up the performance?

> Thanks

> Jianrong  M

Hello,
Generally speaking, a select or update statement
taht does not have a where clause implies that the
whole table is the target of the query or update.
Hence the full table scans.
Your update statement above does not have a where clause.
If you include one, say,
UPDATE bigtable BT
 SET ( col1, col2, col3 ) =
                          ( SELECT col1, col2, col3
                            FROM  smalltmp tmp
                            WHERE BT.key1 = TMP.key1
                            AND BT.key2 = TMP.key2
 )
WHERE BT.key1||BT.key2 in ( SELECT TMP.key1||TMP.key2
                            FROM smalltmp)

Then you should be able to get rid of the full table
scans.

The || may not be the best approach. I haven't done
this stuff in a while.  Play with it and I'm
sure you can speed things up.

You might want to try PL/SQL - use a cursor on the
smalltmp table and for each row update the big table.
That would likely give you a reasonable result.

Good luck,
Terry

 
 
 

How to update a very lage table with a set of rows

Post by Gerard H. Pill » Thu, 06 Nov 1997 04:00:00


WHERE (BT.key1,BT.key2) in ( SELECT TMP.key1, TMP.key2
                             FROM smalltmp)

Would be safer and immensely faster
(functions inhibit index usage)
--
------------
Kind reGards
     \ /   |
      X    |
     / \   x
     Gerard


Quote:> Hello,
> Generally speaking, a select or update statement
> taht does not have a where clause implies that the
> whole table is the target of the query or update.
> Hence the full table scans.
> Your update statement above does not have a where clause.
> If you include one, say,
> UPDATE bigtable BT
>  SET ( col1, col2, col3 ) =
>                           ( SELECT col1, col2, col3
>                             FROM  smalltmp tmp
>                             WHERE BT.key1 = TMP.key1
>                             AND BT.key2 = TMP.key2
>  )
> WHERE BT.key1||BT.key2 in ( SELECT TMP.key1||TMP.key2
>                             FROM smalltmp)

...>
> Good luck,
> Terry

 
 
 

How to update a very lage table with a set of rows

Post by Happl Oberli » Thu, 06 Nov 1997 04:00:00



> I am working on a project for Oracle v8.0.3 on Windows NT.  One of
> functions has to update a very large table witch has ten million rows
> every half hour with about ten thousand rows which are stored in a
> temporary table.
> [...]
> Can anybody help me to figure out how to avoid full table scan on the
> big table and speed up the performance?

Why not using a small PL/SQL-procedure like this one?

-- begin
declare
 cursor c_smalltmp is
   select key1, key2, col1, col2, col3
    from smalltmp;
begin
 for r_smalltmp in c_smalltmp loop
   update bigtable set
     col1 = r_smalltmp.col1,
     col2 = r_smalltmp.col2,
     col3 = r_smalltmp.col3
    where key1 = r_smalltmp.key1 and key2 = r_smalltmp.key2;
 end loop;
end;
/
-- end

Happl

 
 
 

How to update a very lage table with a set of rows

Post by Joseph D. Sumalba » Fri, 07 Nov 1997 04:00:00


Or better still

   where exists
       (select 'x'
        from small TMP
        where TMP.key1 = BT.key2
        and TMP.key2  = BT.key2
        and TMP.key3  = BT.key3)

(This works much better if small.KEY1=BT.key1
                           small.KEY2=BT.key2
                           small.KEY3=BT.key3 )

and you have an index for small (KEY1,KEY2,KEY3)
The second query needs not to access the table since
the query can find all the info from the index itself )

CIAO

Joseph Sumalbag


> WHERE (BT.key1,BT.key2) in ( SELECT TMP.key1, TMP.key2
>                              FROM smalltmp)

> Would be safer and immensely faster
> (functions inhibit index usage)
> --
> ------------
> Kind reGards
>      \ /   |
>       X    |
>      / \   x
>      Gerard


> > Hello,
> > Generally speaking, a select or update statement
> > taht does not have a where clause implies that the
> > whole table is the target of the query or update.
> > Hence the full table scans.
> > Your update statement above does not have a where clause.
> > If you include one, say,
> > UPDATE bigtable BT
> >  SET ( col1, col2, col3 ) =
> >                           ( SELECT col1, col2, col3
> >                             FROM  smalltmp tmp
> >                             WHERE BT.key1 = TMP.key1
> >                             AND BT.key2 = TMP.key2
> >  )
> > WHERE BT.key1||BT.key2 in ( SELECT TMP.key1||TMP.key2
> >                             FROM smalltmp)

> ...>
> > Good luck,
> > Terry

--
================================================================
============================
| Joseph Sumalbag                                                                          
|
| Oracle DBA                                                                                
|
|                                                                                          
|
| The opinions expressed above are my own and doesn't
necessarily                           |
|reflect the opinion of any of my client company or my employer.                            
|
================================================================
============================
 
 
 

How to update a very lage table with a set of rows

Post by terryg » Fri, 07 Nov 1997 04:00:00



> Or better still

>    where exists
>        (select 'x'
>         from small TMP
>         where TMP.key1 = BT.key2
>         and TMP.key2  = BT.key2
>         and TMP.key3  = BT.key3)

> (This works much better if small.KEY1=BT.key1
>                            small.KEY2=BT.key2
>                            small.KEY3=BT.key3 )

> and you have an index for small (KEY1,KEY2,KEY3)
> The second query needs not to access the table since
> the query can find all the info from the index itself )

> CIAO

> Joseph Sumalbag

But....
Wouldn't that where clause be evaluated for each row
of bigtable? After all, BT.key1 etc is defined on a
row by row basis for the subselect above.
Isn't it better to narrow the bigtable rowset first?
Cheers,
Terry
 
 
 

How to update a very lage table with a set of rows

Post by Noodl » Sat, 08 Nov 1997 04:00:00


In a completely differnt direction;

Why not use Export, truncate the temp table, and then Import ?  For better
 Export and Import performance, compile the EXP/IMP executables in single-task
 mode and use a direct Oracle connection ( No MTS stuff).   This solution would
 probably be better for a non-OLTP type of application due to the extensive IO
 for the index updates during the import.

Cliff

 
 
 

1. lage set intersecting (was: search engine text indexing?)

I guess I wasn't very specific with my requests for information on my query
about the implementation of search engine text indexing.

Its easy enough to take a gazillion documents, and then for each word, have
a Btree index to a large set of documents that contain that particular word.

Lets say someone wants to search for all the documents that contain: "cat
dog mouse".  Now, cat returns 1,836,939 documents, dog returns 3,670,789
documents, and mouse returns 251,590.  

Here's an example implementation:

One implementation of the set intersection would be to have the many large
sets of document ID's indexed in a BTree.  So in this example, there would
be 3 BTrees, one for cat, dog, and mouse.  One possible algorithm is to
iterate over every mouse document ID and look up each document ID in the cat
index to see if there is a match.  The remaining documents that succeeded
could be intersected with the dog index.

The above algorithm seems rather inefficient and there must be better ways
of implementing or structuring the data other than how I've described.  So
I'm primarily looking for indexing algorithms or structures, that are
designed primarily for being efficient at doing very large set
intersections.  I imagine that these large set intersections not only are
used heavily in optimizing text search engines, but in other non-text
related searches as well.

Ian

2. US-WA-Seattle Sr. P/A Consultant - Sybase

3. Replication problems with lage tables

4. no inserting using a cursor in SQL 7

5. Updating a Table by selecting row by row

6. PL/SQL

7. update from another table row by row

8. ASP.SQL Problems

9. Updating a row column of a table using values from another row column

10. Using a trigger to update a time field in a table when a row is updated

11. How to Update rows in a table from fields in second table

12. UPDATE MULTIPLE ROWS IN ONE TABLE OBTAINING SUMS FROM ANOTHER TABLE

13. how VB update Table A INNER JOIN Table B, Set A.co=B.ID