| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Optimizing query on 2 servers?
On 8 Jun 1998 16:24:33 GMT, "Lily Djuniarti"
<lily.djuniarti_at_natinst.com> wrote:
>I'm running a query with tables from 2 different servers. I have no problem
>accessing the tables because there are already links for the tables I need
>from, say, server2 to server1.
>The problem is, this query runs forever, so I'll appreciate any idea on how
>to optimize this query.
First of all, allways use EXPLAIN PLAN to investigate the execution of the final query. As far as you didn't use it yet.
Second, if you use the cost based rule, make sure all tables are analyzed (recently).
Third, make sure you make use of the indexes WHEN NECCESSARY. In many cases, a full table scan performs better than with the use of an index (which can cause a lot of overhead). But the lack of an index when you need one is also very important.
Fourth, the use of "SELECT 'X'" seems harmless, but if you only need to know the existance of the record and there is an index on the field you use to connect the tables then use that field instead. So if you have an index on your field customer than use "SELECT customer" instead of "SELECT 'X'".
Fifth, put your 'driving' table as last in the list of your FROM-clause. This mostly is the smallest one. It can improve your performance drastically.
Sixth, be aware of the order in your where-clause. Try to follow the following order (this list is certainly not complete): 1. Restriction on indexed field from driving table (ie. where surname = 'Smith')
2. Restriction on non-index field from driving table 3. Join with other tables on index fields 4. Restriction on index fields with joined table 5. Restriction on non-indexed fields with joined table
7th, make sure you make use of your indexes if you want to. The
following statements make NO USE of the index:
WHERE TABLE1.ID = TABLE2.ID + 0
WHERE TABLE1.SURNAME = 'SMITH' || '& WESSON'
This is also the case in your query:
>table3.Customer like (table1_at_server1.first_name||'
>'||table1_at_server1.last_name))
Since it doesn't make use of an index, you query will perform badly.
Table 3 will be scanned as many times as there are resulting rows from
the join from table 1 with 2. I don't know the consequences of the
'LIKE' without use of the '%'. I think an equality will do better than
LIKE.
A solution to this problem can be a full table scan with the MINUS
operator, as in:
CREATE VIEW
SELECT TABLE1_at_SERVER1.FIRST_NAME || ' ' || TABLE1_at_SERVER1.LAST_NAME
FROM TABLE1_at_SERVER1 , TABLE2_at_SERVER1 WHERE TABLE1_at_SERVER1.ID = TABLE2_at_SERVER1.ID MINUS SELECT TABLE3_at_SERVER2.CUSTOMER
SELECT ... FROM TABLE3_at_SERVER2 WHERE ...
That's the way I should do it, but I can imagine something else is possible here.
Table 3 will be only scanned once (in the view).
Hope it helps.
 |
 |