Solved

Oracle 10g and rowids

Posted on 2008-06-18
12
2,491 Views
Last Modified: 2013-12-18
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.

0
Comment
Question by:javrish
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
12 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 21812063
>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
 
LVL 16

Assisted Solution

by:Milleniumaire
Milleniumaire earned 125 total points
ID: 21812204
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
 
LVL 22

Assisted Solution

by:DrSQL
DrSQL earned 125 total points
ID: 21812478
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Assisted Solution

by:Dr_Billy
Dr_Billy earned 125 total points
ID: 21813812
I do agree with Milleniumaire , The rowids wouldn't change during the upgrade as long you are not importing/exporting your tables data. .
0
 

Author Comment

by:javrish
ID: 21817818
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
 
LVL 22

Expert Comment

by:DrSQL
ID: 21817899
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
 

Author Comment

by:javrish
ID: 21818420
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
 
LVL 22

Expert Comment

by:DrSQL
ID: 21818811
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
 
LVL 7

Expert Comment

by:Dauhee
ID: 21838303
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
 
LVL 22

Expert Comment

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

Good luck!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question