Solved

Oracle 10g and rowids

Posted on 2008-06-18
12
2,470 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
12 Comments
 
LVL 142

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

743 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now