Friday, March 23

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.

2 comments:

Anonymous said...

It is the holic gold which makes me very happy these days, my brother says holic money is his favorite games gold he likes, he usually holic online gold to start his game and most of the time he will win the cheap holic gold back and give me some holic online money to play the game.
I am so happy to get some kal geons and the kal gold is given by my close friend who tells me that the kal online geons is the basis to enter into the game. Therefore, I should kal online gold with the spare money and I gain some kalonline Geons from other players.

Unknown said...

thank admin post hoooo
Yasemin Ünlü yerli turk yıldızın sahnelerinden seyretem