Ways to the Bacon

Is Bacon something you eat or calculate? Both. The Bacon Number of an actor is the lowest degree of separation from Kevin Bacon defined by linking via actors appearing in same movie cast. How to calculate Bacon in Oracle? We’ll look at different ways to do this with SQL, PL/SQL and/or graphs.

Six Degrees of Kevin Bacon is about linking actors by movies where they have worked together. The Bacon Number of an actor is the number of links in the shortest path from the actor to Kevin Bacon.

Calculation of the Bacon number is an interesting exercise (like given as a lab at Oberlin College) and of course we can do it in Oracle. But simple implementations might easily become excessively resource consuming for the database - we might need to do things like early pruning to get acceptable performance on larger datasets.

This talk investigates different Bacon calculation methods with SQL and PL/SQL as well as graph methods (remember that Graph option is now no-cost in supported database versions). We will look at how to map tabular data to a graph in a way that the two models can coexist without duplicating the data, and then perform path queries on the graph. These methods can be useful for other real sets of data besides movie data, such as bill-of-material data in the manufacturing industry.

For the live demo, we will use Graph Studio in Autonomous Database, which supports both SQL and PGQL (graph) queries, and includes visualization of the results as a graph. To wrap up, we will look at how the new GRAPH_TABLE() function in Oracle 23c will make these kinds of queries even more widely applicable.

The inspiration for this talk came from Bryn Llewellyn, who wrote about calculating Bacon Number using recursive subquery factoring.

In his blog post he uses recursion in YugabyteDB/PostgreSQL with some early pruning. This talk will investigate similar in Oracle and compare SQL and PL/SQL methods to using Graph, which is a no-cost option that many developers aren’t yet thinking of when deciding how to solve tasks similar to Bacon Number calculation.