CODEWITHSUNDEEP
sqlperformanceoracle-databasedatabase
Hector
Hector

Reputation: 1220

SQL Query, Trying to improve efficiency (Oracle DBMS)

For the following tables (I've used ** to indicate the parts of the primary keys)

Team (*tid*, name)
Game (*gid*, tid, name, year)
Member (*mid*, name)
MemberOf (*mid*, *tid*, startyear, endyear, position)

I need to Show The History of each Striker (position) If startyear is NULL use the year from the earliest game for that player for that team Result: Striker Name, Team Name, Start Year, End Year Sorted by the Strikers name, then the start year

Whilst I have a solution I dont think it very efficient The Execution plan on infers a node cost of 28. Was Hoping for improvement suggestions and explanations?

Upvotes: 2

Views: 258

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

You can do this with one query, using left outer join and coalesce (if I have the logic correct):

SELECT Striker_Name, Team_Name, coalesce(MO.STARTYEAR, g.start_year), MO.ENDYEAR
FROM (SELECT NAME AS Striker_Name, MID
      FROM MEMBER
      ) ME JOIN
      (SELECT MID, TID, STARTYEAR AS Start_Year, ENDYEAR AS End_Year
       FROM MEMBEROF
       WHERE POSITION = 'striker'
      ) MO ON ME.MID = MO.MID left outer JOIN
      (SELECT NAME AS Team_Name, TID
       FROM TEAM
      ) T
      ON MO.TID = T.TID left outer JOIN
      (SELECT GID, MIN(GAME.YEAR) AS Start_Year FROM GAME GROUP BY TID
      ) G
      ON G.TID = MO.TID
ORDER BY Striker_Name, Start_Year

You seem to be filling in the start year from the earliest game. Just do the join and decide in the select which one to use.

Upvotes: 2

Related Questions