Oracle Tuning analysis of correlation design

Source: Internet
Author: User

An analysis of relevance design

Paradigm

Ideally, any table in the database will correspond to a real-life object, such as a player is an object, the team is an object, the schedule is an object, the result of the game is an object, and so on, is the paradigm.

"Associative Design"

Associative design can be understood as an association between tables and tables, and associated queries are often used when querying a table.

Supplement: The source of a relational database: a transaction operation to be read from multiple tables.

such as the 2014 Brazil World Cup in this table space to have the player table, the schedule, the results of the table, the results of the match table to associate the team name, the name of the player last associated with a game results, this is a simple association. As for why design paradigm, it is also well understood, this is to not redundant storage of data, the results of the same game will not be duplicated storage, such as Brazil to Germany 0:0 and Germany Brazil is also 0:0 (because the same two teams, the result must be the same), so that there can be only one row of data, As a result, data independence is maintained.

"Not good paradigm, associated design examples"

If an incorrect paradigm, the associated design raises the cost of the association, for example, if the cost of both tables is large, there will be a significant cost problem in connection with the equivalent and not the equivalent. If the goal of the World Cup is linked to the player's performance in the club for a season, such as goals, fouls, steals, and so on, and the data is in a larger table, the cost of the correlation between the two tables will be significant. Because a team of scoring players in the World Cup may correspond to many of the rows in the season table, and every player is like this, one row of data in the first table corresponds to many rows of data in the second table, and the cost is quite high during this cross-linking process. This also leads to the goal of more popular technical trends, anti-paradigm.

"Inverse Paradigm"

Anti-paradigm, breaking the old good design, and intentionally using the existence of redundant data.

As an example,

Table 1:

FIFA player ID

Player name

Nationality

2014 World Cup goals

......

10982

Ronaldinho

Portugal

6

......

23781

Villa

Spain

5

......

12312

Muller

Germany

4

......

...... Million bar

Table 2

Europe

TD valign= "top" >

FIFA player ID

10982

23781

12312

......

Region

Europe

Europe

...

Club

Real Madrid

Atletico Madrid

Bayern Munich

...

2014-season goal

+

-

6

...

... Million bar

             above, table 1 is the World Cup player stats, table 2 is the player's performance in the club. To avoid the name of the player, table 2 uses the ID number as the identification. Now want to see the World Cup performance is very good with the goal of Ronaldinho in the club performance, that is, to relate to the World Cup scoring players in the club performance, to be linked through the first table, first know Ronaldinho's ID number, and then find the corresponding club performance. Table 1 is a row of data to correspond to the 1 columns of data in table 2, table 2 of the 1 column of data also corresponds to 1 rows of data in table 1, in the two large tables in the million bar, this association cost is quite high. Although the two tables conform to the good paradigm, the correlation, prevents the redundancy, prevents the conflict, but in the performance is very poor. The corresponding improvement is not to write the ID number, but directly fill in the player's name, if there is a duplicate in the background database to make an identity. In this case, to inquire about the performance of a player's club, directly to table 2 to query the corresponding data information. From a design point of view, there is no good paradigm, no good correlation design. However, performance analysis is better than a good paradigm and associated design.

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.