Materialize hint (again)

Recently a question on the OTN SQL and PL/SQL Forum comes up where someone has presented a test case in which he showed a specific issue when using a pipelined function together with the materialize hint. I have already written something about pipelined function, materialize hint and read consistency here. I thought it is worth investigating the OTN poster problem by mean of 10046 trace events in attempt to understand what makes Oracle working as such.

First, I will start by reproducing the test case given by the OTN Forum Original poster.

SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SQL> create table dummy (name varchar2(50 byte));

Table created.

SQL> create or replace package pkg_dummy
  2  as
  3  type dummy_record is record (name varchar2(50 byte));
  4  type dummy_records is table of dummy_record;
  5  
  6  function func_get_dummy_name
  7    return dummy_records pipelined;
  8  end pkg_dummy;
  9  /

Package created.

SQL> create or replace
  2  package body pkg_dummy
  3  as
  4  function func_get_dummy_name
  5    return dummy_records pipelined
  6  as
  7  begin
  8    for cur in( select * from dummy)
  9    loop
 10      pipe row(cur);
 11    end loop;
 12  end func_get_dummy_name;
 13  end pkg_dummy;
 14  /

Package body created.

Then I will insert a record in dummy table without committing

SQL> INSERT INTO DUMMY VALUES('Original name');

1 row created.

So far so good. Trouble will start soon.

SQL> with dummy_name as
  2    ( select "NAME" from table(pkg_dummy.func_get_dummy_name())
  3    )
  4  select "NAME" from dummy_name;

NAME
----------------
Original name

SQL> with dummy_name as
  2  (
  3  select /*+ materialize */ "NAME"
  4  from table(pkg_dummy.func_get_dummy_name())
  5  )
  6  select "NAME"
  7  from dummy_name;

no rows selected

Ooops!!! The materialize hint is giving a wrong result. When I traced the hinted query using 10046 events I observed the following information (restricted to the main one)

1) The part that corresponds to the temp table creation

SQL ID: 7p67a3p9mg7pd
Plan Hash: 0
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6603_12AFAA9" ("C0" 
  VARCHAR2(50) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 
  4254950915 ) NOPARALLEL


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.01          0          0          0           0
Execute      1      0.00       0.02          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.04          0          0          0           0

Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
********************************************************************************

2) The query part

WITH DUMMY_NAME AS
(
SELECT /*+ materialize */ "NAME"
FROM TABLE(PKG_DUMMY.FUNC_GET_DUMMY_NAME())
)
SELECT "NAME"
FROM DUMMY_NAME

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.18          0        192          0           0
Execute      1      0.00       0.05          0          0          2           0
Fetch        1      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.23          0        192          2           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84  

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  TEMP TABLE TRANSFORMATION  (cr=9 pr=0 pw=0 time=0 us)
      0   LOAD AS SELECT  (cr=9 pr=0 pw=0 time=0 us)
      0    COLLECTION ITERATOR PICKLER FETCH FUNC_GET_DUMMY_NAME (cr=9 pr=0 pw=0 time=0 us cost=29 size=16336 card=8168)
      0   VIEW  (cr=0 pr=0 pw=0 time=0 us cost=3 size=220536 card=8168)
      0    TABLE ACCESS FULL SYS_TEMP_0FD9D6603_12AFAA9 (cr=0 pr=0 pw=0 time=0 us cost=3 size=16336 card=8168)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1        5.55          5.55
********************************************************************************

SELECT * 
FROM
 DUMMY 

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.05          0          9          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.05          0          9          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84     (recursive depth: 1)
********************************************************************************

Why the following operation is generating 0 row instead of 1 row?

Rows     Row Source Operation
-------  ---------------------------------------------------
      0    TABLE ACCESS FULL SYS_TEMP_0FD9D6603_12AFAA9 (cr=0 pr=0 pw=0 time=0 us cost=3 size=16336 card=8168)

Is it because the temp table is created by the SYS user while the hinted query is executed by a different user (parsing user id: 84) ?

Thought that I tried the same operations (create of dummy table and select with and without materialize hint) under user SYS and have observed the same bug.

Any clue?

Virtual column as a foreign key

Recently a thread on the otn forum prompted me to review what I have already learned about the deadlock threat an unindexed foreign key can put in a mutli-user concurrent OLTP application. Particularly when the foreign key is represented by a virtual column.

In a comment of Charles ‘Hooper blog article I wrote (a) first that a function based index cannot cover the deadlock threat induced by a foreign key constraint (b) and then, a month or so after, I enriched my first comment saying that if the function based index starts by the foreign key column then it can cover that deadlock threat. How a about a foreign key represented by a virtual column? Will an index on that virtual column help?

The data model proposed by Dominic Brooks is re-used as follows:

create table t1
 (col1 number primary key);

create table t2
 (col1    number primary key
 ,status  varchar2(12) not null
 ,col2    number
 ,col2v   number generated always as (case when status = 'ACTIVE' then col2 end) VIRTUAL
  ,constraint t2_fk foreign key (col2v) references t1(col1)
  ,constraint t2_ck check (status in ('ACTIVE','INACTIVE') and (status = 'INACTIVE' or col2 is not null))
 );

insert into t1
select rownum
from dual 
connect by level <=100;

commit;

insert into t2 (col1, status, col2) values (1, 'ACTIVE',50);

declare
pragma autonomous_transaction;
begin
  delete from t1
  where col1 = 99;
  commit;
end;
/

Error report:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4
00060. 00000 -  "deadlock detected while waiting for resource";
*Cause:    Transactions deadlocked one another while waiting for resources.
*Action:   Look at the trace file to see the transactions and resources
           involved. Retry if necessary.

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-000137b4-00000000        19     138    SX             19     138           S
 
session 138: DID 0001-0013-00000018	session 138: DID 0001-0013-00000018 
 
Rows waited on:
  Session 138: obj - rowid = 000137B4 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 79796, file - 0, block - 0, slot - 0)
 
----- Information for the OTHER waiting sessions -----
----- End of information for the OTHER waiting sessions -----
 
Information for THIS session:
 
----- Current SQL Statement for this session (sql_id=0tqwpunag33fh) -----
DELETE FROM T1 WHERE COL1 = 99
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
28F75504         4  anonymous block

I didn’t created any index on the foreign key virtual column (col2v) and this is why when I tried to delete from the parent table having an unindexed foreign key, I have been deadlocked.

Let’s now create an index on col2v virtual column and repeat the same operations

create index t2_col2v_fk on t2(col2v);

index T2_COL2V_FK created.

select index_name, index_type
from user_indexes
where table_name = 'T2';

INDEX_NAME                     INDEX_TYPE
------------------------------ ----------------------
T2_COL2V_FK                    FUNCTION-BASED NORMAL
SYS_C0013917                   NORMAL

select index_name, column_expression
from user_ind_expressions
where index_name = 'T2_COL2V_FK';

INDEX_NAME    COLUMN_EXPRESSION
------------  ------------------ ----------------------
T2_COL2V_FK	CASE "STATUS" WHEN 'ACTIVE' THEN "COL2" END 

insert into t2 (col1, status, col2) values (1, 'ACTIVE',50);
1 rows inserted.

declare
pragma autonomous_transaction;
begin
  delete from t1
  where col1 = 99;
  commit;
end;
/
anonymous block completed

Thanks to this new function based index created on the virtual column I succeed to avoid the deadlock threat.

Bottom line: there are two conclusions I can made from this blog
 When a virtual column is declared as a foreign key, indexing it (function based index) will cover the corresponding deadlock threat.
 Whatever this function based index expression is if the corresponding index starts with the foreign virtual column then it will cover the deadlock threat