Understanding Oracle pseudo-columns

All the cool people I meet at Oracle events keep referring to "function based indexes" as pseudo-columns. I never really understood the whole thing about it, sure I figures one could consider it a pseudo-column as it was sort of a calculated value that was stored in the database. Never thought much about investigating it either.
However, when playing around with some composite indexes including functions today I finally got it. They are indeed pseudo-columns, or calculated columns as I think Oracle wants to call them in 11g.

As always,
an example:

SQL> create table d as select * from all_objects;

Table created.

SQL> insert into d select * from d;

66242 rows created.

SQL> update d set object_id=rownum;

132484 rows updated.

-- Ok, got a test table. Let's index it with some pseudo stuff.SQL> create index d_1 on d(upper(status));

Index created.

SQL> select index_name,column_name from user_ind_columns where table_name='D';

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------------------------
D_1                            SYS_NC00014$

SQL> select index_name,column_expression from user_ind_expressions
  2  where table_name='D';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------
D_1                            UPPER("STATUS")

SQL> create index d_2 on d(object_id,upper(status),timestamp);

Index created.

SQL> select index_name,column_name from user_ind_columns where table_name='D';

INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------
D_1                            SYS_NC00014$
D_2                            OBJECT_ID
D_2                            SYS_NC00014$
D_2                            TIMESTAMP

SQL> select index_name,column_expression from user_ind_expressions
  2  where table_name='D';

INDEX_NAME                     COLUMN_EXPRESSION
------------------------------ ------------------------------
D_1                            UPPER("STATUS")
D_2                            UPPER("STATUS")

-- Thats the index bit, we got two indexes with the same "function",
-- so Oracle will index the same pseudo column! Eureka!
-- Some tests then...SQL> set autotrace traceonly exp
SQL> select object_name from d where upper(status)='T';

Execution Plan
----------------------------------------------------------
Plan hash value: 2452139598

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    21 |   462 |   165   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| D    |    21 |   462 |   165   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | D_1  |   418 |       |   157   (0)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("STATUS")='T')

Note
-----
   - dynamic sampling used for this statement

-- A column can go in the where clause, even pseudo-columns.SQL> select object_name from d where sys_nc00014$='T';

Execution Plan
----------------------------------------------------------
Plan hash value: 2452139598

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    21 |   462 |   165   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| D    |    21 |   462 |   165   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | D_1  |   418 |       |   157   (0)| 00:00:02 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(UPPER("STATUS")='T')

Note
-----
   - dynamic sampling used for this statement


-- Since it is a column we can obviously select it as well.SQL> select SYS_NC00014$ from d where object_id=500;

SYS_NC0
-------
VALID

Execution Plan
----------------------------------------------------------
Plan hash value: 2591304836

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |    18 |     5   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| D_2  |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_ID"=500)

Note
-----
   - dynamic sampling used for this statement

-- You can even rename the pseudo-column.
-- Oracle did actually dump core after I did that so better not.SQL> alter table d rename column SYS_NC00014$ to dog;

Table altered.

SQL> alter table d rename column dog to SYS_NC00014$;

Table altered.