Oracle horizontal/vertical table usage and mutual conversion between them

Source: Internet
Author: User

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

  1. Vertical table structure: TEST_Z2H
  2. FNAME FTYPE FVALUE
  3. Employee zaocan10
  4. Employee zhongcan20
  5. Employee wancan5
  6. Converted table structure:
  7. FNAME ZAOCAN_VALUE ZHONGCAN_VALUE WANCAN_VALUE
  8. Employee10205
  9. SQL example of converting a vertical table to a horizontal table:
  10. Select fname,
  11. SUM (DECODE (FTYPE,'Zaocan', FVALUE,0) AS ZAOCAN_VALUE,
  12. SUM (DECODE (FTYPE,'Zhongcan', FVALUE,0) AS ZHONGCAN_VALUE,
  13. SUM (DECODE (FTYPE,'Wancan', FVALUE,0) AS WANCAN_VALUE
  14. FROM TEST_Z2H
  15. Group by fname;

 Horizontal table to vertical table

  1. Horizontal table structure: TEST_H2Z
  2. ID name Chinese maths English
  3. 1Zhang San809070
  4. 2Li Si908595
  5. 3Wang Wu887590
  6. Converted table structure:
  7. ID name subject score
  8. 1Zhang San Chinese80
  9. 2James math90
  10. 3James English70
  11. 4Li Si Chinese90
  12. 5Li Si mathematics80
  13. 6Li Si English99
  14. 7Wang Wu85
  15. 8Wang Wu's mathematics96
  16. 9Wang Wu English88
  17. SQL example of horizontal table to vertical table:
  18. SELECT name,'China'AS subject, Chinese AS score FROM TEST_H2Z UNION ALL
  19. SELECT name,'Mat'AS subject, mathematics AS score FROM TEST_H2Z UNION ALL
  20. SELECT name,'English'AS subject, English AS score FROM TEST_H2Z
  21. 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

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.