Tuning on PostgreSQL 

This section describes how to tune the PostgreSQL database for use with Data Veracity.

...

CREATE OR REPLACE FUNCTION dvcasttime(text)
RETURNS timestamp AS
$$SELECT to_timestamp($1, 'YYYY-MM-DD HH24:MI:SS')$$ -- change the timestamp format to the format being used in the field
LANGUAGE sql IMMUTABLE;

CREATE INDEX dvdDateField1 ON dvtestudr (dvcasttime(udr_json ->> 'dateField1'));

Tuning on Oracle

This section describes how to tune the Oracle Database for use with Data Veracity.

Data Veracity uses Oracle 19c's JSON capabilities for storing data. This provides the capability to improve the performance of the SQL queries from Data Veracity, by applying the Oracle's database features for JSON fields indexing. The following are suggestions to how you may improve performance for Data Veracity, it is highly recommended that you consult your DBA before proceeding with any of these suggestions or if they are necessary in your solution.

Image Added

Example of a table created in Data Veracity, viewed using sqlplus

The Table above shows a table, created using the SQL generation in the Data Veracity profile. The UDR_JSON field is where the record for every UDR is kept. The UDR is kept in a JSON format and Oracle 19c has a way to allow users to create an index of a field located in the JSON format itself.

Image Added

Example UDR with 3 sub UDR levels

For example, when the user frequently searches for the UDR field intField1, the search performance could be improved by creating an index in the Oracle database for this field. The following will be an SQL command to create a unique index meant for a field in the JSON record.

CREATE UNIQUE INDEX dvIntField1_idx
ON dvtestudr (json_value(udr_json, 
'$.intField1' 
RETURNING NUMBER ERROR ON ERROR));

If fields located within multiple sublevels of UDR are frequently queried by the user, an index could be created to improve the performance for searching this field. The following SQL command will create a unique index for a field that is located in within the subUDRs. 

CREATE UNIQUE INDEX dvIntSubField2_idx
ON dvtestudr (json_value(udr_json, 
'$.sub1Field.sub2Field.intField' 
RETURNING NUMBER ERROR ON ERROR));