I. Horizontal and vertical tables
Horizontal table: usually refers to the table we create in the database at ordinary times. It is a common table creation method.
(Primary Key, Field 1, Field 2...) such as: time, customer ID, basic call fee, roaming call fee, domestic long-distance fee, International Long-distance fee .....
Vertical table: it is usually rare. When the table structure is uncertain, it is a table creation method when fields need to be added.
Ii. execution efficiency
Horizontal table: the background database administrator is simple, intuitive, clear, and clear. However, to add one or more fields to the horizontal table, the table structure must be rebuilt.
Vertical table: for horizontal tables, you only need to add one record to the vertical table to add a field. The cost is far smaller than that of the horizontal table. However, the data description of the vertical table is not clear, and the number of databases is large. Using group and other functions in queries greatly reduces execution efficiency. The initial ing of the vertical table is slower, and the change ing of the vertical table may be faster. If only a single field is changed, the horizontal table field is much more than the vertical table after all.
Iii. Conversion
1. During normal development, you may encounter adding fields or better maintenance and management of tables with large data volumes, which will involve conversion between vertical tables and horizontal tables.
2. Design a table structure that is not easy to change into a horizontal table, and design a table structure that is easy to change frequently and uncertain into a vertical table.
Example:
Note:DECODE is one of the powerful functions of Oracle PL/SQL. Currently, only oracle SQL provides this function. DECODE (value, if1, then1, if2, then2, if3, then3 ,..., else), indicating that if the value is equal to if1, the result of the DECODE function returns then1 ,..., if it is not equal to any if value, else is returned.
Sign Function: in mathematics and computer operations, the function is to take a certain number of symbols (positive or negative): When x ≥ 0, sign (x) = 1; when x <0, sign (x) =-1;
Vertical table to horizontal table
- Vertical table structure: TEST_Z2H
- FNAME FTYPE FVALUE
- Employee zaocan10
- Employee zhongcan20
- Employee wancan5
-
- Converted table structure:
- FNAME ZAOCAN_VALUE ZHONGCAN_VALUE WANCAN_VALUE
- Employee10205
-
- SQL example of converting a vertical table to a horizontal table:
- Select fname,
- SUM (DECODE (FTYPE,'Zaocan', FVALUE,0) AS ZAOCAN_VALUE,
- SUM (DECODE (FTYPE,'Zhongcan', FVALUE,0) AS ZHONGCAN_VALUE,
- SUM (DECODE (FTYPE,'Wancan', FVALUE,0) AS WANCAN_VALUE
- FROM TEST_Z2H
- Group by fname;
Horizontal table to vertical table
- Horizontal table structure: TEST_H2Z
- ID name Chinese maths English
- 1Zhang San809070
- 2Li Si908595
- 3Wang Wu887590
-
- Converted table structure:
- ID name subject score
- 1Zhang San Chinese80
- 2James math90
- 3James English70
- 4Li Si Chinese90
- 5Li Si mathematics80
- 6Li Si English99
- 7Wang Wu85
- 8Wang Wu's mathematics96
- 9Wang Wu English88
- SQL example of horizontal table to vertical table:
- SELECT name,'China'AS subject, Chinese AS score FROM TEST_H2Z UNION ALL
- SELECT name,'Mat'AS subject, mathematics AS score FROM TEST_H2Z UNION ALL
- SELECT name,'English'AS subject, English AS score FROM TEST_H2Z
- Order by name, subject DESC;
4. Here is an article about using another method to achieve conversion and querying with and values. For more information about Oracle horizontal tables and vertical tables, see