Oracle connection method: oracle connection

Oracle connection method: oracle connection

In general, Join methods include: Nested Loops, Hash-Join, Sort Merge Join, and Cartesian Join (Merge Join Cartesian ).

1. Nested loop join (Nested Loops)

Nested loops include the concepts of External tables (drivingtable, driving table) and internal tables (inner or driven-to table, driven table). Generally, the result set is larger than the internal table, A small result set is an external table. External tables (result sets) are used to drive internal tables.

Nested loop join uses each row in the result set (External table) obtained by one access operation to touch another table (internal table. If the size of the result set is limited and there is an index on the column used to join (the inner table column), the join efficiency is usually the highest.

In short, this type of join is especially efficient if the external table (result set) is very small and the internal table (result set) is very large and has pre-created indexes.

 

Advantages and disadvantages: memory usage is very small, because it is not sorted, and the data row set processes only one row at a time, the cost is very small. This is also the reason. In addition to the long time spent on building a large dataset, it is also suitable for processing large datasets.

The basic measurement of nested loop join is the number of data blocks to be accessed to prepare the final result set.

 

 

2. Sort and Merge Join (Sort Merge Join)

Internal Connection process:

1) first generate the data required by rowsource1, and then sort the data according to the joined column (such as A. col3.

2) then generate the data required by rowsource2, and sort the data according to the Join Operation associated columns (such as B. col4) corresponding to sort source1.

3) At last, the sorted rows on both sides are put together for the merge operation, that is, the two row sources are connected according to the connection conditions.

The following figure shows the connection steps:

MERGE

//

SORT

|

Row Source1 Row Source 2

If the row source has been sorted in the connection Association column, the connection operation does not need to perform the sort operation, which can greatly improve the connection speed of this connection operation, because sorting is a resource-consuming operation, especially for large tables. Pre-sorted row sources include indexed columns (such as a. col3 or B. col4 indexed) or row source has been sorted in the previous step. Although the process of merging two row sources is serial, the two row sources can be accessed in parallel (such as reading data in parallel and sorting in parallel ). Once the dataset is sorted, the merging process is very fast.

Sort and merge joins are most suitable for queries with limited data filtering conditions and returned finite data rows. If no index is available for the correlated columns, sorting and merging is also a good choice.

In general, sorting and merging are usually the best choice when the condition is not equivalent. For example: where table1.col1 between table2.col1 and table2.col2, such join conditions are more suitable for sorting and merging joins. (In this case, hash join is impossible)

If the data row source is very large, sort and merge joins may be the only viable option.

 

3. Hash-Join)

This kind of JOIN was introduced after oracle7.3. Theoretically it is more efficient than NL (nested loop) and SMJ (sort merge), and only used in CBO optimizer.

First, the where filter criteria are applied to read the two tables to be joined. Based on the statistical information of the tables and indexes, small result sets are determined and completely hashed to the memory. The hash list contains all data rows in the source result set and is loaded into the hash bucket by a random function that converts the join key to the hash value. As long as the memory is sufficient, this hash is always stored in the memory. If the memory is insufficient, it is written to the disk.

Then, read the large result set and apply the hash function to the join key column.

A smaller rowsource is used to construct a hash table and a bitmap. The 2nd row sources are used to be hansed and matched with the hash table generated by the first row source for further connection. Bitmap is used as a fast search method to check whether there are matched rows in the hash table. In particular, this search method is more useful when the hash table is large and cannot be fully stored in the memory. This connection method also involves the so-called driving table concept in the NL connection. The tables built as hash tables and bitmap are driving tables, when the constructed hash table and bitmap can be accommodated in the memory, this join method is highly efficient.

To make the hash connection valid, set HASH_JOIN_ENABLED = TRUE. By default, this parameter is TRUE. In addition, do not forget to set the hash_area_size parameter to make the hash connection run efficiently, because the hash connection runs in the memory of the specified size of this parameter, too small parameters will make the hash connection performance lower than other connection methods.

Note: deciding which table is the smallest depends not only on the number of data rows, but also on the size of these rows, because the entire row will be stored in the hash list.

 

Finally, Let's sum up the following information:

SortMerge Join, SMJ ):

A) For non-equivalent connections, the connection efficiency is relatively high.

B) if the associated columns are indexed, the effect is better.

C) For connecting two large rowsources, the connection method is better than the NL connection.

D) However, if the row source returned by sortmerge is too large, the database performance decreases when too many rowids are used to query data in the table, because too many I/O.

Nested loop (NestedLoops, NL ):

A) if drivingrow source (External table) is small and has a unique index on inner row source (internal table), or has a highly selective non-unique index, using this method can improve efficiency.

B) NESTEDLOOPS does not have the advantage of other Connection Methods: You can first return the connected rows, instead of waiting for all the connection operations to complete before returning data, this enables fast response time.

Hash join (HJ ):

A) This method was introduced later in oracle7 and uses a more advanced connection theory. In general, the efficiency should be better than the other two connections, but this connection can only be used in the CBO optimizer, in addition, you must set the hash_area_size parameter to achieve better performance.

B) It will achieve relatively good efficiency when two large rowsources are connected, and it will achieve better efficiency when one row source is smaller.

C) It can only be used for equivalent connections.

D) no requirement on indexes (an index may also be used, but it does not limit the access of SQL statements)

 

Connection method between oracle and Java

1. Data source connection. To create a new data source TestDataBase
Package com. shenjun;
Import java. SQL .*;

Public class conn {

/**
* @ Param args
*/
Public static void main (String [] args ){
// TODO Auto-generated method stub
Try {
Class. forName ("sun. jdbc. odbc. JdbcOdbcDriver ");
Connection ct = DriverManager. getConnection ("jdbc: odbc: TestDataBase", "scott", "tiger ");
Statement sm = ct. createStatement ();
ResultSet rs = sm.exe cuteQuery ("select * from student ");
While (rs. next ()){
System. out. println ("username:" + rs. getString (2 ));
}
} Catch (Exception e ){
E. printStackTrace ();
}

}

}
2. jdbc connection, cited jar package
Package com. shenjun;

Import java. SQL. Connection;
Import java. SQL. DriverManager;
Import java. SQL. ResultSet;
Import java. SQL. Statement;

Public class conn2 {

/**
* @ Param args
*/
Public static void main (String [] args ){
// TODO Auto-generated method stub
Try {
Class. forName ("oracle. jdbc. driver. OracleDriver ");
Connection ct = DriverManager. getConnection ("jdbc: oracle: thin: @ 127.0.0.1: 1521: ORCL", "scott", "tiger ");
Statement sm = ct. createStatement ();
ResultSet rs = sm.exe cuteQuery ("select * from student ");
While (rs. next ()){
System. out. println ("username:" + rs. getString (2 ));
}
} Catch (Exception e ){
E. printStackTrace ();
}
}

}... Remaining full text>

How to create an oracle database connection method?

Currently, oracle provides a plug-in similar to transparent gateway. After this is installed, the oracle database can be connected to other database environments through dblink.

However, this installation is difficult.

An easy way is to use a java application to read and write data directly from two databases.

If the database is an oracle database, only dblink is used.
Create database link XXX connect to USER identified by PASSWORD using 'dbname'