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.

Wednesday, March 21

Solaris 10 NFS version when using the automounter

Classic problem, new stuff is cool and all. But sometimes people (like myself) can't be bothered with configuring everything to the extreme so all new features are working and are compatible. For basic things, the basics is enough.
Take NFSv4, a default Linux box these days do support NFSv4 but I'm usually to lazy to configure it and just slap in an old NFSv3 line in my exports file. Fine with me, fine with most things, except one thing.
The Solaris 10 automounter, it detects the NFSv4 support and automatically tries to mount the NFS share in v4 mode when browsing /net.
Sidenote, if you are not familiar with the /net feature in Linux and Solaris it's pretty neat. See it as a cli version of "My network neighbourhood" in Windows. You can simply cd to /net/<machine name>/<share path> and the automounter will mount that share on that machine. Easy peasy.
Back to my problem, what do I want? I want the default NFS mount level to be vers=3.
Easy change, just uncomment and set NFS_CLIENT_MAXVERS to 3 in /etc/default/nfs.

Here's an example, the machine dolphin is my old trustworthy Linux workstation (yes, I like examples):
[root@marble /]$ cd /net/dolphin/
[root@marble dolphin]$ ls
shared
[root@marble dolphin]$ cd shared
bash: cd: shared: Permission denied
[root@marble dolphin]$ grep NFS_CLIENT_VERSMAX /etc/default/nfs
#NFS_CLIENT_VERSMAX=4
[root@marble dolphin]$ vim /etc/default/nfs
.. edit ...
[root@marble dolphin]$ grep NFS_CLIENT_VERSMAX /etc/default/nfs
NFS_CLIENT_VERSMAX=3
[root@marble dolphin]$ cd shared
[root@marble shared]$ ls -l
total 84
drwxrwxr-x 6 hlinden hlinden 4096 Mar 8 15:29 apps
drwxrwxr-x 6 hlinden hlinden 4096 Sep 7 2006 archive
drwxrwxr-x 11 hlinden hlinden 12288 Mar 21 16:32 download
drwxrwxr-x 15 hlinden hlinden 4096 Mar 1 11:05 tmp
[root@marble shared]$