Oracle optimization analysis association Design

Analysis of association Design

[Paradigm]

Ideally, any table in the database will correspond to an object in real life. For example, a player is an object, a team is an object, and a competition is an object, the competition result is an object, and so on, it is a paradigm.

[Association design]

The Association design can be understood as the relationship between the table and the table. Association queries are often used in table queries.

Supplement: Source of Relational Database: read from multiple tables for one transaction operation.

For example, in the table space of the 2014 World Cup in Brazil, there must be a player table, a competition table, and a results table. The results table must be associated with the name of the team and the name of the player, this is a simple association. It is easy to understand why the design should be a paradigm. This is to store data without redundancy, so that the results of the same game do not need to be stored repeatedly, for example, if Brazil is 0: 0 against Germany and Brazil is 0: 0 (because the two teams are the same, the results must be the same), only one row of data can exist, the result is that data independence is maintained.

[Examples of poor paradigm and associated design]

If a good paradigm or association design is poor, the associated costs may occur. For example, if the costs of both tables are high, there will be a lot of cost problems when doing equijoin and non-equijoin. For example, when a World Cup goal-scoring player is associated with the performance of the player in the club for a season, such as number of players, number of fouls, and number of steals, the data is stored in a large table, and the associated costs of the two tables will be high. Because a single row of record of the Goal players in the world cup may correspond to many rows of data in the season table, and this is true for every player. The data in the first table corresponds to many rows in the second table, in this cross-linking process, the cost is quite high. This leads to a more popular technical trend and anti-paradigm.

[Anti-paradigm]

The anti-paradigm breaks the old good design and deliberately uses redundant data.

For example

Table 1:

FIFA Player ID	Player name	Nationality	2014 World Cup goal	......
10982	Rongo	Portugal	6	......
23781	Bytes	Spain	5	......
12312	Muller	Germany	4	......
......

Table 2

FIFA Player ID	10982	23781	12312	......
Region	Europe	Europe	Europe	......
Club	Real Madrid	Madrid	Bayern Munich	......
2014 goals for the season	32	28	6	......
......

As shown in the preceding figure, table 1 shows the statistics of players in the World Cup. Table 2 shows the performance of players in the club. To avoid duplicate names of players, Table 2 uses the ID number as the identifier. Now I want to check the performance of rongo who scored well in the World Cup in the club, that is, to associate players who scored well in the World Cup with the performance of the club, I need to use the first table to associate, first, you need to know Luo's ID number, and then find the corresponding club performance. One row of data in table 1 corresponds to one column of data in table 2, and one column of data in Table 2 corresponds to one row of data in table 1, the associated costs are quite high. Although the two tables conform to the good paradigm and associativity, they prevent redundancy and conflict, but the performance is very poor. The corresponding improvement is to directly enter the player name without writing the ID number. If there is a duplicate name, the player will be identified in the background database. To query the performance of a player club, you can directly query the corresponding data in table 2. From the design perspective, there is no good paradigm and no good association design. However, performance analysis shows better performance than a good paradigm and associated design.