函数索引 + HIDDEN_COLUMN

rollingpig

可以再trace 一下 create function index的过程。

完了，给你加精华吧……

Yong Huang

最初由 dba_mx 发布
[B]于是，随之而来的问题就是Oracle是如何维护这个隐藏字段的呢？

我希望通过trace来查看：

[php]
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
...
郁闷的是，在trace文件中根本就没有维护那个隐藏字段的操作，或者说此操作被Oracle特意过滤了？ [/B]

I don't think there's any way to see any recursive SQL Oracle runs to maintain the hidden columns during a DML. In fact, Oracle probably doesn't even consider that as a separate recursive SQL.

I thought there was some event we can set to trace FBI's operation. But I didn't find any.

Yong Huang

Yong Huang

最初由 Yong Huang 发布
[B]

I don't think there's any way to see any recursive SQL Oracle runs to maintain the hidden columns during a DML. In fact, Oracle probably doesn't even consider that as a separate recursive SQL.

I thought there was some event we can set to trace FBI's operation. But I didn't find any.

Yong Huang [/B]

On second thought, I don't think Oracle should have any separate, additional, recursive SQL to handle FBI. The reason is simple. Once you have a FBI, a hidden column is added to the table. It's no different than you explicitly add a column with a weird name like SYS_NC00002$ and set the default value to yourfunction(originalcolumn), except desc won't show it. In fact, you can't run this any more

alter table test.tt add (SYS_NC00002$ varchar2(10));

You would get an error saying the column already exists.

Anyway, suppose everything is like you explicitly add a new column (so desc mytable shows it), setting default to function(original_column). When you update mytable set original='xxx', the new column will be automatically updated. There's no other hidden SQLs you would see maintaining the new column.

Yong Huang

rollingpig

But how this happend:
When you update mytable set original='xxx', the new column will be automatically updated ?

dba_mx

[php]

CREATE TABLE TEST.TT (STR VARCHAR2(5));
INSERT INTO TEST.TT SELECT 'TTT' || LPAD(ROWNUM,2,'0') FROM DUAL CONNECT BY ROWNUM<=5;
COMMIT;
ALTER SESSION SET SQL_TRACE=TRUE;
CREATE INDEX TEST.FIDX_TT_STR ON TEST.TT(SUBSTR(STR,4));
ALTER SESSION SET SQL_TRACE=FALSE;
..
[/php]

从trace文件中根本看不到向表中添加隐藏字段的步骤，或者说我没有看出来。

dba_mx

我觉得，Oracle对于函数索引所引发的 隐藏字段及其后续的维护操作都做了过滤，
就如同 DESC 里面看不到那个字段一样，其添加、更新的过程也看不到

d.c.b.a

we should check the segcol#, if it's 0 then oracle will not store the computed value.

Yong Huang

最初由 dba_mx 发布
[B][php]

CREATE TABLE TEST.TT (STR VARCHAR2(5));
INSERT INTO TEST.TT SELECT 'TTT' || LPAD(ROWNUM,2,'0') FROM DUAL CONNECT BY ROWNUM<=5;
COMMIT;
ALTER SESSION SET SQL_TRACE=TRUE;
CREATE INDEX TEST.FIDX_TT_STR ON TEST.TT(SUBSTR(STR,4));
ALTER SESSION SET SQL_TRACE=FALSE;
..
[/php]

从trace文件中根本看不到向表中添加隐藏字段的步骤，或者说我没有看出来。 [/B]

Earlier you said the hidden *update* on the hidden column is not shown. I said there *isn't* any need to have a separate SQL to update that. Now you say the hidden column *creation* is not shown.

I didn't read your trace file since I don't have WinRar installed on this computer. But you can clearly see the 向表中添加隐藏字段的步骤. Here's my test, done on 10.1.0.3.1 (but likely to be reproducible on 9i).

alter system flush shared_pool;
alter session set events = '10046 trace name context forever, level 4';
create index fbi_testfbi on testfbi (upper(name));

I use 10046 event level 4 just to see the bind value. Here's the trace file:

insert into col$(obj#,name,intcol#,segcol#,type#,length,precision#,scale,null$,offset,fixedstorage,segcollength,deflength,default$,col#,property,charsetid,charsetform,spare1,spare2,spare3)values(:1,:2,:3,:4,:5,:6,decode(:7,0,null,:7),decode(:5,2,decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,183,:8,231,:8,null),:9,0,:10,:11,decode(:12,0,null,:12),:13,:14,:15,:16,:17,:18,:19,:20)
END OF STMT
PARSE #10:c=15625,e=5765,p=0,cr=84,cu=0,mis=1,r=0,dep=1,og=4,tim=1227079990
BINDS #10:
bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=08 oacfl2=0001 size=24 offset=0
   bfp=05c206f4 bln=22 avl=04 flg=05
   value=68541
bind 1: dty=1 mxl=32(12) mal=00 scl=00 pre=00 oacflg=18 oacfl2=0001 size=32 offset=0
   bfp=28d47a1e bln=32 avl=12 flg=09
   value="SYS_NC00002$"
...

It's about adding a column, with obj# bound at runtime to 68541 and name to "SYS_NC00002$"... Isn't that clear?

I flush shared pool just to show recursive SQLs. Otherwise since dictionary cache already has some data dictionary info, some recursive SQLs wouldn't be run. But in this case, it's a DML against a data dictionary table. This flush may not be necessary.

Yong Huang

dba_mx

alter session set events = '10046 trace name context forever, level 4';

关键在这一句，我只是普通的一个trace而已，没有设置10046事件，所以没有看到。。。。 呵呵，才疏学浅啊

biti_rainy

首先我并不认为oracle会在  表的block中去真实地存储 函数计算出来的 值，这么一个 列 应该仅仅是一个虚拟的，作为某种用途的列。

真实的数据应该是在函数索引的segment中。 所以不存在DML 时候递归  DML 维护表数据的问题。