Ndtyjky

I need some guidance on how to approach this problem.

Consider the following 3 tables:

I inherited this dataset in Oracle. I need to find a way to merge these 3 tables into a single table. Why? Compliance and fraud checks. We're being audited, I have no choice but to comply.

The tables are millions of records. 69million. 7 million, 4 million. If I try to join and search by using contains, or instr or like, I'd be creating Cartesian joins (I think) and it will be quite slow.

How do I approach this? The data is formatted with the square brackets as in the picture. I have considered pulling these tables into pandas and using python to get the answer.

The resulting table would look like this:

I think your first step would be to get the meals and teams tables in a usable format. Using these weird concatenation columns is never going to be easy or fast.

Doing something simple like this to split the ids onto different rows:

select trim(column_value) partner_id,

       team

  from teams,

       xmltable(('"' || replace(teams.concat_identifier_partner, ']',']","') || '"'))

Gives an output like this:

+------------+------+

| PARTNER_ID | TEAM |

+------------+------+

| [012345]   | Blue |

| [012346]   | Blue |

| [012347]   | Red  |

+------------+------+

Then you could use these as inline views in your query or create a new temporary table for each of these. The temporary table would allow you to index them on the new ID column.

After your data is in the easier format, the joins become easy.

select people.identifier,

       people.name,

       formatted_teams.team,

       formatted_meals.meal

from people

join (select trim(column_value) partner_id,

              team

       from teams,

       xmltable(('"' || replace(teams.concat_identifier_partner, ']',']","') || '"')) ) formatted_teams

  on people.identifier = formatted_teams.partner_id

left join (select trim(column_value) plate_id,

            meal

       from meals,

       xmltable(('"' || replace(meals.concat_identifier_plate, ']',']","') || '"')) ) formatted_meals

  on people.identifier = formatted_meals.plate_id

order by people.identifier

Output:

+------------+--------+------+--------+

| IDENTIFIER |  NAME  | TEAM |  MEAL  |

+------------+--------+------+--------+

| [012345]   | George | Blue | Salmon |

| [012346]   | Jenny  | Blue |        |

| [012347]   | Alex   | Red  | Pike   |

| [012347]   | Alex   | Red  | Cod    |

+------------+--------+------+--------+