Oracle SQL - multiple JOINS performance Issue

Question

I'm currently trying to write a query for an oracle DB to get some information about a patient's hospital stay. For every day he gets a specific score in specific fields - therefore I have to join multiple tables to get the actual daily score (not the full score).

My first attempt (which is working, but I want to show it in another format) was this:

SELECT
    f.FALLID AS "ID",
    f.FALLNR AS "Fallnummer",
    DECODE(e.SCOREID, 12, 'SAPS', 13, 'TISS', 'X') AS "Score",
    to_char(e.VON, 'DD.MM.YYYY') AS "Datum (von)",
    -- to_char(e.BIS, 'DD.MM.YYYY') AS "Datum (bis)",
    scp.BEZEICHNUNG AS "Bezeichnung",
    s.PUNKTE AS "Punkte"
FROM
    FALL f
    JOIN OS_MUP.EINSTUFUNG e ON e.FALLID = f.FALLID
    JOIN OS_MUP.EINSTUFUNGSRANG einr ON einr.EINSTUFUNGID = e.EINSTUFUNGID
    JOIN OS_MUP.SCORERANG s ON s.SCORERANGID = einr.SCORERANGID
    JOIN OS_MUP.SCOREPOSITION scp ON scp.SCOREPOSITIONID = s.SCOREPOSITIONID
WHERE
    f.FALLNR = 1234567
ORDER BY
    e.SCOREID, e.VON

This is showing the results in the following form:

+------+---------+-------+------------+-------------+--------+
|  ID  | Fallnr  | Score |    Date    | Description | Points |
+------+---------+-------+------------+-------------+--------+
| 1234 | 1234567 | ABCD  | 05.05.2020 | Age         |     12 |
| 1234 | 1234567 | ABCD  | 05.05.2020 | Temperature |      2 |
+------+---------+-------+------------+-------------+--------+

That's okay, but I don't want the repeating column for the date and the description for every day, I wanted to get like that appearance:

+------+---------+-------+------------+-----+-------------+
|  ID  | Fallnr  | Score |    Date    | Age | Temperature |
+------+---------+-------+------------+-----+-------------+
| 1234 | 1234567 | ABCD  | 05.05.2020 |  12 |           2 |
| 1234 | 1234567 | ABCD  | 06.05.2020 |  12 |           2 |
+------+---------+-------+------------+-----+-------------+

So I reworked the query and finished with this at the moment:

variable var number
exec :var := 2068237610

SELECT
    dt1.FALLNR AS "Fallnummer",
    to_char(dt1.VON, 'DD.MM.YYYY') AS "Datum",
    dt1.PUNKTE AS "Alter",
    dt2.PUNKTE AS "Herzfrequenz"
    --dt3.PUNKTE AS "RR Syst.",
    --dt4.PUNKTE AS "Körpertemp.",
    --dt5.PUNKTE AS "PaO2/FiO2"
    -- dt6.PUNKTE AS "Urin",
    -- dt7.PUNKTE AS "Leukozyten"
    FROM (
    SELECT
        f.FALLID,
        f.FALLNR,
        s.PUNKTE,
        e.VON
    FROM Fall f
        JOIN OS_MUP.EINSTUFUNG e ON e.FALLID = f.FALLID
        JOIN OS_MUP.EINSTUFUNGSRANG einr ON einr.EINSTUFUNGID = e.EINSTUFUNGID
        JOIN OS_MUP.SCORERANG s ON s.SCORERANGID = einr.SCORERANGID
    WHERE
        s.SCOREPOSITIONID = 10003 AND f.FALLNR = :var /* Alter */
    ) dt1
JOIN
    (SELECT
        f.FALLNR,
        s.PUNKTE,
        e.VON
    FROM Fall f
        JOIN OS_MUP.EINSTUFUNG e ON e.FALLID = f.FALLID
        JOIN OS_MUP.EINSTUFUNGSRANG einr ON einr.EINSTUFUNGID = e.EINSTUFUNGID
        JOIN OS_MUP.SCORERANG s ON s.SCORERANGID = einr.SCORERANGID
    WHERE
        s.SCOREPOSITIONID = 10004 AND f.FALLNR = :var /* Herzfrequenz */
    ) dt2
ON dt1.FALLNR = dt2.FALLNR AND dt1.VON = dt2.VON
ORDER BY dt1.VON

This is working as I want it to work, but I'd have to do those joins over and over again for more columns, and there are like 5-6 more to come - and cause of the nested loops created by the joins this version of the query is extremely slow (like 250 seconds slow).

Is there a chance to achieve my goal in another way of with less use of joins?

Thanks in advance!

Answer 1

How to do a Transpose

One option is a Pivot: https://www.oracletutorial.com/oracle-basics/oracle-pivot/ Or more options: https://livesql.oracle.com/apex/livesql/file/tutorial_GNZ3LQPJ0K6RTD1NEEPNRQT0R.html

Main drawbacks:

• fixed list of items for direct SQL
• only XML output for dynamic items

Then there is the old fashioned way:

• prepare the data first
• fold over columns to output and assign
• group

Something along the lines like:

WITH
  cte_position
(
  key_position,
  name_position
)
AS
( -- manual preparation of column name list
  SELECT Cast( 10003 AS number(6,0) ) AS key_position, Cast( 'Alter' AS varchar2(20 char) ) AS name_position FROM dual
  UNION ALL
  SELECT Cast( 10004 AS number(6,0) ) AS key_position, Cast( 'Herzfrequenz' AS varchar2(20 char) ) AS name_position FROM dual
),
  cte_data
(
  fallnr,
  punkte,
  column_group,
  von
)
AS
( -- combine data and prepare for single pass data collection
  SELECT
    f.FALLNR,
    e.VON,
    punkt_name.name_position AS column_group,
    s.PUNKTE
  FROM Fall f
    INNER JOIN OS_MUP.EINSTUFUNG e 
      ON  e.FALLID = f.FALLID
    INNER JOIN OS_MUP.EINSTUFUNGSRANG einr 
      ON  einr.EINSTUFUNGID = e.EINSTUFUNGID
    INNER JOIN OS_MUP.SCORERANG s 
      ON  s.SCORERANGID = einr.SCORERANGID
    -- link (and filter) by required positions to show
    INNER JOIN cte_position punkt_name
      ON  s.SCOREPOSITIONID = punkt_name.key_position
  WHERE -- specific patient
      f.FALLNR = :var 
)
SELECT
  patient.fallnr AS Fallnummer,
  To_char( patient.von, 'DD.MM.YYYY' ) AS Datum, -- non ISO 8601 date
  Max( CASE WHEN patient.column_group = 'Alter' THEN patient.punkte ELSE 0 END ) AS "Punkt Alter",
  Max( CASE WHEN patient.column_group = 'Herzfrequenz' THEN patient.punkte ELSE 0 END ) AS "Punkt Herzfrequenz"
FROM
  cte_data patient
GROUP BY
  patient.fallnr,
  patient.von
;

Again there are some downsides to this approach:

• each Max function has to be named individually

On the other hand this is a single pass to capture the data which in theory makes the data collection faster. If not then there is an index missing on the patient code.

Answer 2

Take your original query and use PIVOT on it:

SELECT *
FROM   (
  SELECT f.FALLID AS ID,
         f.FALLNR AS Fallnummer,
         DECODE(e.SCOREID, 12, 'SAPS', 13, 'TISS', 'X') AS Score,
         TO_CHAR(e.VON, 'DD.MM.YYYY') AS Datum,
         scp.BEZEICHNUNG,
         s.PUNKTE
  FROM   FALL f
         INNER JOIN OS_MUP.EINSTUFUNG e
         ON ( e.FALLID = f.FALLID )
         INNER JOIN OS_MUP.EINSTUFUNGSRANG einr
         ON ( einr.EINSTUFUNGID = e.EINSTUFUNGID )
         INNER JOIN OS_MUP.SCORERANG s
         ON ( s.SCORERANGID = einr.SCORERANGID )
         JOIN OS_MUP.SCOREPOSITION scp
         ON ( scp.SCOREPOSITIONID = s.SCOREPOSITIONID )
  WHERE  f.FALLNR = 1234567
)
PIVOT (
  MAX( Punkte ) FOR BEZEICHNUNG IN (
    'Age' AS Age,
    'Temperature' AS Temperature,
    'Alter' AS Alter,
    'Herzfrequenz' AS Herzfrequenz,
    'RR Syst.' AS "RR Syst.",
    'Körpertemp' AS "Körpertemp.",
    'PaO2/FiO2' AS "PaO2/FiO2"
    -- ...
  )
)
ORDER BY
       SCOREID,
       VON