SQL coding specifications and SQL coding specifications

SQL coding specifications and SQL coding specifications

1. aliases must be created for tables to facilitate the use of columns in the questionnaire.

For example, select owner, object_id, name from a, B where a. id = B. id;

If you do not use an alias for a table, how can I know which table the accessed column belongs. If there are hundreds of rows of SQL, if there are many SQL tables associated, let's go.

2. database object naming

Table prefix/suffix T_XXX

View prefix/suffix V_XXX

Materialized View prefix/suffix MV_XXX

Index IDX _ column name

Special table

Data Warehouse FACT table _ FACT

Data Warehouse dimension table _ DIM

Business intermediate table _ TMP

LOG table _ LOG

This naming convention makes it easier for developers to get started with DBAs who are not familiar with the business.

3. Standard Quantum queries are strictly prohibited (paging can be written)

Select (select... from a where a. id = B. id) from B; --- this is called scalar query.

If B returns 100 w, a may be scanned times and you know it is dead.

All scalar queries are rewritten to select... from a left join B .....

4. SQL nested udfs, packages, and stored procedures are prohibited

The principle is the same as that of scalar queries.

5. It is strictly prohibited to select a view that contains ROWNUM create or replace view... select rownum

Influence on predicate push + view merge

6. order by is not allowed in the view.

Interference execution plan

7. It is strictly prohibited to apply more than two views, because changing the innermost view may affect the outermost High Cohesion and low coupling.

If there is a problem with the innermost view, all the SQL statements that call this view will go wrong, and all the rewrite code will die for you.

8. in exists, not in not exists is rewritten as with as (subquery)

Leave this blank. register now.

9. The pages cannot contain distinct, group by, union/union all, and order by can only be one table.

Come sign up, don't say why?

10. Associate update, which can be changed to merge or rowid update

11. Do not use a function for the join column, such as where trunc (time) = B. Time

12. Note implicit conversion.

13. omitted... registration is available