You Asked

Greetings,

Mongo JSON/BSON provides what seem like two cool improvements:

1.) Denormalization through embedding documents: https://www.mongodb.com/docs/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/
2.) Design patterns to optimize access to data: https://www.mongodb.com/blog/post/building-with-patterns-a-summary

I would think that with OSON, we can denormalize/embed documents by simply creating multiple JSON data types within a single table. Is there a limit to the number of JSON data types per table? Are there any potential performance problems to watch out for?

Regarding Mongo design patterns to optimize access to data, does Oracle OSON have their own design patterns? Finally, design patterns seem like they optimize data access, why hasn't Oracle RDBMS implemented their own design patterns?

Thanks,

John

and Connor said...

If there's a limit, its more than 100 :-)

SQL> create table t (
  2   j1 json
  3  ,j2 json
  4  ,j3 json
  5  ,j4 json
  6  ,j5 json
  7  ,j6 json
  8  ,j7 json
  9  ,j8 json
 10  ,j9 json
 11  ,j10 json
 12  ,j11 json
 13  ,j12 json
 14  ,j13 json
 15  ,j14 json
 16  ,j15 json
 17  ,j16 json
 18  ,j17 json
 19  ,j18 json
 20  ,j19 json
 21  ,j20 json
 22  ,j21 json
 23  ,j22 json
 24  ,j23 json
 25  ,j24 json
 26  ,j25 json
 27  ,j26 json
 28  ,j27 json
 29  ,j28 json
 30  ,j29 json
 31  ,j30 json
 32  ,j31 json
 33  ,j32 json
 34  ,j33 json
 35  ,j34 json
 36  ,j35 json
 37  ,j36 json
 38  ,j37 json
 39  ,j38 json
 40  ,j39 json
 41  ,j40 json
 42  ,j41 json
 43  ,j42 json
 44  ,j43 json
 45  ,j44 json
 46  ,j45 json
 47  ,j46 json
 48  ,j47 json
 49  ,j48 json
 50  ,j49 json
 51  ,j50 json
 52  ,j51 json
 53  ,j52 json
 54  ,j53 json
 55  ,j54 json
 56  ,j55 json
 57  ,j56 json
 58  ,j57 json
 59  ,j58 json
 60  ,j59 json
 61  ,j60 json
 62  ,j61 json
 63  ,j62 json
 64  ,j63 json
 65  ,j64 json
 66  ,j65 json
 67  ,j66 json
 68  ,j67 json
 69  ,j68 json
 70  ,j69 json
 71  ,j70 json
 72  ,j71 json
 73  ,j72 json
 74  ,j73 json
 75  ,j74 json
 76  ,j75 json
 77  ,j76 json
 78  ,j77 json
 79  ,j78 json
 80  ,j79 json
 81  ,j80 json
 82  ,j81 json
 83  ,j82 json
 84  ,j83 json
 85  ,j84 json
 86  ,j85 json
 87  ,j86 json
 88  ,j87 json
 89  ,j88 json
 90  ,j89 json
 91  ,j90 json
 92  ,j91 json
 93  ,j92 json
 94  ,j93 json
 95  ,j94 json
 96  ,j95 json
 97  ,j96 json
 98  ,j97 json
 99  ,j98 json
100  ,j99 json
101  ,j100 json);

Table created.

SQL>

In terms of design patterns, most of those techniques stated seem to aimed at overcoming shortfalls with Mongo or JSON storage in general.

Unlike Mongo, Oracle can handle JSON as JSON, or as relational with either JSON_TABLE or (in 23ai) duality views, so (in my opinion) we don't need to head down the track of compromising how you store data just to overcome deficiencies in the product

Rating

(2 ratings)

Comments

John Cantu, November 01, 2024 - 12:18 pm UTC

Hi Connor,

Thanks for confirming. I wanted to try it, but I don't have Oracle installed anymore. After 25 years of using Oracle, I haven't touched it in almost 3 years because my focus has been solely on MongoDB.

Also, sorry, I just dump the design pattern webpage full of information. Let me provide a specific scenario and ask if this approach would be helpful in Oracle:

Lets say we have an entity that stores the theaters and another entity that stores the movies. A website is developed to only show the latest three movies. To improve read performance, the developers decide to store the latest three movies for each theatre within the "theaters" entity. This will eliminate having to perform an expensive join between the two tables. I understand that now the list of three movies has to be maintain in the "theatre" entity, but there will be thousands of requests to see the list of movies in a theatre versus the a few updates per week on the list of movies. This is known as the "subset" design pattern in Mongo. Would Oracle benefit from implementing this approach?

November 01, 2024 - 2:00 pm UTC

The major design pattern for Oracle Database is the relational model. A key point of this is how you store the data is independent of how you access it.

Whenever you start duplicating data you increase complexity and the odds of data errors. The performance benefits of duplicating data need to be significant to offset these costs.

In the theatres/movies example, I would expect an M:M junction table between these. The query to find the three most recent movies would be something like:

select * from theatres
join theatre_movies 
using ( theatre_id )
join movies 
using ( movie_id )
order by release_date desc
fetch first 3 rows only;

Joins in Oracle Database are efficient. In most cases, this query will be faster than fast enough.

If it's not, there are various options to speed it up.

You could store the three recent releases on the theatres table. While this eliminates the joins, it adds complexity. You have to write the code to keep these in sync, think about how to handle failures in this process, etc.

A simpler option is to create an MV for the query that refreshes on a regular schedule. The database will then keep it in sync for you.

TL;DR the subset or similar design patterns might be useful in some cases in Oracle Database, but is often unnecessary. In cases where it's of significant enough benefit to be worth considering there's usually an alternative that gives a similar benefit without having to copy data.

Amazing Answers!

John, November 04, 2024 - 4:26 pm UTC

Thank you for providing great responses to this new big topic!

November 04, 2024 - 6:44 pm UTC

You're welcome

More to Explore

JSON

Need more information on JSON? Check out the JSON dev guide for the Oracle Database

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Amazing Answers!

More to Explore

JSON

Oracle OSON: Denormalization and Design Patterns?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Amazing Answers!

More to Explore

JSON