Connection Methods and common usage of Oracle database tables

If there are more than two associated tables in an SQL statement, what is the order of connection? ORACLE first connects two tables to generate a result set, and then associates the generated result set with the next table. continue until all tables are connected; finally, we will analyze several different connection methods of Oracle tables from the following typical examples:

1. Equal connection

Two columns with the same meaning can be used to create equal join conditions.

Only rows with the same value in the two tables of the connected column will appear in the query results.

For example, query employee information and department information of the corresponding employee;

 
 

  
  
   SELECT * FROM EMP,DEPT;   
  
  
SELECT * FROM EMP,DEPT  
  
  
 
  
  
WHERE EMP.DEPTNO = DEPT.DEPTNO;  
 
 

REM displays information about employees whose salaries exceed 2000 and their department names.

2. External Connection

For external connections, "(+)" can be used in Oracle, and "LEFT/RIGHT/full outer join" can be used in 9i. The following describes the external connections with instances. In addition to the information that matches the same connection condition, the information of a table that cannot match the same connection condition is displayed.

External connections are identified by (+.

◆ Left condition (+) = right condition;

In addition to displaying information that matches equal connection conditions, it also displays information that cannot match equal connection conditions in the table where the right condition is located.

This is also called "right Outer Join". Another representation method is:

SELECT... FROM table 1 right outer join table 2 on join conditions

◆ Left condition = right condition (+ );

In addition to displaying information that matches equal connection conditions, it also displays information that cannot match equal connection conditions in the table where the left condition is located.

This is also called "left Outer Join ".

SELECT... FROM table 1 left outer join table 2 on join conditions

Example: displays employee information and Department Information

-- Unable to display employee information without a department

-- Unable to display department information without employees

-- SELECT * from emp, dept where emp. DEPTNO = DEPT. DEPTNO;

-- Directly perform equal connections:

SELECT * from emp join dept on emp. DEPTNO = DEPT. DEPTNO;

REM displays employee information and corresponding department information, and displays department information without employee

-- SELECT * from emp, dept where emp. DEPTNO (+) = DEPT. DEPTNO;

SELECT * from emp right outer join dept on emp. DEPTNO = DEPT. DEPTNO;

REM displays employee information and corresponding department information, and displays employee information without Department

-- SELECT * from emp, dept where emp. DEPTNO = DEPT. DEPTNO (+ );

SELECT * from emp left outer join dept on emp. DEPTNO = DEPT. DEPTNO;

3. Unequal connections

The two related columns in the two tables are not connected. The comparison symbols are generally >,<,..., ..

REM SALGRADE

-- Desc salgrade;

-- SELECT * from salgrade;

REM displays the employee's ID, name, salary, and level.

Select empno, ENAME, SAL, SALGRADE. * from salgrade, EMP

Where emp. sal between losal and hisal;

REM displays the employee ID, name, salary, salary level, and department name;

Select empno, ENAME, SAL, GRADE, dname from emp, DEPT, SALGRADE

Where emp. DEPTNO = DEPT. deptno and emp. sal between losal and hisal;

4. Self-connection

Self-join is a frequently used connection method in databases. Using Self-join, you can treat an image of your own table as another table to get some special data. The following describes the self-connection method:

Copy one copy of the original table as another table, and connect the two tables to the same flute.

For example, the employee ID, name, and manager name are displayed.

Select worker. ENAME, WORKER. MGR, MANAGER. EMPNO, MANAGER. ename from emp worker, EMP MANAGER

Where worker. MGR = MANAGER. EMPNO;

5. Hash connection

When the memory can provide sufficient space, the HASH connection is the common choice of the Oracle optimizer. In the hash connection, the optimizer first selects the small tables in the two tables based on the statistical information and creates the hash table based on the connection key in the memory. The optimizer then scans the large tables in the table connection, compare the data in a large table with a hash table. If there is associated data, add the data to the result set.

When a small table in a table connection can be completely cached to available memory, the hash connection works best. The cost of hash connection is only the cost of reading two tables from the hard disk to the memory.

However, if the hash table is too large to be fully cached in the available memory, the optimizer will split the hash table into multiple partitions and then cache the partitions one by one into the memory. When the table partition exceeds the available memory, part of the partition data will be temporarily written to the temporary tablespace on the disk. Therefore, when the partition data is written to the disk, the large range (EXTENT) will improve the I/O performance. The recommended range of temporary tablespace in ORACLE is 1 MB. The interval SIZE of the temporary tablespace is specified by the uniform size.

After the hash table is created, perform the following operations:

1) scan the second large table

2) if a large table cannot be completely cached to available memory, the large table will also be divided into many partitions.

3) cache the first partition of a large table to the memory.

4) scan the data in the first partition of a large table and compare it with the hash table. If there is a matching record, add it to the result set.

5) Like the first partition, other partitions are also processed.

6) After all partitions are processed, ORACLE merges and summarizes the generated result sets to generate the final result.

When the hash table is too large or the available memory is limited, the hash table cannot be completely cached to the memory. As the number of result sets meeting the connection conditions increases, the available memory will decrease. data cached in the memory may be written back to the hard disk. In this case, the system performance will decrease.

When the two connected tables use equivalent join and the data volume of the tables is large, the Optimizer may use Hash join. Hash connections are based on CBO. Oracle uses the hash EDGE connection only when the HASH_JOIN_ENABLED parameter of the database Initialization is set to True and a sufficiently large value is set for the PGA_AGGREGATE_TARGET parameter. HASH_AREA_SIZE is a backward compatible parameter, but HASH_AREA_SIZE should be used in versions earlier than Oracle9i. When ORDERED is used, the first table in the FROM clause is used to create a hash table.

 
 

  
  
   select a.user_name,b.dev_no   
  
  
from user_info a, dev_info b  
  
  
 
  
  
where a.user_id = b.user_id;  
  
  
 
  
  
Plan  
  
  
 
  
  
----------------------------------------------------------  
  
  
 
  
  
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=3936  
  
  
 
  
  
)  
  
  
 
  
  
1 0 HASH JOIN (Cost=5 Card=82 Bytes=3936)  
  
  
 
  
  
2 1 TABLE ACCESS (FULL) OF 'USER_INFO' (Cost=2 Card=82 Bytes  
  
  
 
  
  
=1968)  
  
  
 
  
  
3 1 TABLE ACCESS (FULL) OF 'DEV_INFO' (Cost=2 Card=82 Bytes=  
  
  
 
  
  
1968)  
 
 

You can add HINTS to an SQL statement to force the ORACLE optimizer to generate an execution plan for hash connections.

 
 

  
  
select /*+ use_hash(a b)*/ a.user_name,b.dev_no   
  
  
 
  
  
from user_info a, dev_info b  
  
  
 
  
  
where a.user_id = b.user_id; 
 
 

When a useful index is missing, hash connections are more effective than nested loop connections. Hash connections may also be faster than nested loop connections, because processing hash tables in memory is faster than retrieving B _ tree indexes.

1. MySQL left connection, right connection, and internal connection
2. In-depth exploration of database connection Performance
3. Example a>
4. How to deal with several common improper database connections
5. Multi-database environment connection Policy