Using a single field in Oracle SQL with multiple JSON values inside. How slow is it to query this?

Question

We currently have an audit table of the following form:

TABLE(timestamp, type, data, id) -- all the fields are of type varchar / clob

The issue is that for some types there are actually several well suited ids. We currently only store one of those but it would be interesting to be able to query by any of those ids while still keeping only one column for that.

With Oracle's recent support for JSON we were thinking about maybe storing all the ids as a JSON object:

{ orderId:"xyz123"; salesId:"31232131" }

This would be interesting if we could continue to make queries by id with very good performance. Is this possible? Does Oracle allow for indexing in these kind of situations or would it always end up being a O(n) text search over all the millions of rows this table has?

Thanks

Gordon Linoff · Accepted Answer · 2017-08-21 14:11:43Z

1

Although you can start fiddling around with features such as JSON types, nested tables and the appropriate indexing methods, I wouldn't recommend that unless you specifically want to enhance your Oracle skills.

Instead, you can store the ids in a junction table:

table_id    synonym_id
  1            1
  1            10
  1            100
  2            2

With an index on synonym_id, table_id, looking up the synonym should be really fast. This should be simple to maintain. You can guarantee that a given synonym only applies to one id. You can hide the join in a view, if you like.

Of course, you can go down another route, but this should be quite efficient.

answered Aug 21, 2017 at 14:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

devoured elysium Over a year ago

We use this just as a dump. We aren't really interested in using this as a normalized database.

Gordon Linoff Over a year ago

@devouredelysium . . . If you want to search by the ids, I wouldn't call it a "dump" but a "database".

Beda Hammerschmidt · Accepted Answer · 2017-09-07 02:49:20Z

0

1)Oracle's JSON support allows functional indexing on the JSON data (b-tree index on JSON_VALUE virtual column.) 2) for non-indexed fields the json date needs to be parsed to get to the field values. Oracle uses a streaming parser with early termination. This means that fields at the beginning of the JSON data are found faster than those at the end.

answered Sep 7, 2017 at 2:49

Beda Hammerschmidt

4463 silver badges6 bronze badges

Collectives™ on Stack Overflow

Using a single field in Oracle SQL with multiple JSON values inside. How slow is it to query this?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related