Oracle 10g and rowids

Hi All,

I have to upgrade a oracle 9i to 10g database.

I also have tables that have rowids and also a few tables that have stored rowids of other tables.

What is the impact to these tables due to the upgrade?

Thanks in advance.

javrishAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>and also a few tables that have stored rowids of other tables.
that is a big problem, because the ROWID of a row can change, over time...
you should NEVER store the ROWID values, this will require either a application change, unless you have some other field using which you can relate the rows.
0
 
MilleniumaireConnect With a Mentor Commented:
I have worked on datawarehousing applications that stored rowid's for performance reasons.  Providing the data is static it shouldn't cause a problem, but I wouldn't recommend it.

As part of your upgrade to 10g providing you aren't exporting and importing the data (or otherwise moving it) the rowid's shouldn't change for any tables.
0
 
DrSQLConnect With a Mentor Commented:
javrish,
    I agree with Milleniumaire, the upgrade shouldn't change the rowids.  However, 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.  So, if you've done any of this you might want to reconsider and make changes to your applicaiton code.

     I would recommend that you create yourself some cross-reference tables to stitch things back together if need be.

Using "reverse" lookup based on a stored rowid (don't do this in 10g):

select * from mytableofinterest
where stored_rowid = (select rowid from atableofinterestingrows where the_sequence=1)

instead, you should always assume that the rowid is a pointer, not a value.  The stored rowids should have been in the other table in this case.

Good luck!
DrSQL
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Dr_BillyConnect With a Mentor Commented:
I do agree with Milleniumaire , The rowids wouldn't change during the upgrade as long you are not importing/exporting your tables data. .
0
 
javrishAuthor Commented:
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.
0
 
DrSQLCommented:
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!

0
 
javrishAuthor Commented:
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.

0
 
DrSQLCommented:
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!

0
 
DauheeCommented:
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
0
 
DrSQLCommented:
javrish,
    It's been over a month.  Could you please update/close this question?  Thank you for using Experts Exchange.

Good luck!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.