Hi Tom,

thanks for this explanation. But how does Oracle use this fake column? Is it because you can not create two Indexes on the same column and this is a way to cheat a litte? You can add as much function based indexes as you wish on the same column as far as you use a different function.
So this fake column would work like a Filesystem link - or at least similar to that.

Regards
Wolfgang

Ok this virtual column is not a link, but it's still a little mystic - and of course very interesting.
I tried that:

CREATE TABLE t AS
SELECT OBJECT_NAME FROM ALL_OBJECTS;

CREATE INDEX T_IDX ON T (LOWER(OBJECT_NAME));

SELECT COLUMN_NAME FROM USER_TAB_COLS WHERE TABLE_NAME='T';

COLUMN_NAME
------------------------------
OBJECT_NAME
SYS_NC00002$

SET AUTOTRACE ON

SELECT OBJECT_NAME ,SYS_NC00002$ FROM T WHERE ROWNUM <10;

OBJECT_NAME SYS_NC00002$
------------------------------ ----------------------------
DUAL dual
DUAL dual
SYSTEM_PRIVILEGE_MAP system_privilege_map
SYSTEM_PRIVILEGE_MAP system_privilege_map
TABLE_PRIVILEGE_MAP table_privilege_map
TABLE_PRIVILEGE_MAP table_privilege_map
STMT_AUDIT_OPTION_MAP stmt_audit_option_map
STMT_AUDIT_OPTION_MAP stmt_audit_option_map
MAP_OBJECT map_object

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 153 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 33134 | 550K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

So it seems as if the virtual column is a calculated column. Would be very interesting - just for knowing it -why they need a calculated column to implement function based indexes. Perhaps its just needed to build the index and craete the needed values.

Regards
Wolfgang

PS: I like the new autotrace style in R2. :-) Much more usefull information without the need to query the plan table itself.

I think it's worth adding what Jonathan Lewis writes in his book (page 133), that is, that these "virtual columns" (as they seem to be named technically) [can] have statistics attached to them, and this helps the CBO to compute eg cardinality/selectivity even if the FBI is not used.

Simple example (10.2.0.1) :

dellera@ORACLE10> create table t as select rownum x from dual connect by level <= 1000;

Table created.

dellera@ORACLE10> create index t_idx on t( mod(x,2) );

Index created.

dellera@ORACLE10> exec dbms_stats.gather_table_stats (user, 't', cascade=>true);

PL/SQL procedure successfully completed.

dellera@ORACLE10> select column_name, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, VIRTUAL_COLUMN from user_tab_cols where table_name = 'T';

COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE VIRTUAL_C
------------------------------ ------------ ---------- ---------- ---------
X 1000 C102 C20B NO
SYS_NC00002$ 2 80 C102 YES

select /*+ full(t) */ x from t where mod(x,2) = 0

---------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)|
|* 1 | TABLE ACCESS FULL| T | 500 | 2000 | 3 (0)|
---------------------------------------------------------------

You mentioned this somewhere too, but I thought it would be relevant for the discussion ...

Thanks. I'm at page 95 but perhaps I should overjump a view pages for the moment ;-)

Regards
Wolfgang

Hi,

Function [ which has been used for index ] has been calling
for each row. It is not taking space at database level.

I just put this line in function [ which has been used in index ]

execute immediate ' alter system switch log file ' ;

and I was not able to select a single row while
using

select hidden_column from table;

Thanks,
Js