Oracle Dual table

Oracle is still quite common, So I studied the Oracle Dual table and shared it with you here. I hope it will be useful to you. Character functions include conversion functions and character operation functions.

Conversion functions include LOWER, UPPER, and INITCAP (uppercase)

Character operation function: CONCAT, SUBSTR, LENGTH, INSTR (position of a string in this string), IPAD string is displayed in a certain format );
For example:

 
 

  
  
Select initcap (ename) from emp;
  
  
// Return all the names and upper-case letters.
  
  
Select concat (ename, job) from emp;
  
  
// Return a column consisting of ename and job.
  
  
Select initcap (substr (ename, 1, 3) from emp;
  
  
// Return a column, which is a string of a column.
  
  
Select length ('I love you') from dual;
  
  
// 3 is returned. both letters and Chinese characters are stored in two bytes.
  
  
Select lpad (ename, 10, '*') from emp;
  
  
// The returned name. If there are less than 10 names, use * to complete the query.
 
 

2: Internal Oracle storage is in the upper-write format.
For example:

 
 

  
  
Select * from emp whereEname='King';
  
  
// No results found
  
  
Select * from emp whereEname=Upper('King ');
  
  
// Check and find the matching results.
 
 

3: Oracle Dual table
The Oracle Dual table is special. It is a system table with only one Dummy Varchar2 (1) field, and Oracle tries its best to ensure that it returns only one record. When querying system values such as sysdate or sequence. currval in Oracle, you must write Dual in the Select statement. For example, select sysdate from dual. Use the Dual table to query data that does not have a specific user table.

In fact, each table has a hidden rowid, which is available in all tables except dual ).

Dual can be inserted and deleted. It is recommended that you do not delete the table, which may cause the database to fail to start. If you delete the file by mistake, set replication_dependency_tracking to FALSE ).

Dual should be a virtual table in the system memory, while the dual table in the system is only used to maintain the data dictionary and the system's operation permissions on dual. In the following experiment, insert several records to the Oracle Dual table and query them:

 
 

  
  
SQL> select * from dual;  
  
  
D  
  
  
-  
  
  
X  
  
  
SQL> select rowid ,dummy from dual;  
  
  
ROWID              D  
  
  
------------------ -  
  
  
AAAM1CAABAAAAgiAAA X  
  
  
SQL> select rowid ,dummy from dual order by dummy;  
  
  
ROWID              D  
  
  
------------------ -  
  
  
AAAM1CAABAAAAgiAAA X  
  
  
AAAM1CAABAAAAgiAAB Y  
  
  
AAAM1CAABAAAAgiAAC Y  
  
  
AAAM1CAABAAAAgiAAD Z 
 
 

1. Redo Oracle log files
2. High theory of Oracle Buffer Optimization
3. Oracle CPU usage Optimization
4. Overview operating system Oracle Optimization
5. How Oracle reduces Shared Server Processes