Oracle horizontal/vertical table usage and mutual conversion between them

In normal development, you may encounter adding fields or better maintenance and management of tables with large data volumes.

Oracle horizontal/vertical tables and their mutual conversion may encounter field addition or better maintenance and management of large data tables during normal development.

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 field addition or better maintenance and management of large data tables, which involves 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

Converted table structure:

FNAME ZAOCAN_VALUE ZHONGCAN_VALUE WANCAN_VALUE

SQL example of converting a vertical table to a horizontal table:

Select fname,

FROM TEST_Z2H

Group by fname;

Horizontal table to vertical table

Horizontal table structure: TEST_H2Z

ID name Chinese maths English

Converted table structure:

ID name subject score

SQL example of horizontal table to vertical table:

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