Oracle 10g and rowids

Hi DrSQL,

You have mentioned "10g introduces UROWIDs as the native form for rowids.  This changes the behavior of the rowids pseudocolumn in the where clause.  While you should never try to find a row based on a stored rowid (see below for what I mean), if you do you might find that there will be a new "literal" for the rowid.  They still reference the same row, but the character form is slightly different."

I have not very clearly understood what is the meaning of a new literal for the rowid.

I do have code that have got updates.

When an emp table is being updated, update emp_image eg:

update emp_image
set emp_xx = 123
     emp_val = 'job'
 where emp_rowid = :old.rowid;

Will this update code syntax cause an issue to an upgrade from 9i to 10g.

Thanks in advance.

javrish,
   When you look at the character equivalent of a rowid (rowidtochar function) you see that the rowid has a particular format and can be broken up into component parts.  When you get to 10g there are situation where the value that gets stored in a rowid type column will not be the same as what was in the rowid pseudcolumn.  Use of the stored rowid column will still find the same record, but you shouldn't assume that looking for a particular value in the stored rowids will work.  Since that is what your trigger is trying to do, I would expect there could be issues.  I would suggest you use the rowid to fetch the primary key of the other record and compare those rather than stop with a comparison of rowids:

update emp_image
set emp_xx = 123
     emp_val = 'job'
 where emp_id = (select emp_id from emp_image where rowid = :old.rowid);

Hope that helps

Good luck!

Hi DrSQL,

Thanks for all your replies.

How about the merge that uses rowid like this? Is this likely to cause issues as well?

merge into emp_image e
using
(
select a.empno, a.empvalue,
a.empdate, a.empsal, b.deptno,
a.emp_rowid,
b.dept_rowid
from
emp a,
dept b
where a.empid = b.empid) data_val
ON
(a.emp_rowid = data_val.emp_rowid
 and b.dept_rowid = data_val.dept_rowid)
when matched then
update set
   e.empsal = data_val.empsal
when not matched then
insert
(
 e.xx,
 e.xx,
 e.xx,
 e.emp_rowid,
 e.dept_rowid
)
values
(
 xxx,
 xxx,
 xxx,
 data_val.emp_rowid,
 data_val.dept_rowid
)

Also, as you have discussed above, there are no primary keys. The only way a row can be identified correctly is using rowid concept for the update trigger.

I still could not understand your statement:
"Use of the stored rowid column will still find the same record, but you shouldn't assume that looking for a particular value in the stored rowids will work"
Can you explain with an example please?

Thanks in advance.

Since your example is just using stored rowids, it SHOULD be ok.  But this is still a very fragile solution.  If you ever lost a tablespace or had some other issue that required rebuilding your database, there's no way the rows are all going to go back to their original locations.  I would strongly encourage you to add something that is a unique key value to the records just in case something goes wrong.

What I was referring to in the quote you are asking about was when you examine the value of a stored rowid and compare it to a rowid pseudocolumn:

select * from employees
where employee_salary_rowid = (select rowid from salaries
                                                       where salary = (select max(salary) from salaries))

This ISN'T guaranteed to work.  It usually will, but the rowid value returned from the subquery may not be exactly the same as the one that was stored in the employees table.  But even if it isn't, the record being pointed to is the same, so you can use it to find the row, you just have to use it as a pointer:

select * from employees, salaries
where salaries.rowid = employee_salary_rowid and
           salary = (select max(salary) from salaries)

This may seem like a subtle difference (and seem like it won't peform as well), but the join allows Oracle to resolve the query so that the salaries table will do a lookup by rowid - using the stored rowid as a pointer.  The other syntax has to treat the rowid as a value (like a number or a string).  If there's a difference in how the rowid pseudocolumn generates the value as opposed to how it was stored in the employee_salary_rowid, then the comparison fails (you are just telling oracle to see if the values match, not whether they refer to the same record).

Good luck!

also beware of performing segment shrinks in 10g (might sound logical after upgrade) as rowids will change

http://www.oracle.com/technology/oramag/oracle/05-may/o35tuning.html

javrish,
    It's been over a month.  Could you please update/close this question?  Thank you for using Experts Exchange.

Good luck!