1

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.

Yugamani
  • 33
  • 1
  • 8
  • 1
    I think you need to explain some more details about that column. – Tomalak Sep 15 '17 at 09:11
  • 1
    I have updated the details about table and columns – Yugamani Sep 15 '17 at 12:09
  • The [documentation](https://docs.oracle.com/database/122/ADJSN/json-in-oracle-database.htm#GUID-D7BCE045-EF6D-47E9-9BB2-30C01933248E__USESQLWITHJSONDATA-144609D2) suggests that you could use dot notation to directly access values inside JSON, something like this `SELECT j.DETAILS.policy.premium FROM J_MARINE_CERT j;`, but I'm not sure if that applies to you or if that is any faster than what you do now. JSON parsing is a complex operation, doing it for every row quickly adds up. – Tomalak Sep 15 '17 at 15:14
  • The fastest solution for any such case is to persist the calculated value into a table column and update it (e.g. through a trigger) whenever the JSON changes. If the JSON never changes, extract the value on INSERT. – Tomalak Sep 15 '17 at 15:18
  • Sorry for the delayed response. – Yugamani Sep 18 '17 at 09:07
  • Accessing json value using dot notation like j.DETAILS.policy.premium is also very slow even for 15000records. So tried creating virtual column and indexing as well. But no improvement. – Yugamani Sep 18 '17 at 09:12
  • Virtual column is still a virtual column and it just parses/tranforms the data from the original column. If you need search by that field I would suggest extracting it to a separate column. – RokX Apr 16 '19 at 15:10

1 Answers1

0

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

wast
  • 878
  • 9
  • 27