Avoid Joining The Same Table 3 Times

oracleoracle-12c

I have this database schema on a Oracle 12c database :

I'm trying to answer this question :

What is the game in which Dallas Mavericks had the biggest percentage of successful 3-point shots ?

I've managed to answer this question with this query:

SELECT HOME_TEAMS.TEAM_NAME                    "HOME TEAM",
       AWAY_TEAMS.TEAM_NAME                    "AWAY TEAM",
       GAMES.GAME_DATE                         "DATE",
       TEAMS.TEAM_ID                           "GENERIC ID",
       HOME_TEAMS.TEAM_ID                      "HOME_ID",
       AWAY_TEAMS.TEAM_ID                      "AWAY_ID",
       GAME_STATISTICS.X3POINTSHOTS_PERCENTAGE "3 POINTS PERCENTAGE"
FROM GAMES_TEAMS_STATISTICS
         INNER JOIN GAMES ON GAMES_TEAMS_STATISTICS.GAME_ID = GAMES.GAME_ID
         INNER JOIN TEAMS ON GAMES_TEAMS_STATISTICS.TEAM_ID = TEAMS.TEAM_ID
         INNER JOIN TEAMS HOME_TEAMS ON GAMES.HOME_TEAM = HOME_TEAMS.TEAM_ID
         INNER JOIN TEAMS AWAY_TEAMS ON GAMES.AWAY_TEAM = AWAY_TEAMS.TEAM_ID
         INNER JOIN GAME_STATISTICS ON GAMES_TEAMS_STATISTICS.STATS_ID = GAME_STATISTICS.STATS_ID
WHERE TEAMS.TEAM_ACRONYM = 'DAL'
ORDER BY GAME_STATISTICS.X3POINTSHOTS_PERCENTAGE DESC
    FETCH FIRST 1 ROWS WITH TIES;

However, to answer this question , I join the teams table 3 times. Is there any way I can write this query in a more efficient way , avoiding so many joins ?

Best Answer

Remove HOME_TEAMand AWAY_TEAM columns from the GAMES table and add a HOME_AWAY flag column to theGAME_TEAM_STATISTICS table.

Right now, you need to code for an ASSERTION (because they do not yet exist) to ensure the GAME_ID+TEAM_ID combo in GAME_TEAM_STATISTICS matches the GAMES.HOME_TEAM/GAMES.AWAY_TEAM information.

Related Solutions

Can two transactions share the same SCN

For single node Oracle RDBMS installations, an SCN is never duplicated.

With older versions of Oracle RAC, Oracle 9i for example, it was possible that two transactions, running on separate RAC nodes, could end up with the same SCN. Duplicates happened only in rare situations where high transaction rates on the RAC nodes was coupled with a large value for MAX_COMMIT_PROPAGATION_DELAY.

In Oracle RAC installations of version 11gR2 and later, RAC nodes coordinate changes to the SCN via a broadcast mechanism by default, essentially eliminating the possibility of a duplicate SCN.

How to manipulate the data contained in a quiz database

Some suggestions for future projects. What does the name E20079852 represent? I notice that all your tables have this prefix, so I guess it is some kind of grouping of tables, to indicate that they belong together. You can use a schema for that.

CREATE TABLE E20079852.PROJ 
( PID NUMBER(4) NOT NULL 
, PROJNAME VARCHAR2(20) NOT NULL 
, DID NUMBER(4) NOT NULL
, PROJCOST NUMBER(20) NOT NULL 
, CONSTRAINT PROJ_PK PRIMARY KEY 
  (
    PID 
  )
  ENABLE 
);

I also think you should come up with a better name for the schema, what domain do these tables represent? I assume that the table that is named E20079852 represents USERS or something similar:

CREATE TABLE E20079852.USERS 
( EID NUMBER(4) NOT NULL 
, EFNAME VARCHAR2(20) NOT NULL 
, ELNAME VARCHAR2(20) NOT NULL 
, EDOB DATE NOT NULL 
, EDOJ DATE NOT NULL 
, EDOR DATE NOT NULL 
, DID NUMBER(4) NOT NULL 
, CONSTRAINT USERS_PK PRIMARY KEY (EID)
  ENABLE 
);

For the understanding of the schema (not to mention consistency of the information), declare referential constraints. I assume this is the case, but it is only a guess:

CREATE TABLE E20079852.ASSIGNMENTS 
( PID NUMBER(4) NOT NULL
      REFERENCES E20079852.PROJECTS(PID) 
, ... 
);

Also, don't use "," separated lists, at least not for information that references other things. I would suggest:

CREATE TABLE E20079852.ASSIGNMENTS 
( PID NUMBER(4) NOT NULL
      REFERENCES E20079852.PROJECTS(PID)
, EID NUMBER(4) NOT NULL
      REFERENCES E20079852.USERS(EID)
, ...
, CONSTRAINT ASSIGNMENTS_PK PRIMARY KEY (PID, EID)
  ENABLE 
);

If you represent ASSIGNMENTS this way it is very easy to answer question number one:

SELECT EID
FROM E20079852.ASSIGNMENTS 
GROUP BY EID
HAVING COUNT(1) > 1

With your current schema you need to split the EID string into its atoms, and the use a similar query as above.

Best Answer

Related Solutions

Can two transactions share the same SCN

How to manipulate the data contained in a quiz database

Related Question