I have a Application which will update any of the field in a table(table with four fields with null as default and have a Unique key which includes all the fields of the table and hence no primary key associated with the table).Application using ROWID to update the record in the table(because table does not have any key field) .Application selects ROWID of the record when it selecting record for the update and uses the same while it updating back to the table. Application is able to update the record in the table as intended and my dought is is there any risk involed in using the ROWID of the record to update.
Quick suggestions will be appriciated.....
in a normal table with his correspondong indexes the data and indexes are seperated. with index organized table the data is actually stored *inside* index, in a normal b tree index what it contains it's the index and the rowid which points the location of data, so basically oracle does more I/O with normal b tree indexes. With indexed organized table instead of storing index and the rowid the btree index actually stores the index AND the data so in one I/O Oracle can retrieve both index and data therefore reducing I/O, in some way the way this work is similar to a cluster
MS SQL Server 2000 has a similar (or same) feature called clustered indexes which microsoft claims with this feature they beat TPC benchmark, if you are familiar with SQL Server 2000 may be this might help you a bit to understand indexed organized table, also the main page of dbasupport there is an article about indexed organized tables
you wrote that "From 8 onwards Rowid in a table may not be unique because of different format of rowid.". can you please explan more, in what circumstances? as far as i know if i use a cursor, where i get the rowid, and i us the rowid to update te row from the cursor, like this:
set ___ = ____
where rowid = rec.row_id
In general, we should not use ROWID for update/delete, we should use PK for those 2 DML statements. The reason is Oracle changes the format of ROWID in each major release. We are not sure that ROWID of 8i will be the same in 9i also. Avoid using ROWID in the application. It is not portable across all RDBMSs.
Ahh, but that brings up a point of discussion. Judging by how Mallikds describes his application, it appears that it's possible that he can wind up with rows that are not unique, and using the ROWID is the best way for him to pinpoint a single row.
Obviously, creating a sequenced column is the most obvious way that he could solve his problem, but is there any other way that one could identify a single row of data in the absence a unique primary key?
Basically, as tamilselvan said, this is a very bad idea. This means that your table is not even in First Normal Form. Not having a PK on a table is a wholly unacceptable design.
Having said that, the problem that I would see with your routine would be that the ROWID could have changed. If you did not lock the row when you SELECTed it, then other users were free to UPDATE or DELETE said row. Therefore, you must be able to handle the fact that the row may no longer exist, or may actually exist, but with a different row id.
However, you did say that it had a "Unique key which includes all the fields of the table ". Then why not use all the fields in the WHERE clause? This has the same issues with records moving or deleting, but allows you to bypass use of the row id. Using the ROWID, IMHO, is a very bad idea. I personally don't think Oracle should ever have exposed that column. Creating a sequenced PK on this table would really be your best move.
[QUOTE][i]Originally posted by chrisrlong [/i]
[B].... If you did not lock the row when you SELECTed it, then other users were free to UPDATE or DELETE said row. Therefore, you must be able to handle the fact that the row may no longer exist, or may actually exist, but with a different row id. ....
- Chris [/B][/QUOTE]
There is even another posibility (more or less theoretical, but it is possible): a totaly new row might have been *inserted* in the meantime, with the *same rowid* you have previously selected from a different row! If you do not lock the row before a SELECT, this same row might be deleted and a totaly new row inserted that might get the same rowid that was used by deleted row!
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?