Access problem NOT IN query using db link on db 9i:select id_bda

from anag_li
where id_bda not in
(select idanag from prova@bdlink);
Access plan:
————————————————————————————
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  | Inst   |IN-OUT|
————————————————————————————
|   0 | SELECT STATEMENT     |             | 37490 |   585K|   978K|        |      |
|*  1 |  FILTER              |             |       |       |       |        |      |
|   2 |   TABLE ACCESS FULL  | ANAG_LI     | 37490 |   585K|  3646 |        |      |
|   3 |   REMOTE             |             |   186K|  1819K|    26 | BDAPR~ | R->S |
————————————————————————————
the query as written will never use no index because it must take all lines except those that are returned by a remote query.
You can improve access by rewriting the query using a correlated subquery.
Details in this doc:
http://docs.oracle.com/html/A86647_01/vmqtune.htm
paragraph  “Use NOT EXISTS instead of NOT IN”
Let’s rewrite the query as shown in doc oracle:
SELECT id_bda
FROM anag_li
WHERE NOT EXISTS
(SELECT id_bda
FROM prova@bdlink
WHERE anag_li.id_bda=anagpop.idanag);
Access plan
————————————————————————————
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  | Inst   |IN-OUT|
————————————————————————————
|   0 | SELECT STATEMENT     |             | 37490 |   585K| 78626 |        |      |
|*  1 |  FILTER              |             |       |       |       |        |      |
|   2 |   TABLE ACCESS FULL  | ANAG_LI     | 37490 |   585K|  3646 |        |      |
|   3 |   REMOTE             |             |     1 |    10 |     2 | BDAPR~ | R->S |
There is an improvement in both cost and timing of the query.
The ideal solution, however, is the upgrade 11g.
It is present in 11 new features that makes the immediate query:
——————————————————————————
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Inst   |IN-OUT|
——————————————————————————
|   0 | SELECT STATEMENT   |         |  7391 |   129K| 11811 |        |      |
|   1 |  HASH JOIN ANTI SNA|         |  7391 |   129K| 11811 |        |      |
|   2 |   TABLE ACCESS FULL| ANAG_LI |   739K|  5774K|  9787 |        |      |
|   3 |   REMOTE           | ANAGPOP |   742K|  7251K|   535 | BDAPR~ | R->S |
——————————————————————————
information of  features Null-Aware Anti-Join :
http://structureddata.org/2008/05/22/null-aware-anti-join/
 

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *