Oracle database Development Face Questions

Recently participated in a number of interviews, summed up the competition in the Oracle development post most frequently asked what questions:

1, delete and truncate difference?

1) Truncate is a DDL statement. Delete is a DML statement;

2) Truncate speed is much faster than delete.

The reason is that when we run the delete operation, all table data is first copied to the rollback table space, and the amount of data takes different lengths of 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. EXPR3 will be converted to EXPR2 type if the EXPR2 and EXPR3 types are different

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 set of rows selected by each query, with the total unions including repeated values, not sorted.

3) minus: in the first query, the rows in the query are not in the back face. do not include repeat, sorted in ascending order of column 1 of the 1 query .

4) INTERSECT: Take the intersection of each query result, excluding repeated lines, sorted in ascending order of column 1 of the 1 query .

Please look forward to!

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

Dylan presents.

Oracle database Development Face Questions