Oracle Tuning analysis of correlation design

An analysis of relevance design

Paradigm

Ideally, no matter what a table in the database will be corresponding to a real life object, such as the 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"

For associative design to be able to understand the relationship between a table and a table, the associated query is 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 very well understood, this is to not redundant storage of data, the results of the same game will not be stored repeatedly, such as Brazil to Germany 0:0 and Brazil 0:0 (because the same two teams, the result must be the same), so that there is only one row of data, As a result, data independence is maintained.

"Not good paradigm, associated design examples"

Assuming that the incorrect paradigm, the associated design raises the cost of the association, for example, the cost of both tables is very large, and there will be a very large cost problem in connection with the equivalent and not the equivalent. If the goal of the World Cup is related to the player's performance during a season in the club, such as the number of goals, fouls, steals, and so on, and the data is in a larger table, the associated costs for the two tables will be very large. Since the scoring team in the World Cup is likely to have a very large number of rows in the season table, and every player is like this, one row of data in the first table corresponds to a very large number of rows in the second table, so the cost is quite high during the cross-linking process. This leads to a more popular technical trend, the inverse paradigm.

"Inverse Paradigm"

Anti-paradigm, breaking old good designs, and beneficial use of redundant data.

Give a sample, for example, to

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

as above, table 1 is the World Cup player stats and table 2 shows the players ' 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 has scored the goal of Ronaldinho in the club performance, that is to relate to the World Cup scoring players in the club's performance, to the first form of association, first know Ronaldinho's ID number, and then find the corresponding club performance. Table 1, a row of data to the corresponding to the 1 columns of data in table 2, table 2 of the 1 columns of data to the corresponding table 1 of the 1 rows of data, 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, assuming that 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, it is directly to the table 2 to query the corresponding data information can be. 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.

Oracle Tuning analysis of correlation design