Error Clause And JSON Indexes

This post concludes my series on JSON indexes. It shows how the ERROR clause impacts index usage and query results. The code below relies on tables and indexes created in previous posts in this thread. Now lets get to the code… — — This query uses the JSON_4 index to quickly find the — desired … Read more

JSON Index Return Lengths

This is the fourth post investigating nuances of Oracle indexes on JSON data. The previous posts demonstrated how changing the length of returned data in an SQL JSON function call influences Oracle’s choice of index. This post begins to expose why that occurs by creating 3 indexes for 3 different return value lengths and showing … Read more

JSON Index Non Reuse

Indexes on JSON data elements  can be used by all SQL JSON functions (JSON_VALUE, JSON_QUERY, etc) using that element as criteria.  But the match must be exact including the RETURNING and ERROR clause. Here’s an example of and index not being re-used. — — Verify the virtual column used for the index — SQL> SELECT … Read more

JSON Index Reuse

The first post in this series created an index using Dot Notation to specify the JSON element to index. Behind the scenes Oracle translated the Dot Notation to a JSON_QUERY call to build the index. Subsequent queries using Dot Notation undergo the same translation to use the index. Can other functions such as JSON_VALUE use … Read more

JSON Index 1

Welcome to the introductory post of a new series on JSON indexes in the Oracle database. This post shows how JSON indexes are created and used in SQL queries. As always – lets get right to the code! SQL> CREATE TABLE json_data 2 ( json_col CLOB, 3 CONSTRAINT json_only CHECK ( json_col IS JSON ) … Read more

JSON Check Constraints…or not

I’ve been researching the Oracle SQL JSON functions and found some weird interesting behaviors. My New Year’s resolution is to look for the goodness in things so I’ll present them as features and tie them loosely (very, very loosely) to existing Oracle features. Here’s the first entry. The following code imitates a check constraint by requiring an … Read more

JSON Unique Keys

When it comes to JSON unique key values there are two schools of thought – those that want keys within objects (squiggly brackets) to be unique and those that don’t care. This can present some interesting outcomes when parsing the data as shown in this post using the SQL JSON_QUERY function. Note that array pointers … Read more

Transaction Backout Does Not Do JSON Part 2

I did some digging on this issue and found the problem was caused when Transaction Backout executed the following statement which failed: SQL> INSERT INTO json 2 VALUES(EMPTY_CLOB()); INSERT INTO json * ERROR at line 1: ORA-02290: check constraint (DRH.KEEP_IT_REAL) violated At this point I wondered where the EMPTY_CLOB insert was coming from so I … Read more

Transaction Backout Does Not Do JSON

I was testing transaction backout with non-scalar datatypes. And since I’m doing JSON research I decided to try JSON columns. I discovered that transaction backout does not work with JSON. Not sure just why yet. Here is the test code I used. SQL> CREATE TABLE JSON 2 ( always_json CLOB 3 CONSTRAINT keep_it_real 4* CHECK … Read more