This directory contains examples of fine-grained cursor invalidation.

The test.sql is provided to give you ideas on how you can try this feature out for yourself.

They are in quite a raw state because I originally wrote the examples for my own benefit to explore the boundaries of this feature. In particular, there is a mixture of some DDL that *will* allow SQL statements to use rolling invalidation and some DDL will not. Some of the PROMPT comments may be incorrect now because the test was created before Oracle Database 12c R2 was released.

The example here was run on Oracle Database 12c Release 2.

The test case drops tables T1 and T2.

* These scripts are provided for educational purposes only.

* They are NOT supported by Oracle World Wide Technical Support.

* The scripts have been tested and they appear to work as intended.

* You should always run scripts on a test instance.

* These scripts drop and create tables. For use on test databases

set linesize 200

set tab off

set pagesize 1000

column plan_table_output format a180

SELECT *

FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'TYPICAL'));

alter system flush shared_pool;

select /* TESTFG */ count(*) from t1 where id = 1;

@plan

select /* TESTFG */ count(*) from t1 where val1 = 1;

@plan

select /* TESTFG */ count(*) from t2 where id = 1;

@plan

select /* TESTFG */ count(*) from t2 where id = 1+150000;

@plan

select /* TESTFG */ count(*) from t2 where val1 = 1;

@plan

select /* TESTFG */ /*+ INDEX_FFS(t new1) */ sum(val2) from t1 t;

@plan

select /* TESTFG */ count(*) from t2 where id < (select max(id) from t1);

@plan

select /* TESTFG */ /*+ USE_INVISIBLE_INDEXES */ count(*) from t1 where val2<50;

@plan

select /* TESTFG */ count(*) from t2 partition (p2);

@plan

select /* TESTFG */ count(*) from t2 partition (p1);

@plan

update /* TESTFG */ t1 set val1 = val1 + 1 where val1 < 50;

update /* TESTFG */ t2 set val1 = val1 + 1 where val1 < 50;

update /* TESTFG */ t1 set val1 = val1 + 1 where id < 50;

update /* TESTFG */ t2 set val1 = val1 + 1 where id < 50;

commit;

column EXACT_MATCHING_SIGNATURE format 99999999999999999999999999999999999

set linesize 300

set tab off

column sql_text format a85

column is_shareable format a20

column IS_ROLLING_INVALID format a20

column IS_ROLLING_REFRESH_INVALID format a20

column DDL_NO_INVALIDATE format a20

select sql_id,sql_text,child_number

, is_shareable, OBJECT_STATUS, INVALIDATIONS,IS_ROLLING_INVALID,IS_ROLLING_REFRESH_INVALID ,DDL_NO_INVALIDATE

from v$sql

where sql_text like '%TESTFG%' and sql_text not like '%v$sql%'

order by 2;

select sql_id,is_shareable, IS_ROLLING_INVALID,IS_ROLLING_REFRESH_INVALID

from v$sql

where sql_text like '%TESTVG%' and sql_text not like '%v$sql%'

order by 2;

drop table t1;

drop table t2;

create table t1 (id number(10) not null, val1 number(10) not null, val2 number(10) not null);

create table t2 (id number(10) not null, val1 number(10) not null, val2 number(10) not null)

partition by range (id) (

partition p1 values less than (100000)

, partition p2 values less than (200000)

)

/

insert into t1 select rownum,rownum,rownum from (

select 1

from dual connect by rownum < 10000);

insert into t2 select rownum,rownum,rownum from (

select 1

from dual connect by rownum < 10000);

insert into t2 select rownum+100000,rownum,rownum from (

select 1

from dual connect by rownum < 10000);

create index t1i on t1 (id);

create index t2i on t2 (id) local (

partition p1i,

partition p2i);

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t1');

exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'t2');