The content discussed herein is based on the following environment: Oracle 8i (8.1.7)
Current version: 1.0 (041215)
Disclaimer: Some operations described herein may have a significant impact on the system, please use it with caution! I am responsible for any consequences of this!
Dual table small discussion
The Dual Table is a special table in Oracle and can see it in many times. For example, we have to take the system time: SQL> SELECT SYSDATE from DUAL;
Sysdate ----------- 2004-12-15 But what is going on in the Dual table, I want to carefully explore. Always, on a day of 9CBS community Oracle sector, some netizens have deleted the DUAL table, which has attracted my interest in exploration. Let's take a look at how many objects in the system are related to the DUAL table: SQL> Select Owner, Object_Name, Object_Type, TO_CHAR (CREATED, 'YYYY-MM-DD') Created from all_Objects where object_name like '% dual%';
Owner object_name Object_type create -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------- Sys Dual Table 2000-02-27Public Dual synynym 2000-02-27
It can be seen that there is a table in the system, and there is a synonym of DUAL. The owner of the DUAL table is sys because DUAL is the system created, and SYS is root user. The owner is transparent to all users, and does not need to add "SYS" prefix. Then look at the structure of the DUAL table: create table SYS.DUAL (DUMMY VARCHAR2 (1)) tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 16K next 16K minextents 1 maxextents 505 pctincrease 50); DUAL table only 1 column: Dummy, for varchar2 (1). (Very confused, why oracle uses VARCHAR (1) type, can't use char (1)?) From such a table structure, the purpose of the Dual table design is to reduce the retrieval. Overhead. Also, the DUAL table is built in the system table space, the first because the Dual Table is the user built, the default table space is system; second, the table and user table that may often be queried and user table Store, it is good for system performance. Have an creation of a table, creating synonyms or not enough. Dual under SCHEMA, so you can log in with another user or you can't query this table, so you should also need to authorize: Grant Select ON Sys. Dual to public with grant option; grant SELECT authority to the public. Next, look at the data in the DUAL table, in fact, the data in the DUAL table has a very important relationship with the Oracle database environment (Oracle will not be paralyzed, but many stored procedures and some queries will not be executed correctly). SQL> SELECT * from Dual; Dummy ---- X After the database is created, a record has been inserted in the DUAL table. Personally think that the value of the Dummy field does not matter, it is important that the number of records in the DUAL table. Below, try to delete the data in the dual table, see what results will appear: SQL> delete from dual;
1 line has been removed
SQL> SELECT *.
Dummy ----- It looks normal, then take the system time to see: SQL> SELECT SYSDATE from DUAL;
Sysdate ----------- This is below, we can't get the system date. Because sysdate is a function, acting on each data line. There is no data now, and it is impossible to take out the system date. This is fatal for a lot of select sysdate info v_sysdate from dual; how to take system time and other information stored procedures, because Oracle will immediately throw an exception of NO_DATA_FOUND, even if it is captured The stored procedure will not complete the required action correctly. In turn, if we insert a data, what is the result? SQL> INSERT INTO DUAL VALUES ('Y');
1 line has been inserted
SQL> commit;
Submit
SQL> SELECT *.
Dummy ----- xysql> select sysdate from dual; sysdate ----------- 2004-12-152004-12-15 Returning two records, which also cause problems. By use
SELECT SYSDATE INTO V_SYSDATE from DUAL;
To get the stored procedure for time or other information, Oracle will throw TOO_MANY_ROWS (ORA-01422) exception. Therefore, there is a need to ensure that there is only one record in the Dual table. Of course, you can't release the Update, INSERT, DELETE permissions of the DUAL table, so that the system is very dangerous. Finally, summarize if the Dual table is "unfortunate" to delete the recovery: 1. Log in with SYS. 2. Create a DUAL table (SQL as described above). 3. Grant public SELECT permission (SQL is as described above, but do not give Update, Insert, Delete Permissions). 4. Insert a record to the DUAL table (this only): INSERT INTO DUAL VALUES ('x'); 5. Submit modification. Others: For such important tables, Oracle does not protect it really unless (for 8i), but for Delete operations, Oracle seems to have done behind. SQL> SELECT *.
Dummy ----- xy
SQL> Delete from Dual;
1 line has been removed
SQL> commit;
Submit
SQL> SELECT *.
Dummy ----- Y
Regardless of how much records in the table (except for records), Oracle only deletes a data for each delete operation. This is why? ? About optimization: Building an index, it may be useful, however, if you can put the DUAL table in SGA? ? May not, because we have never cared about what is the value of Dummy this column (except this time), Oracle will not care. PS: Dual is a double meaning in English. It is really impossible to take this form. (Maybe a personal IQ issue), Dummy is the meaning of the virtual, this is a good understanding, just when it doesn't ......... Welcome everyone !
