SQL JSON query operators – Part II

Next in our introduction of the new SQL JSON query operator is JSON_QUERY, this is complementary to JSON_VALUE.

JSON_VALUE takes JSON as input and return the scalar SQL value as output. JSON_QUERY on the other hand returns a JSON (i.e, an Array or an Object), this implies that JSON_QUERY could be chained (JSON in – JSON out) versus the output of the JSON_VALUE can never be used as the input for another operator that expects a JSON input.

create table t(cust_details varchar2(4000)
constraint is_valid_json check(
cust_details IS JSON STRICT) );

insert into t(cust_details)  
values( '{
 "custNo":2,
 "name" : "Jane",
 "status":"Gold",
 "address": {
 "Street": "Main Rd 1",
 "City": "San Jose",
 "zip": 95002
}}');

insert into t(cust_details)
values ('{
 "custNo":3,
 "name" : "Jill",
 "status":["Important","Gold"],
 "address": {
 "Street": "Broadway 2",
  "City": "Belmont",
 "zip": 94065
}}');
commit;



We can see each customer has an address which is a JSON object; we can return this embedded object using JSON_QUERY.

demo@ORA12C> select json_query( cust_details, '$.address') from t;

JSON_QUERY(CUST_DETAILS,'$.ADDRESS')
----------------------------------------------------------------------
{"Street":"Main Rd 1","City":"San Jose","zip":95002}
{"Street":"Broadway 2","City":"Belmont","zip":94065}

2 rows selected.

demo@ORA12C>

Similarly we can select an inner array, say the “status” of each customer.

demo@ORA12C> select json_query( cust_details, '$.status') from t;

JSON_QUERY(CUST_DETAILS,'$.STATUS')
-------------------------------------------------------------------

["Important","Gold"]

2 rows selected.

demo@ORA12C>


So what happened? We have a field called “status” but only one value shows up, the other value is NULL, may be this NULL comes from a masked error ( the default error handler is to return NULL ), so let’s make sure that errors get reported. 

demo@ORA12C> select json_query( cust_details, '$.status' ERROR ON ERROR) from t;
select json_query( cust_details, '$.status' ERROR ON ERROR) from t
                                                                 *
ERROR at line 1:
ORA-40480: result cannot be returned without array wrapper


demo@ORA12C>


So what does this error mean? Basically it is the path expression ($.status) has selected something, but it cannot be returned because it is not a JSON value (not an object or array), but this can be resolved with Array wrappers.


demo@ORA12C> select json_query( cust_details, '$.status' WITH ARRAY WRAPPER) from t;

JSON_QUERY(CUST_DETAILS,'$.STATUS'WITHARRAYWRAPPER)
--------------------------------------------------------------------------------------
["Gold"]
[["Important","Gold"]]

2 rows selected.

demo@ORA12C>


Now every selected value gets wrapped inside an array and the values are returned. But sometimes we may want to only wrap a value if needed and not wrap a value which is already a JSON; this can be done by adding CONDITIONAL keyword to it.


demo@ORA12C> select json_query( cust_details, '$.status' WITH CONDITIONAL ARRAY WRAPPER) from t;

JSON_QUERY(CUST_DETAILS,'$.STATUS'WITHCONDITIONALARRAYWRAPPER)
-----------------------------------------------------------------
["Gold"]
["Important","Gold"]

2 rows selected.

Given this JSON_QUERY can do even more.


insert into t(cust_details)
values ('{
 "custNo":3,
 "name" : "Jim",
 "status": "Silver",
 "address":[
 {
 "Street": "Fastlane 4",
 "City": "Atherton",
 "zip": 94027
 },
 {
 "Street": "Slowlane 5",
 "City": "San Francisco",
 "zip": 94105
 } ]
}');
commit;


Obviously, since both addresses inside one array, we can select the array as a whole.

demo@ORA12C> select json_query( cust_details, '$.address' ) from t;

JSON_QUERY(CUST_DETAILS,'$.ADDRESS')
--------------------------------------------------------------------------------------------------------------------
{"Street":"Main Rd 1","City":"San Jose","zip":95002}
{"Street":"Broadway 2","City":"Belmont","zip":94065}
[{"Street":"Fastlane 4","City":"Atherton","zip":94027},{"Street":"Slowlane 5","City":"San Francisco","zip":94105}]

3 rows selected.

demo@ORA12C>


We can also select one address out of each array.


demo@ORA12C> select json_query( cust_details, '$.address[0]' ) from t;

JSON_QUERY(CUST_DETAILS,'$.ADDRESS[0]')
-------------------------------------------------------------------------
{"Street":"Main Rd 1","City":"San Jose","zip":95002}
{"Street":"Broadway 2","City":"Belmont","zip":94065}
{"Street":"Fastlane 4","City":"Atherton","zip":94027}

3 rows selected.


So, how can we select the City names? The attribute “City” is inside “address” object in the first two JSON document, for the third JSON document this “City” is inside the “address” array – so how to retrieve this City names? 


Using JSON_VALUE, we couldn’t get the full set of values, across all the three JSON documents.


demo@ORA12C> select json_value( cust_details ,'$.address.City') from t;

JSON_VALUE(CUST_DETAILS,'$.ADDRESS.CITY')
---------------------------------------------------------------------------
San Jose
Belmont


3 rows selected.

demo@ORA12C>


But, with JSON_QUERY, we could get a full set of values like this.


demo@ORA12C> select json_query( cust_details, '$.address[*].City' with conditional array wrapper ) from t;

JSON_QUERY(CUST_DETAILS,'$.ADDRESS[*].CITY'WITHCONDITIONALARRAYWRAPPER)
------------------------------------------------------------------------------------------------------------
["San Jose"]
["Belmont"]
["Atherton","San Francisco"]

3 rows selected.

demo@ORA12C>

SQL JSON query operators – Part I

The simplified syntax shown in the last blog allows easy but limited functionality with JSON data, Hence Oracle has provided more powerful SQL operators to work with JSON data starting 12c(12.1.0.2)

• JSON_VALUE: to select one scalar value in the JSON data and return it to the SQL
• JSON_EXISTS: a Boolean operator typically used in the WHERE clause to filter rows based on the property in the JSON data.
• JSON_QUERY: an operator to select value in the JSON data, in contrast to JSON_VALUE which always return one scalar value, JSON_QUERY returns JSON value (object or array), with JSON_QUERY user can select multiple values and have them wrapped inside a JSON array.
• JSON_TABLE: the most powerful operator that exposes JSON data as a relational view. With JSON_TABLE you could turn a JSON data into a relational representation.

These operators have and ‘error handler’ which specified what to do in case of an error is encountered, and options how the selected values are being returned.

create table t(

doc_id int,

doc_name clob,

constraint is_valid_json check

(doc_name is json) );

insert into t(doc_id,doc_name)

 values(1, '{

 "ColorName": "black",

 "rgb": [0,0,0],

 "hex": "#000000"

 }');

insert into t(doc_id,doc_name)

 values(2, '{

 "ColorName": "light blue",

 "rgb": [255,69,78],

 "hex": "#FFFFFF"

 }'); 

insert into t(doc_id,doc_name)

 values(3, '{

 "ColorName": "dark yellow",

 "rgb": [72,53,68],

 "hex": "#AB02CD"

 }');  

commit;

Using simple dotted notation, the query to access the “colorname” attribute would be like this.

demo@ORA12C> select t1.doc_name.ColorName from t t1;

COLORNAME

--------------------

black

light blue

dark yellow

3 rows selected.

demo@ORA12C> 

If you drop the check constraint, then the simple dotted notation will not even work and result in errors.

demo@ORA12C> alter table t drop constraint is_valid_json;

Table altered.

demo@ORA12C> select t1.doc_name.ColorName from t t1;

select t1.doc_name.ColorName from t t1

       *

ERROR at line 1:

ORA-00904: "T1"."DOC_NAME"."COLORNAME": invalid identifier

But when using SQL JSON query operator to access the JSON document, it don’t even require the check constraint, even without the check constraint in place, it could execute successfully.

demo@ORA12C> select json_value(doc_name,'$.ColorName') as colorname

  2  from t;

COLORNAME

--------------------

black

light blue

dark yellow

3 rows selected.

demo@ORA12C>

This above query uses the simplest version of JSON_VALUE expression. The first argument to the JSON_VALUE operator is the input column_name that provides JSON data. The second argument is the path expression. A path expression starts with $ after which you can use either dot or square brackets (similar to Java script) to navigate inside to the desired key/value pairs.

Similar to the other SQL operators JSON_VALUE can be used anywhere in the SQL statements.

demo@ORA12C> select doc_id

  2  from t

  3  where json_value( doc_name ,'$.ColorName') like '%b%'

  4  order by json_value( doc_name ,'$.ColorName') desc;

    DOC_ID

----------

         2

         1

2 rows selected.

demo@ORA12C>

The default output of JSON_VALUE is varchar2(4000), since the name of the color is less than 4000 characters, we can request the JSON_VALUE to return something less than 4000 characters. 

demo@ORA12C> select json_value(doc_name,'$.ColorName' RETURNING VARCHAR2(10)) as colorname

  2  from t;

COLORNAME

--------------------

black

light blue

3 rows selected.

demo@ORA12C>

What happens now? Instead of three values we see only two, what happens to “dark yellow” we see NULL instead.

The color name “dark yellow” don’t fit with in Varchar2(10), so we encountered an error, but the default error handler (error on null) for SQL JSON function suppressed it and gave us a NULL instead.

At this point we have three options

Increase the output value a larger value

demo@ORA12C> select json_value(doc_name,'$.ColorName' RETURNING VARCHAR2(20)) as colorname

  2  from t;

COLORNAME

--------------------

black

light blue

dark yellow

3 rows selected.

demo@ORA12C>

Tell the error handler to return a default value instead.

demo@ORA12C> select json_value(doc_name,'$.ColorName' RETURNING VARCHAR2(10)

  2              DEFAULT '#BAD_VALUE' on error) as colorname

  3  from t;

COLORNAME

--------------------

black

light blue

#BAD_VALUE

3 rows selected.

Tell the error handler to not suppress the error and raise it instead.

demo@ORA12C> select json_value(doc_name,'$.ColorName' returning varchar2(10)

  2              ERROR ON ERROR ) as colorname

  3  from t;

ERROR:

ORA-40478: output value too large (maximum: )

no rows selected

The returning clause also allows us to return non-textual values.

demo@ORA12C> select json_value(doc_name,'$.rgb[0]' returning number) red,

  2         json_value(doc_name,'$.rgb[1]' returning number) blue,

  3         json_value(doc_name,'$.rgb[2]' returning number) green

  4  from t        ;

       RED       BLUE      GREEN

---------- ---------- ----------

         0          0          0

       255         69         78

        72         53         68

3 rows selected.

demo@ORA12C>

The other possible return data types are Boolean values, JSON support those but Oracle (at least PL/SQL does, but not SQL) not, but we have two choices to return either as string (true|false) or number (1|0).

demo@ORA12C> select json_value( '{a:true}','$.a' ) from dual;

JSON_VALUE('{A:TRUE}','$.A')

------------------------------------------------------------------------------

true

1 row selected.

demo@ORA12C> select json_value( '{a:true}','$.a' returning number) from dual;

JSON_VALUE('{A:TRUE}','$.A'RETURNINGNUMBER)

-------------------------------------------

                                          1

1 row selected.

demo@ORA12C>

Loading JSON from external file system

Very recently got a request from an internal team to load a JSON document (from external file system) into Oracle database.

Thought of using DBMS_LOB API to read the file contents and load them into application tables, but it got failed miserably.

demo@ORA12C> create table t(doc clob
  2     constraint t_valid_json
  3     check(doc IS JSON) );

Table created.

demo@ORA12C> declare
  2     l_bfile bfile;
  3     l_temp clob;
  4  begin
  5     insert into t(doc)
  6             values( empty_clob() )
  7             returning doc into l_temp;
  8
  9     l_bfile := bfilename('ETL_DATA_DIR','sample_json.txt');
 10
 11     dbms_lob.fileopen(l_bfile);
 12     dbms_lob.loadfromfile(
 13             dest_lob=>l_temp,
 14             src_lob=>l_bfile,
 15             amount=>dbms_lob.getlength(l_bfile)) ;
 16     dbms_lob.fileclose(l_bfile);
 17     commit;
 18
 19     exception
 20             when others then
 21             if dbms_lob.isopen(l_bfile) = 1 then
 22                     dbms_lob.fileclose(l_bfile);
 23             end if;
 24             raise;
 25  end;
 26  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 897
ORA-06512: at line 21
ORA-02290: check constraint (DEMO.T_VALID_JSON) violated

This error message is not due to invalid JSON document (infact the contents inside the file "sample_json.txt" is a valid JSON document, validated it through http://jsonlint.com/ portal.)

This error message is due to inserting empty_clob() into the table (in the above block).

demo@ORA12C> insert into t(doc) values( empty_clob() );
insert into t(doc) values( empty_clob() )
*
ERROR at line 1:
ORA-02290: check constraint (DEMO.T_VALID_JSON) violated

So, how to over come this, we could see if external table could help us. so created a sql loader control file and asked sql-loader to generate external table for us.

demo@ORA12C> host
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\179818>type d:\ctl.txt
load data
infile *
truncate into table t
(
lob_filenm filler char(100),
doc lobfile(lob_filenm) TERMINATED BY EOF)
begindata
d:\sample_json.txt

C:\Users\179818>sqlldr demo/demo@ora12c control=d:\ctl.txt log=d:\log.txt EXTERNAL_TABLE = GENERATE_ONLY

SQL*Loader: Release 12.1.0.2.0 - Production on Wed Jan 11 12:42:51 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      External Table
SQL*Loader-803: Field type LOBFILE not supported by External Table

C:\Users\179818>exit

demo@ORA12C>

But then it doesn't help us, so created an external table manually and loaded those JSON document like this.

demo@ORA12C> CREATE TABLE t_load_json (
  2    doc CLOB
  3  )
  4  ORGANIZATION EXTERNAL
  5  (
  6    TYPE ORACLE_LOADER
  7    DEFAULT DIRECTORY ETL_DATA_DIR
  8    ACCESS PARAMETERS
  9    (
 10      RECORDS DELIMITED BY NEWLINE
 11     FIELDS TERMINATED BY ','
 12      MISSING FIELD VALUES ARE NULL
 13      (
 14        clob_filename     CHAR(100)
 15      )
 16      COLUMN TRANSFORMS (doc FROM LOBFILE (clob_filename) FROM (ETL_DATA_DIR) CLOB)
 17    )
 18    LOCATION ('sample_data.txt')
 19  )
 20  REJECT LIMIT UNLIMITED ;

Table created.

demo@ORA12C> select dbms_lob.getlength(doc) from t_load_json;

DBMS_LOB.GETLENGTH(DOC)
-----------------------
                  11877

1 row selected.

demo@ORA12C> insert into t(doc)
  2  select doc
  3  from t_load_json;

1 row created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> select dbms_lob.getlength(doc) from t where doc IS JSON;

DBMS_LOB.GETLENGTH(DOC)
-----------------------
                  11877

1 row selected.

demo@ORA12C>

You might still wonder, why don't we load this JSON from sql-loader itself, sql-loader has a couple of bugs in 12c (12.1.0.2) for loading JSON, here is a quick test case for that.

A simple JSON document having one key value pair got loaded successfully, with an insert statement

demo@ORA12C> insert into t(doc) values(' {"department":"Sales"} ');

1 row created.

demo@ORA12C> rollback;

Rollback complete.

But when loading the same JSON document from sql-loader it goes like this, Here is my control file, for this load process.

C:\Users\179818>type d:\ctl.txt
load data
infile *
truncate into table t
( doc )
begindata
{"department":"Sales"}

When initiated the load process, it completed with zero rows processed.

C:\Users\179818>sqlldr demo/demo@ora12c control=d:\ctl.txt log=d:\log.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Wed Jan 11 14:15:35 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Path used:      Conventional
Commit point reached - logical record count 1

Table T:
  0 Rows successfully loaded.

Check the log file:
  d:\log.txt
for more information about the load.

Peeking into the log file , it shows this.

C:\Users\179818>type d:\log.txt

SQL*Loader: Release 12.1.0.2.0 - Production on Wed Jan 11 14:15:35 2017

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Control File:   d:\ctl.txt
Data File:      d:\ctl.txt
  Bad File:     d:\ctl.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table T, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DOC                                 FIRST     1           CHARACTER

Record 1: Rejected - Error on table T.
ORA-02290: check constraint (DEMO.T_VALID_JSON) violated


Table T:
  0 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                    256 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             1
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Wed Jan 11 14:15:35 2017
Run ended on Wed Jan 11 14:15:39 2017

Elapsed time was:     00:00:03.76
CPU time was:         00:00:00.31

So the JSON document that loaded via INSERT statement but not through sql-loader, is due to the presence of the bug in 12c (12.1.0.2) database ( i hope patches will be available at Oracle support for this. - right now don't have access to Oralce 12c Release 2 database to verify this)

Simple queries over JSON documents

In the previous blog we saw about how to store JSON data in oracle database, let’s see how to query them now.

demo@ORA12C> create table t(doc_id number,
  2     doc_name varchar2(1000) ,
  3     constraint is_valid_json check(doc_name IS JSON) );

Table created.

demo@ORA12C> insert into t(doc_id,doc_name)
  2     values(1, '{
  3     "firstName" : "Tom",
  4     "ColorName": "black",
  5     "rgb": [0,0,0],
  6     "hex": "#000000"
  7     }');

1 row created.

demo@ORA12C> insert into t(doc_id,doc_name)
  2     values(2, '{
  3     "firstName" : "Kyte",
  4     "ColorName": "White",
  5     "rgb": [255,255,255],
  6     "hex": "#FFFFFF"
  7     }');

1 row created.

demo@ORA12C> commit;

Commit complete.


Now that I have some JSON in database, let’s go ahead and retrieve some of the data via SQL. With the added JSON support also comes an enhanced SQL engine that allows you to use a simple-dot-notation (SDN) syntax on your JSON data (i.e. you can write SQL queries that contain something like <table_alias>.<json_column_name>.<json_key>.<json_key>), but be careful, the SDN syntax is case sensitive! 

The query to retrieve the “firstname” from JSON document using SDN syntax will be like this.

demo@ORA12C> select t1.doc_name.firstName from t t1;

FIRSTNAME
------------------------------------------------------
Tom
Kyte

2 rows selected.


If the JSON key from SDN syntax doesn’t matches with JSON keys in the document stored in the column, then NULL values would be returned. (Say instead of “firstName” if we use “firstname” in the SDN query, then NULL values will be returned)

demo@ORA12C> select t1.doc_name.firstname from t t1;

FIRSTNAME
------------------------------------------------------



2 rows selected.



So what happens if we use a key name that doesn’t in the data, (let’s say ‘description’) then we get NULL values back rather than an error message. This is because in many JSON use cases there are optional fields which do not exist in every instance.


demo@ORA12C> select t1.doc_name.description from t t1;

DESCRIPTION
----------------------------------------------------------



2 rows selected.


This SDN allows us to select a complex value (i.e. array or objects)


demo@ORA12C> select t1.doc_name.rgb from t t1;

RGB
-------------------------------------------------
[0,0,0]
[255,255,255]

2 rows selected.


This SDN syntax works well for simple queries like the one illustrated above, but it comes with some limitation. That is it requires the presence of check constraint (either in enabled/disabled status) that marks the columns as a JSON column, in case of missing constraint over JSON column would return error message.


demo@ORA12C> select t1.doc_name.firstName from t t1;

FIRSTNAME
------------------------------------------------------
Tom
Kyte

2 rows selected.

demo@ORA12C> alter table t disable constraint is_valid_json;

Table altered.

demo@ORA12C> select t1.doc_name.firstName from t t1;

FIRSTNAME
-------------------------------------------------------------
Tom
Kyte

2 rows selected.

demo@ORA12C> alter table t drop constraint is_valid_json;

Table altered.

demo@ORA12C> select t1.doc_name.firstName from t t1;
select t1.doc_name.firstName from t t1
       *
ERROR at line 1:
ORA-00904: "T1"."DOC_NAME"."FIRSTNAME": invalid identifier


demo@ORA12C>

Enhanced incremental statistics staleness Tolerance

Have discussed about incremental partition enough in the past, this post is for incremental statistics enhancements in Oracle 12c database.

By default increment maintenance does not use the staleness status to decide when to update statistics, if a partition or sub-partition is subjected to even a single DML operation, statistics will be re-gathered, the appropriate synopsis will be updated and the global-level statistics will be re-calculated from the synopsis.  This behavior can be changed in Oracle database 12c, allowing us to use staleness threshold to define when incremental statistics will be re-calculated. 

Prior to 12c database, when we update one row in a partition containing one million rows, the partition won’t be marked STALE (if we assume a 10% staleness threshold) but fresh statistics will be re-gathered. Oracle 12c exhibits the same behavior by default, but gives us an option to allow multiple dml changes to occur against (sub) partition before it is a target for incremental refresh. You can enable this behavior by changing the DBMS_STATS preference INCREMENTAL_STALENESS from its default value (NULL) to USE_STALE_PERCENT

Once this preference is set at the table, statistics will not be re-gathered for a (sub) partition if the number of DML changes is below the STALE_PERCENT threshold.

Here is the test case from Oracle 11g (11.2.0.4) database.

demo@ORA11G> create table t
  2  partition by range(created_dt)
  3   interval( numtoyminterval(1,'year') )
  4  ( partition p2010 values less than
  5     (to_date('01-jan-2010','dd-mon-yyyy')) )
  6  as
  7  select owner,object_id,object_name,object_type,
  8     to_date('01-Jan-2012','dd-mon-yyyy')+rownum created_dt
  9  from all_objects
 10  where rownum <= (365*3);

Table created.

demo@ORA11G> begin
  2     dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');
  3     dbms_stats.set_table_prefs(user,'T','GRANULARITY','AUTO');
  4  end;
  5  /

PL/SQL procedure successfully completed.

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA11G> select t1.table_name,t1.partition_name,t1.object_type,
  2             t1.last_analyzed,t2.high_value
  3  from user_tab_statistics t1 ,
  4       user_tab_partitions t2
  5  where t1.table_name ='T'
  6  and t1.table_name = t2.table_name (+)
  7  and t1.partition_name = t2.partition_name (+)
  8  order by 1,2 nulls first;

TABLE_NAME PARTITION_ OBJECT_TYPE  LAST_ANALYZED        HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T                     TABLE        02-JAN-2017 04:45:00
T          P2010      PARTITION    02-JAN-2017 04:44:57 TO_DATE(' 2010-01-01
T          SYS_P62406 PARTITION    02-JAN-2017 04:44:58 TO_DATE(' 2013-01-01
T          SYS_P62407 PARTITION    02-JAN-2017 04:44:58 TO_DATE(' 2014-01-01
T          SYS_P62408 PARTITION    02-JAN-2017 04:44:58 TO_DATE(' 2015-01-01

5 rows selected.


Incremental statistics been enabled on table ‘T’, synopsis got created at each partition and global statistics generated by aggregating partition level statistics and synopsis.

Let’s now insert a single row for partition that belong to 2013 and a whole bunch of records in 2017 and re-gather the stats to see what partition level statistics got refreshed.


 demo@ORA11G> insert into t(owner,object_id,object_name,object_type,created_dt)
  2  select owner,object_id,object_name,object_type,
  3                     to_date('13-feb-2013','dd-mon-yyyy') created_dt
  4  from all_objects
  5  where rownum = 1;

1 row created.

demo@ORA11G> insert into t(owner,object_id,object_name,object_type,created_dt)
  2  select owner,object_id,object_name,object_type,
  3       to_date('01-Jan-2017','dd-mon-yyyy')+rownum created_dt
  4  from all_objects
  5  where rownum < 365;

364 rows created.

demo@ORA11G> commit;

Commit complete.

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA11G> select t1.table_name,t1.partition_name,t1.object_type,
  2             t1.last_analyzed,t2.high_value
  3  from user_tab_statistics t1 ,
  4       user_tab_partitions t2
  5  where t1.table_name ='T'
  6  and t1.table_name = t2.table_name (+)
  7  and t1.partition_name = t2.partition_name (+)
  8  order by 1,2 nulls first;

TABLE_NAME PARTITION_ OBJECT_TYPE  LAST_ANALYZED        HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T                     TABLE        02-JAN-2017 04:46:55
T          P2010      PARTITION    02-JAN-2017 04:44:57 TO_DATE(' 2010-01-01
T          SYS_P62406 PARTITION    02-JAN-2017 04:44:58 TO_DATE(' 2013-01-01
T          SYS_P62407 PARTITION    02-JAN-2017 04:46:53 TO_DATE(' 2014-01-01
T          SYS_P62408 PARTITION    02-JAN-2017 04:44:58 TO_DATE(' 2015-01-01
T          SYS_P62442 PARTITION    02-JAN-2017 04:46:50 TO_DATE(' 2018-01-01

6 rows selected.



So inserting a single row into this partition SYS_P62407 has made the incremental stats gathering process to refresh the statistics on that partition.

In 11g any DML on older partitions triggered partition statistics to be re-gathered, this is the default in 12c, however you could change this behavior by changing the DBMS_STATS preference INCREMENTAL_STALENESS from its default value (NULL) to USE_STALE_PERCENT (that allows partition level statistics to be re-gathered once it exceeds the threshold value)


demo@ORA12C> begin
  2     dbms_stats.set_table_prefs(user,'T','INCREMENTAL','TRUE');
  3     dbms_stats.set_table_prefs(user,'T','GRANULARITY','AUTO');
  4  end;
  5  /

PL/SQL procedure successfully completed.

demo@ORA12C> select dbms_stats.get_prefs('INCREMENTAL_STALENESS',user,'T')
  2  from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS',USER,'T')
------------------------------------------------------


1 row selected.

demo@ORA12C> begin
  2     dbms_stats.set_table_prefs(
  3             ownname=>user,
  4             tabname=>'T',
  5             pname=>'INCREMENTAL_STALENESS',
  6             pvalue=>'USE_STALE_PERCENT' );
  7  end;
  8  /

PL/SQL procedure successfully completed.

demo@ORA12C> select dbms_stats.get_prefs('INCREMENTAL_STALENESS',user,'T')
  2  from dual;

DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS',USER,'T')
-------------------------------------------------------
USE_STALE_PERCENT

1 row selected.

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA12C> select t1.table_name,t1.partition_name,t1.object_type,
  2             t1.last_analyzed,t2.high_value
  3  from user_tab_statistics t1 ,
  4       user_tab_partitions t2
  5  where t1.table_name ='T'
  6  and t1.table_name = t2.table_name (+)
  7  and t1.partition_name = t2.partition_name (+)
  8  order by 1,2 nulls first;

TABLE_NAME PARTITION_ OBJECT_TYPE  LAST_ANALYZED        HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T                     TABLE        02-JAN-2017 16:31:18
T          P2010      PARTITION    02-JAN-2017 16:31:18 TO_DATE(' 2010-01-01
T          SYS_P22309 PARTITION    02-JAN-2017 16:31:17 TO_DATE(' 2013-01-01
T          SYS_P22310 PARTITION    02-JAN-2017 16:31:17 TO_DATE(' 2014-01-01
T          SYS_P22311 PARTITION    02-JAN-2017 16:31:17 TO_DATE(' 2015-01-01

5 rows selected.


Let’s now insert a single row for partition that belong to 2013 and a whole bunch of records in 2017 and re-gather the stats to see what partition level statistics got refreshed in 12c database.


demo@ORA12C> insert into t(owner,object_id,object_name,object_type,created_dt)
  2  select owner,object_id,object_name,object_type,
  3                     to_date('13-feb-2013','dd-mon-yyyy') created_dt
  4  from all_objects
  5  where rownum = 1;

1 row created.

demo@ORA12C> insert into t(owner,object_id,object_name,object_type,created_dt)
  2  select owner,object_id,object_name,object_type,
  3       to_date('01-Jan-2017','dd-mon-yyyy')+rownum created_dt
  4  from all_objects
  5  where rownum < 365;

364 rows created.

demo@ORA12C> commit;

Commit complete.

demo@ORA12C> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

demo@ORA12C> select t1.table_name,t1.partition_name,t1.object_type,
  2             t1.last_analyzed,t2.high_value
  3  from user_tab_statistics t1 ,
  4       user_tab_partitions t2
  5  where t1.table_name ='T'
  6  and t1.table_name = t2.table_name (+)
  7  and t1.partition_name = t2.partition_name (+)
  8  order by 1,2 nulls first;

TABLE_NAME PARTITION_ OBJECT_TYPE  LAST_ANALYZED        HIGH_VALUE
---------- ---------- ------------ -------------------- --------------------
T                     TABLE        02-JAN-2017 16:31:39
T          P2010      PARTITION    02-JAN-2017 16:31:18 TO_DATE(' 2010-01-01
T          SYS_P22309 PARTITION    02-JAN-2017 16:31:17 TO_DATE(' 2013-01-01
T          SYS_P22310 PARTITION    02-JAN-2017 16:31:17 TO_DATE(' 2014-01-01
T          SYS_P22311 PARTITION    02-JAN-2017 16:31:17 TO_DATE(' 2015-01-01
T          SYS_P22361 PARTITION    02-JAN-2017 16:31:39 TO_DATE(' 2018-01-01

6 rows selected.

demo@ORA12C> select dbms_stats.get_prefs('STALE_PERCENT',user,'T')
  2  from dual;

DBMS_STATS.GET_PREFS('STALE_PERCENT',USER,'T')
--------------------------------------------------------------------
10

1 row selected.

demo@ORA12C>


In Oracle 12c even after a single row change in this partition SYS_P22310 which is still below the threshold, so no stats refresh to that partition, wherever the changes is above the threshold limit, only those (sub) partitions will be considered for stats refresh process.