Abstract
This paper will present how queries involving database links are processed. We will look at four cases: (1) query against a single remote table, (2) a join involving one local table and one remote table, (3) a join of two tables located on the same remote database, and (4) a join of two tables existing on two separate remote databases.
Introduction
The method Oracle uses to process distributed queries is one of the less understood features offered by Oracle. This presentation will explain some of the things that happen behind the scenes of a distributed query and will give some techniques an application developer can use to write better distributed queries.
SQL*Net Demystified
In order to perform a distributed query, an RDBMS needs to have a means of communicating with other databases. SQL*Net is Oracle's remote data access protocol and application programmatic interface (API) that runs on top of existing network protocols. Its main function is to allow a front-end application such as SQL*Forms to run on one computer while accessing Oracle data which resides on a database either on the same machine or on a remote machine.
Architecturally, the front-end tool is responsible for generating SQL statements depending upon user input (eg user fills in fields in a form and then hits the INSERT key) or application design (eg embedded SQL in a C program). We also refer to this as a client process. The Oracle back-end or server process is responsible for parsing and executing SQL statements as optimally as possible. Once processed, the results of the statement are returned to the front-end tool. In the case of a SELECT statement, the server process retrieves a record or set of records from the datafiles and sends them the client upon request. The front-end tool then decides how the data is presented to the user. This leaves SQL*Net with the deceptively simple tasks of managing a communication session between two computers, transporting data between two processes, and providing any necessary character set conversions. Since SQL*Net itself has no role in the decision making process of executing a query, one can say that this paper is actually an RDBMS topic in SQL*Net clothing!
The Sample Environment
For the purposes of this paper, we will use a sample environment consisting of:
- three computers running an OS such as Unix (identified as unix1,
unix2, and unix3) connected via some networking protocol such as
TCP/IP.
- an Oracle database running on each machine (identified as instance A, B, and C respectively)
- an Oracle database user account of scott/tiger on each database
We will assume that our user, scott, is already logged onto unix1 and has initiated a SQL*Plus session with database A. Theoretically, it should be possible for the three nodes to be running any of the operating systems supported by Oracle and be able to communicate with any supported networking protocol. Some environments, however, will present some minor limitations not covered in this paper. For example, DOS cannot act as a server to other machines.
Database Links
In order for two Oracle databases to share data, one Oracle instance temporarily acts as a client to the other Oracle instance. This action is accomplished through database links stored in the initiating database. The syntax for creating a database link is as follows (optional clause enclosed by []):
CREATE DATABASE LINK dblinkname
[CONNECT TO user IDENTIFIED BY password]
USING 'connect_string'
For our example, we initially create two database links from unix1 to unix2 and unix3 as follows:
CREATE DATABASE LINK lax
USING 'T:unix2:B';
CREATE DATABASE LINK syd
USING 'T:unix3:C';
SQL*Net V2 based DBLINKs using a V2 service name example:
CREATE DATABASE LINK lax
USING 'v2servicename';
-or-
CREATE DATABASE LINK lax
USING 'TNS:v2servicename';
Simple Remote Queries
Before we look at what happens with a complex distributed query involving joins of tables on sereral remote databases, it helps to consider what the mechanics are behind a simple query using a database link. We will use the following example SQL statement:
SELECT MAX(ename), 'dept # =' || deptno
FROM emp@syd
WHERE deptno BETWEEN 10 AND 100
GROUP BY deptno
ORDER BY 1;
The local database on unix1 will process the query and send the following to the database on unix3:
SELECT ename, deptno
FROM emp
WHERE deptno >= 10 AND deptno <= 100;
As the records are fetched into unix1, it performs the group by applies the MAX() function to each group then orders what it has and sends those results back to its client. This implies several important points about how a remote query is handled by Oracle V6. Some are not easily implied but noted
below:
1. Aggregate functions like MAX(), COUNT() are NOT passed across the net but rather are performed on the local node.
2. Expressions in the SELECT list are decomposed into columns and evaluated when the data is retrieved.
3. Only a relevant subset of columns are fetched across the net.
4. An expression in a WHERE clause is passed across to the remote
database if it compares the remote column to a literal (eg ename =
'KING').
5. Expressions in a WHERE clause are passed to the remote database
if all columns are in the expression are located in the same remote
table the remote database (eg emp.sal * 0.20 > emp.bonus)
6. Datatype conversion functions like TO_CHAR() in a WHERE clause are subject to the conditions in #4 and #5.
7. The optimizer only has knowledge of local indexes when making decisions about how to execute a query.
8. Remote indexes can be still be used to resolve the decomposed query sent to the remote database.
9. GROUP BY is not sent to the remote database.
10. ORDER BY is not sent to the remote database.
Simple Distributed Joins
With joins that involve tables on a remote database and a local
or second remote database, the points mentioned in the case of a
simple remote query are still true. What happens to allow this
is the decomposition of a join into separate independent queries
whose results are then joined on the local node. Here is a
simple example of how a join is decomposed.
SELECT ename, dname
FROM emp@syd e, dept@lax d
WHERE e.deptno = d.deptno
AND e.job != 'CLERK'
AND d.loc = 'NEW YORK';
becomes:
unix3: SELECT ename, job, deptno
FROM emp
WHERE job <> 'CLERK';
unix2: SELECT dname, loc, deptno
FROM dept
WHERE loc = 'NEW YORK';
Note that, at least in Oracle V6, the same decomposition occurs
even if both tables exist on the same remote node.
Putting It All Together
With these facts in mind, it is easy to see how the use of views
can help in cases where it is clearly better for a function to be
performed on a remote node, rather than the local one. For
example, instead of issuing a count of rows against a remote
database and having the data returned to be counted locally, a
remote view could be queried to return only the count value.
This increases performance by reducing network traffic.
Example:
SELECT COUNT(*)
FROM emp@syd
WHERE deptno = 10;
becomes...
unix3:
CREATE VIEW empcount (numemps, deptno) AS
SELECT COUNT(empno), deptno
FROM emp
GROUP BY deptno;
unix1:
SELECT numemps, deptno
FROM empcount@lax
WHERE deptno = 10;
Views can also be used to help control the node where the join
will actually take place. In the case where there is a small
table on the local database and a large table on a remote
database, it is usually better to have the smaller table sent
across the network to be processed remotely than retrieve the
large table and perform a local join. This method requires the
existence of database links from each of the three machines to
the other two.
Example:
SELECT ..
FROM small, big@syd
WHERE small.key=big.key;
becomes...
unix2:
CREATE VIEW bigsmall AS
SELECT ..
FROM small@mia, big
WHERE small.key=big.key;
unix1:
SELECT ..
FROM bigsmall@syd;
When both tables being joined are on the same remote node, one
can use a view to make sure the join is performed on that node.
At times, the above scenario exists in a subtle form. Consider
the following query:
SELECT *
FROM A@mia a, B@lax b, C@mia c
WHERE a.x = b.x
AND b.x = c.x;
Upon examining the WHERE clause, one sees that the transitive
expression of "a.x = c.x" can also be generated. This shows that
tables A and C can be joined at the same remote node rather than
being joined at the local node. The predicates taken directly do
not always show that a remote join is possible.
In the case where both tables are on separate databases one can
create a view on the machine where the join may be performed the
quickest. While deciding this, one can consider which node has
the largest table, the fastest CPU, or the lightest load.
If views are not possible, it may be possible to fool the
optimizer into choosing a different path that may be faster than
what the rules would predict. For example if one were joining a
subset of a local small dept table with a large remote emp table,
a nested loops join may return faster than a sort merge. Here is
an example.
unix1:
SELECT e.ename, d.dname
FROM emp@hq e, dept d
WHERE d.loc IN ('BUTTE', 'FLAGSTAFF')
AND e.deptno = d.deptno;
In the above query, the ename and deptno columns of the whole emp
table will be pulled over the net to have it's deptno value
compared with the department numbers of departments located in
Butte, Montana and Flagstaff, Arizona. If the total number of
employees working in those cities are a small percentage of the
total employees working worldwide, then much network traffic and
IO was wasted reading and sending data that would just be
rejected.
If emp had an index on the deptno column, it would be ideal to
just have it make an index scan for rows that have departments
located in the two cities we want and just return those rows
back. The following query will persuade the optimizer to choose
that execution path.
unix1:
SELECT e.ename, d.dname
FROM emp@hq e, dept d
WHERE d.loc IN ('BUTTE', 'FLAGSTAFF')
AND e.deptno <= d.deptno
AND e.deptno >= d.deptno
Note that the last two clauses taken together have the same logic
as a straight equality. However, the optimizer analyzes each
clause and concludes that it will have to do a full table scan
for each dept.deptno.
In this case, Oracle performs a nested loops join and sends the
following query to the remote database:
SELECT ename, dept
FROM emp
WHERE deptno >= :1
AND deptno <=: 2;
It then loops through the appropriate rows in dept, binds the
deptno values and executes the remote query.
One can also use the TKPROF and EXPLAIN utilities as another way
to see how queries are processed. To enable these facilities,
one may turn on global tracing by setting sql_trace = true in the
init.ora file. Then one may simulate a distributed database by
creating and using database links that loop back to the database
where the query originated. This will create multiple trace
files -- one for the "local" session and another for each
"remote" connection query. If one had to, one could copy trace
files from a remote machine and use the EXPLAIN parameter over
SQL*Net with the command "tkprof .. EXPLAIN=user/pswd@remote."
Note 1004553.6 DISTRIBUTED QUERY ANALYSIS.
* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!
02winter_optimizer_1.pdf