SQL Encoding Specification

1. You must alias the table to make it easier to investigate which columns are used in the table

For example select Owner,object_id,name from a A, where a.id=b.id;

If I do not alias the table, how do I know which table you are accessing. If SQL hundreds of rows, if the SQL table is associated with a lot, go to hell.

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 tables

Data Warehouse fact Table _fact

Data Warehouse dimension table _dim

Business Intermediate Table _tmp

Log Table _log

This naming convention makes it easy for DBAs who are unfamiliar with the business to get started faster

3. Non-standard quantum query (pagination can be written)

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

If B returns 100w then a may be scanned 100w times and you know it's dead.

The scalar quantum query is all rewritten as Select ... from a LEFT join B ....

4. SQL set custom functions, packages, stored procedures are strictly forbidden.

The truth is the same as the scalar query.

5. No view in select contains rownum create or Replace view .... Select RowNum

Impact predicate push + view Merge

6. It is forbidden to have order by in the view

Interference Execution Plan

7. It is forbidden to apply more than 2 views, because the innermost view changes the high cohesion and low coupling which may affect the outermost

If the innermost view problems, then call this view of SQL all problems, rewrite code to kill you

8. In exists, no in not exists rewritten as with AS (subquery)

9. Pagination cannot have distinct, group by, Union/union All,order by only one table

10. Associated updates, rewritten as merge or rewritten to take advantage of the ROWID update

11. Disable the Join column with functions such as where trunc (time) =b. Time

12. Note Implicit conversions

SQL Encoding Specification