Query optimization question

Query optimization question

Post by Gary R Meyers(2 » Sun, 05 Sep 1993 00:03:34



I have a question as to ordering table references in an Insert statement.

First, info:
I have two tables--1) WELL_HDR, containing 1,500,000 rows.

2) LD_WELL_HDR containing about 50,000 rows.
Both tables have the same structure. The Primary Key is a column named UWI.
Both tables have a unique index on UWI.
I want to insert into WELL_HDR from LD_WELL_HDR all the unique rows.

My question is about the order of table names in my Insert sql.

INSERT INTO WELL_HDR
SELECT * FROM LD_WELL_HDR
WHERE NOT EXISTS
(SELECT UWI FROM WELL_HDR WHERE LD_WELL_HDR.UWI= WELL_HDR.UWI)
/

Does the smaller table (LD_WELL_HDR) or the larger table (WELL_HDR) come
first in the subquery? (Driver table?)

Any other suggestions for making this faster??

Also, I will be wanting to update the WELL_HDR table using some (but not all)
columns from the LD_WELL_HDR table. Hints on constructing the updates are
greatly appreciated.

Thanks,
Gary
--

 
 
 

Query optimization question

Post by J.Ple » Wed, 08 Sep 1993 21:10:56


: INSERT INTO WELL_HDR
: SELECT * FROM LD_WELL_HDR
: WHERE NOT EXISTS
: (SELECT UWI FROM WELL_HDR WHERE LD_WELL_HDR.UWI= WELL_HDR.UWI)
: /

: Does the smaller table (LD_WELL_HDR) or the larger table (WELL_HDR) come
: first in the subquery? (Driver table?)    

I don't know that from my head, but try the EXPLAIN PLAN command from SQL*plus
to find out!

        - Joerg Plewe, MPI Dortmund

 
 
 

Query optimization question

Post by Jared Heck » Wed, 08 Sep 1993 18:51:00


G7>Any other suggestions for making this faster??

If you are looking to update only unique rows, why not use rowid as your
surrogate key to eliminate dups?  

e.g.

update large_table                      /*  or an 'insert' statement if appropriate */
as select col1,col2,col3
from small_table a
where a.rowid not in
(select *
from small_table x, small_table y
where x.rowid != y.rowid and
x.pk=y.pk)

Please note the more selective join is last, as that is how Oracle parses.
Also, if the schemas differ, you may have to dummy a field or two on your
select.

hth -

jh

---
. MR/2 1.39x NR . OS/2'ing it big-time!!

 
 
 

1. query optimization question

Hi...

        we've got a couple of tables and some indexes and we're
having trouble getting the following query to make use of the
indexes:

select * from x,y where x.a = y.a order by x.b desc;

x.b has the index on it already...

        we had high-priced oracle consultants tell us there was
no way to get the index in on the act unless you add a bogus
where clause on x.b, e.g.

select * from x,y where x.b is not null and x.a=y.a order by x.b;

        we'd had no luck trying with a couple of hints to get
the index used in the first case, but it seems ridiculous to have
to create a bogus where clause to get the optimizer to recognize
the index...

        anyone out there have better advice than the oracle
racketeers?

thanks
-mark

2. Best choice for the Newbie??

3. A New Query Optimization Strategy

4. CD Creator & Win 95

5. Query Optimization with Oracle

6. email on palmIIIc - weird newbie question

7. Query optimization puzzle.

8. PPCP

9. Query Optimization

10. Query optimization?

11. Query Optimization Tips

12. articles on query optimization

13. Query Optimization in ORACLE 7.0