Oracle database Development Face Questions

Recently participated in several interviews. Summarize the most frequently asked questions about the competition for Oracle development positions:

1, delete and truncate difference?

1) Truncate is a DDL statement, and delete is a DML statement;

2) Truncate speed is much faster than delete;

The reason: When we run the delete operation, all table data is first copied to the rollback table space. The amount of data spent varies over time. While truncate is directly deleting data, it does not go into the rollback table space.

3) Connect (2) This also causes the delete data to be executed rollback for data rollback, while truncate is permanently deleted and cannot be rolled back.

4) The truncate operation does not touch the delete trigger on the publication. and delete will trigger normally.

5) Truncate statement can not take the where condition means that only all data can be deleted, and delete can delete some data;

6) The TRUNCATE operation resets the table's high watermark (higher water Mark), and delete does not.

PS: About high watermark HWM knowledge Clear reference article: http://blog.csdn.net/indexman/article/details/25910255

2, explain the three paradigms of the database?

Please refer to: http://blog.csdn.net/indexman/article/details/19907731

3, NVL and NVL2 two functions of the use of the method and the difference?

NVL (Expr1, EXPR2): EXPR1 is null, returns EXPR2, NOT NULL, returns EXPR1. Note that the two types should be consistent
NVL2 (Expr1, Expr2, EXPR3): EXPR1 is not null. returns EXPR2; null to return EXPR3. EXPR2 and EXPR3 are different types of words. EXPR3 is converted to the type of EXPR2

Take a look at the official Demo sample: Commission is a wage commission

[Email protected]> Select last_name, NVL (To_char (commission_pct), ' not Applicable ')  2  "commission" from Employees  3  WHERE last_name like ' B% '  4  ORDER by last_name; Last_Name                 COMMISSION-----------------------------------------------------------------Baer                      not Applicablebaida not                     Applicablebanda                     . 1Bates                     . 15Bell not                      Applicablebernstein                 . 25Bissot Not                    applicablebloom                     . 2Bull                      Applicable

[Email protected]> SELECT last_name, salary, NVL2 (commission_pct,  2  salary + (Salary * commission_pct), Salary) Income  3 from  employees WHERE last_name like ' B% '  4  ORDER by last_name; Last_Name                     SALARY     INCOME---------------------------------------------Baer                           10000      10000Baida                           2900       2900Banda                           6200       6820Bates                           7300       8395Bell                            4000       4000Bernstein                       9500      11875Bissot                          3300       3300Bloom                          10000      12000Bull                            4100       4100

4. Set operator minus and Interset

If Table A and table B data such as the following:

Table A:

Data

----------

A

B

B

C

Table B

Data

----------

B

C

D

D

Ask 1:select data from A minus SELECT data from B; Run the results?

--------------------------------------------------------------------------------------------------

Answer:

A

Ask 2:select data from A INTERSECT SELECT data from B; Run the results?

Answer:

B

C

Summary of Set operators:

1)UNION: Consists of all non-repeating rows selected by each query. The set does not include a recurrence value, which is ordered by default in column 1 of the 1 query .

2)UNION all: The entire row selected by each query . A complete set includes repeated values. Not sorted.

3) minus: in the first query. Not a row in a back-face query.

Not including repetition. Sorted in ascending order of column 1 of the 1 query .

4) INTERSECT: Take the intersection of each query result. Do not include repeated lines. Sorted in ascending order of column 1 of the 1 query .

Please look forward to!

------------------------------

Dylan presents.

Oracle database Development Face Questions