Interview Questions and Answers in Oracle Database

Interview Questions and Answers in Oracle Database

Basic theme:

1. Compare truncation with command

A: Both can be used to delete all records in the table. The difference is that truncate is a DDL operation that moves HWK without moving the rollback segment.

Deleting is a DML operation, and it takes a long time to roll back the segment.

similar

Truncate and without where clauses and drop will delete the data in the table.

Difference:

1.truncate and table structure (definition) that only deletes data without deleting data.

Drop statement will delete the constraints, triggers and indexes on which the table structure depends; Stored procedures/functions that depend on this table will be preserved.

But it becomes invalid.

2. The statement is dml, and the operation will be put into the rollback segment, which will not take effect until the transaction is committed; If there is a corresponding trigger, it will trigger when it is executed.

Truncate, drop is ddl, the operation takes effect immediately, and the original data cannot be rolled back without being put into the rollback segment. This operation does not trigger the trigger.

3. This statement does not affect the range occupied by the table, and the high water mark remains in its original position.

Obviously, the drop statement releases all the space occupied by the table.

Truncate statement By default, space is released to the minextent section unless reuse storage is used; Truncate will reset the high watermark (back to the starting point).

4. Speed, generally speaking: drop & gt；; truncate & gt；

5. Safety: Use drop and truncate carefully, especially when there is no backup, otherwise it is too late to cry.

In use, if you want to delete some data lines, you should pay attention to the where clause. The rollback segment should be large enough.

If you want to delete a table, of course, use drop.

If you want to keep the table, please delete all data. Truncate is used if it has nothing to do with the transaction. If it is related to a transaction, or if you want to trigger a trigger, use truncate.

If you are tidying up fragments in a table, you can use truncate to keep reusing storage, and then re-import/insert data.

2. Two minutes later. Oracle, need to output NULL as 0 in the query statement. How to deal with it?

Answer: nvl (field 0).

Nvl () function

Returns a non-null value from two expressions.

grammar

NVL (electronic expression 1, electronic expression 2)

parameter

eExpression 1，eExpression2

If eExpression 1 evaluates to null, NVL () returns eExpression2. If the evaluation result of eExpression 1 is not null,

Returns ee expression 1. EExpression 1 and eExpression2 can be of any data type. If eExpression 1 and eExpression2

The result of is null, then NVL () returns. Empty.

Return value type

Character type, date type, date time type, number type, currency type, logical type or null value.

explain

When null values are unsupported or irrelevant, you can use NVL () to remove null values from calculations or operations.

Choose nvl(a.name, empty) as student a and add the name of school b to a.id = b.id

Note: The types of the two parameters should match.

What is the difference between char and varchar2 data types in 3.3? Oracle Bone Inscriptions? The data "test" is stored in fields of type 10 and varchar2( 10) respectively.

What is the difference between its storage length and type?

Answer:

Poor: 1). The length of char is fixed, while the length of VARCHAR2 can be changed. For example, store the string "test". For CHAR (10),

;