Oracle JSON document select query performance tuning

Question

Table description COLUMN DATA_TYPE NULLABLE DEFAULT_VALUE ID VARCHAR2(16) No UPDATED_DATE TIMESTAMP(6) Yes DETAILS CLOB Yes TX_STATUS VARCHAR2(10) Yes TX_USER VARCHAR2(16) Yes PREMIUM NUMBER(10,2) Yes JSON_VALUE("DETAILS" FORMAT JSON , '$.policy.premium' RETURNING NUMBER(10,2) NULL ON ERROR)

Where,

1. DETAILS - JSON Document
2. PREMIUM - column is virtual column.

If i select virtual column with order by clause, query execution is taking too much time to run a select query.

The below query is taking 32.23secs. PREMIUM is the virtual column here

select id,tx_status,updated_date,tx_user, PREMIUM from J_MARINE_CERT j order by j.UPDATED_DATE desc

After removing PREMIUM, it is taking 0.009secs.

select id,tx_status,updated_date,tx_user from J_MARINE_CERT j order by j.UPDATED_DATE desc

Even after indexing PREMIUM, updated_date it is taking same amount of time(32.23) to execute.

Answer 1

I had the same issue and the only good solution was creating a Materialized View for values from json.

CREATE MATERIALIZED VIEW mv_for_query_rewrite
BUILD IMMEDIATE
REFRESH FAST ON STATEMENT WITH PRIMARY KEY
AS SELECT tbl.id, jt.*
   FROM jour_table tbl,
        json_table(tbl.json_document, '$' ERROR ON ERROR NULL ON EMPTY
          COLUMNS (
            some_number       NUMBER       PATH '$.PONumber',
            userid          VARCHAR2(10)   PATH '$.User'
)) jt;

Reason for performance drop is that Oracle takes whole json in memory to select one value from it.

From Oracle documentation