Oracle Tuning analysis of correlation design

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.