“Tenbagger” is a term coined by legendary investor Peter Lynch to describe stocks that provide tenfold value on the initial investment. As software engineers we make scores of decisions every day in the face of ever-evolving requirements, whose impact on the architecture might only become visible down the line. It’s nice when a relatively low-effort code change ends up providing compounding benefits over a long time horizon.

I am currently working on a critical component in a distributed system for a food-delivery app. One of its core responsibilities is to receive information from an upstream service and pass it into downstream services. The feature requirements typically follow this pattern: customers can now provide a backup phone number, which should be shown on the delivery app. So an order passing through a chain of systems can now contain a new field, which needs to be forwarded to relevant downstream services.

A query plan is a sequence of steps used by a database to access data. Being able to read a query plan is key to understanding the performance of an SQL query. While tuning a query we need to know how the rows are being fetched from the tables? Are the indexes being used? What is the cost of joining to tables? A query plan provides with an answer for all of these questions.

In a relational database, the relationships between two tables are defined by foreign keys. Typically, one table has a column that contains the primary key of another table’s row. In JPA, we deal with entity objects that are Java representations of database tables. So we need a different way for establishing relationship between two entities. JPA entity relationships define how these entities refer to each other.

For the purpose of this article, I will work with JPA 2.0 and a table structure as following.

CREATE TABLE team(team_id NUMBER, name VARCHAR2(20));
INSERT INTO team(team_id, name) VALUES(1, 'india');
INSERT INTO team(team_id, name) VALUES(2, 'australia');
INSERT INTO team(team_id, name) VALUES(3, 'england');


CREATE TABLE player(player_id NUMBER, name VARCHAR2(50), team_id NUMBER, age NUMBER, role VARCHAR2(20));
INSERT INTO player(player_id, name, team_id, age, role) VALUES(1, 'sachin', 1, 42, 'batsman');
INSERT INTO player(player_id, name, team_id, age, role) VALUES(2, 'dhoni', 1, 34, 'wicketkeeper');
INSERT INTO player(player_id, name, team_id, age, role) VALUES(3, 'clarke', 2, 38, 'batsman');
INSERT INTO player(player_id, name, team_id, age, role) VALUES(4, 'rogers', 2, 40, 'batsman');
INSERT INTO player(player_id, name, team_id, age, role) VALUES(5, 'cook', 3, 31, 'batsman');
INSERT INTO player(player_id, name, team_id, age, role) VALUES(6, 'root', 3, 26, 'batsman');

CREATE TABLE player_stat(player_stat_id NUMBER, player_id NUMBER, runs NUMBER, wickets NUMBER);
INSERT INTO player_stat(player_stat_id, player_id, runs, wickets) VALUES(10, 1, 10000, 300);
INSERT INTO player_stat(player_stat_id, player_id, runs, wickets) VALUES(20, 2, 5000, 10);
INSERT INTO player_stat(player_stat_id, player_id, runs, wickets) VALUES(30, 3, 7000, 100);
INSERT INTO player_stat(player_stat_id, player_id, runs, wickets) VALUES(40, 4, 2000, 0);
INSERT INTO player_stat(player_stat_id, player_id, runs, wickets) VALUES(50, 5, 9000, 0);