Experience sharing for Oracle database system usage Summary

For example, the following command is slow to execute
The following is a code fragment:
Select A.empno from emp A where a.empno does not
(select Empno from Emp1 where job=′sale′);
If you are using an outer join, rewrite the command as follows:
The following is a code fragment:
Select A.empno from emp A, EMP1 b
where A.empno=b.empno (+)
and B.empno is null
and b.job=′sale′;
It can be found that the running speed is obviously improved.

3. How to delete duplicate records in a table
You can use this command to delete duplicate records in a table:
Oracle database is a large database system, generally used in business, government departments, it is very powerful, able to handle large quantities of data, in the network also use a lot. Oracle database management system is a database management software system based on relational and object-oriented management data.

Use of the 4.set Transaction command
When performing large transactions, Oracle sometimes reports the following error:
The following is a code fragment:
Ora-01555:snapshot Too old (rollback segment too small)
This means that Oracle's randomly allocated rollback segment for this transaction is too small for it to specify a rollback segment that is large enough to ensure the successful execution of the transaction. For example
The following is a code fragment:
Set TRANSACTION use rollback segment ROLL_ABC;
Delete from table_name where ...
Commit
The rollback segment ROLL_ABC is assigned to the delete transaction, and the commit cancels the rollback segment designation after the transaction ends.

5. Considerations for Using Indexes
A subquery in a Select,update,delete statement should regularly find a table row less than 20%. If a statement finds more rows than 20% of the Total row count, it will not be able to gain performance gains by using the index.
Indexes can produce fragmentation because when records are deleted from a table, they are also deleted from the table's indexes. The space that the table frees can be used again, while the space that the index frees is no longer available. Indexed tables that perform frequent deletions should periodically rebuild the index to avoid space fragmentation in the index and affect performance. Under permitted conditions, you can also periodically truncate tables, truncate commands to delete all records in a table, and also delete index fragments.

6. Issues to be noted in database reconstruction
Some views may cause problems when using import for database rebuilding, because the order in which the structure is entered may cause the input of the view to precede the input of its low-level tables, which will fail to establish the view. To solve this problem, you can take a two-step approach: First enter the structure, and then enter the data. Examples of commands are as follows (Uesrname:jfcl,password:hfjf,host sting:ora1, data files: expdata.dmp):
The following is a code fragment:
Imp jfcl/hfjf@ora1 file=empdata.dmp rows=n
Imp jfcl/hfjf@ora1 file=empdata.dmp full=y buffer=64000
Commit=y Ignore=y
The first command enters all database structures, but no records. Second input structure and data, 64000 bytes submitted once. The IGNORE=Y option guarantees that the second input succeeds even if the object exists.

The above six small experience is from the ordinary work summary come out, take out to share with everybody, hope can help everybody.