Driving_site: the speed of Oracle cross-Database Data Association

In a recent database query statistics, you must use db_link to associate tables across databases. Database
A table with more than one million data records and database B in
Tables with more than records are associated. The fields associated with the two tables are indexed. However, in actual queries, if two tables are joined from database B, the full table scan of large tables in database A will be performed. The slow query speed is secondary, the key is that full table scan will affect the normal operation of database.

Through constant adjustment and optimization of SQL statements, it is found that when the two tables perform simple join queries, if "*" is used after the SELECT statement to obtain all the data, the query will not cause the full table scan, however, when a specific field is obtained after the SELECT statement, a full table scan is performed for a large table.

Grouping based on a simple query will directly cause a full table scan for a large table. In this case, all statement optimization techniques will fail because the index is invalid, in this case, a special method must be used to establish
The index of the associated query, which is mandatory to specify the table as the main driving table during the association process, and that table as the slave table. In the process of cross-database association query, because the table is not in the same database (at the same time in different operating systems
The data of one table is extracted to another database during the association process. In this case, if the big table data is extracted to the database where the small table is located during the association process
During full table scan of a large table, the entire operation process is very slow and affects the normal operation of database.

The final solution to full table scan is to force the primary drive table to be specified through driving_site when two large and small tables of different databases are joined in database B, that is, use the specified table as the main table, and use other tables as the database to extract from the table to the driver table for join operations. The statement is as follows:

Select/* + driving_site (main) */a. *, B. * from A. A main @ bigtabledb, B. B
Minor where main. ID = minor. ID and .......

In this way, the full table scan of the database where the large table is located will be avoided, and the query speed will be improved in a series.

 

Explanation:

When/* + driving_site (main) */is specified, Oracle will obtain all data from the minor table to the database where the main table is located for join operations. (The index takes effect, and the speed is fast)
If/* + driving_site (minor) */is specified, Oracle obtains all data from the main table to the database where the minor table is located for join operations. (Index failed, full table scan)

 

Of course, Oracle has a lot of keywords used to specify the calculation method for associated queries. We will introduce them one by one when we encounter specific application instances.

 

Reference:

Driving_site
Functions and
Ordered
Similar.
Driving_site
It is usually used in Distributed queries. If this prompt is not displayed,
Oracle
It will first retrieve from the remote vertex and connect them to the local site. Use
Driving_site
You can search locally and send the retrieved data to a remote node for connection.

Tip: Reasonable Use
Driving_site
To greatly reduce network traffic in Distributed queries.