2021 – JSON Code Changes With Database Unavailable

The last modification for the JSON structure change is the PL/SQL API. First up lets make sure the query API works. BEGIN DBMS_OUTPUT.PUT_LINE(jpatient_pkg.get_pname( p_os => 1, p_mrn => ‘1’)); END; Patient 1 That is the correct result. Now for the add_patient procedure. BEGIN jpatient_pkg.add_patient( p_name => ‘Bob’, p_os => 1, p_mrn => 11 ); END; … Read more

2021 – What About Indexes

This post compares index creation and usage for RDBMS data and JSON data. I’ll focus on three specific aspects. Creating Indexes Including JSON Search Indexes Verifying Index Usage Oracle’s EXPLAIN PLAN utility shows if an index was used or not in a query Resolving Index Usage Oracle’s EXPLAIN PLAN utility shows the actual actual criteria … Read more

2021 – Virtual Column Invisibility, Errors and Indexes

This post covers continues with the Virtual Column discussion including making them invisible, handling errors in the underlying function and creating indexes on them. Invisibility First up is invisibility. Virtual columns are treated as just another column in Data Manipulation Language (DML) statements. For example and INSERT statement like the one below assumes that all … Read more

2021 – Performance Comparison Part 1

Oracle offers like JSON_TABLE and JSON_VALUE to translate JSON keys and values into RDBMS rows and columns. But there is no fair way to compare the performance of those functions with home grown code because its not possible to see the inner workings and influences of the built in functions. Thus an apples-to-apples comparison is … Read more