February 3, 2011 at 9:24 am
Tom.Thomson (2/3/2011)
Hugo Kornelis (2/3/2011)
Great question!I'm surprised (and, frankly, disappointed) about the large percentage of incorrect answers, though.
yes, it's a good questiopn. And yes, that half of the answers are incorrect is both surprising and disappointing - if fact I think it's appalling.
Seeing this made me wonder if SQL 2000 and earlier did this differantly.
Does anyone know if Oracle uses the same Uniqueifiers to point directly to data in it's non-clustered indexes?
February 3, 2011 at 9:37 am
SanDroid (2/3/2011)
Seeing this made me wonder if SQL 2000 and earlier did this differantly.
6.5 may have. 7 and 2000 were the same as 2005+
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 3, 2011 at 10:20 am
SanDroid (2/3/2011)
Does anyone know if Oracle uses the same Uniqueifiers to point directly to data in it's non-clustered indexes?
Indexes in Oracle don't quite work the same as they do in SQL Server. There really isn't the concept of a clustered index that determines the physical order of records. Internally, Oracle uses a multi-part identifier called a ROWID to identify the location of a table record on disk. The indexes use this ROWID to link the index entry to the rest of the record. Since ROWIDs are a reference to where on disk the record is located, they could potentially change over time.
Some ROWID info: http://www.orafaq.com/wiki/ROWID
February 3, 2011 at 10:39 am
Thanks for the question!
February 3, 2011 at 10:51 am
Chris Harshman (2/3/2011)
Indexes in Oracle don't quite work the same as they do in SQL Server.
I can imagine that they don't... 😛
There really isn't the concept of a clustered index that determines the physical order of records. Internally, Oracle uses a multi-part identifier called a ROWID to identify... ROWID... Since ROWIDs are a reference to where on disk the record is located
Some ROWID info: http://www.orafaq.com/wiki/ROWID
Sounds to me like ROWID = Uniqueufiers = Index Column Key
Everyone likes thier special words for things.
😎
February 3, 2011 at 11:07 am
paul.knibbs (2/3/2011)
Hugo Kornelis (2/3/2011)
Great question!I'm surprised (and, frankly, disappointed) about the large percentage of incorrect answers, though.
Especially considering the "reprint" of Gail's excellent articles on the subject recently...
This is why we reprint things. No one sees everything and they miss good ones.
February 3, 2011 at 2:20 pm
SanDroid (2/3/2011)
There really isn't the concept of a clustered index that determines the physical order of records. Internally, Oracle uses a multi-part identifier called a ROWID to identify... ROWID... Since ROWIDs are a reference to where on disk the record is located
Some ROWID info: http://www.orafaq.com/wiki/ROWID
Sounds to me like ROWID = Uniqueufiers = Index Column Key
No, it's not the same at all.
An Oracle ROWID (according to Chris and the webpage he referenced) represents the physical location on disk where a row resides. There is nothing in SQL Server that is linked to the physical location, all references to a location on disk use the page number, which is basically a counter that starts at zero for the first page of the file. That's why you can detach a database, move a file, then attach again and it still works. And that's why SQL Server won't break if you defragment a drive while it's offline - fragments of the file will be moved, but their physical location on disk is not relevant; the OS handles that.
The closest thing to the Orcale ROWID that SQL Server has, is the RID (Row ID - the combination of file#, page#, and slot#). But since this refers to a logical page number in the file, it is not a physical location on disk. Its usage is somewhat comparable, though - SQL Server uses the RID in the leaf layer of a nonclustered index to point back to the actual data - but only for tables that do not have a clustered index defined. If a clustered index exists, the nonclustered indexes will use the clustered index key instead of the RID.
Finally, the uniquifier you mention is yet seomething different. This is a hidden extra column (taking 4 bytes) that SQL Server will add to a nonunique clustered index. Because the nonclustered indexes use the clustered index key as a pointer to the actual data, clustered index key values have to be unique - so if you declare a nonunique index to be the clustered index, SQL Server makes it unique by adding this 4-byte uniquifier. There is no way (except for a few DBCC commands) to actually see the data SQL Server stores in this uniquifier column.
February 3, 2011 at 2:44 pm
Hugo Kornelis (2/3/2011)
SanDroid (2/3/2011)
There really isn't the concept of a clustered index that determines the physical order of records. Internally, Oracle uses a multi-part identifier called a ROWID to identify... ROWID... Since ROWIDs are a reference to where on disk the record is located
Some ROWID info: http://www.orafaq.com/wiki/ROWID
Sounds to me like ROWID = Uniqueufiers = Index Column Key
No, it's not the same at all.
An Oracle ROWID (according to Chris and the webpage he referenced) represents the physical location on disk where a row resides. There is nothing in SQL Server that is linked to the physical location, all references to a location on disk use the page number, which is basically a counter that starts at zero for the first page of the file.
Very Odd... :w00t:
I was reading someplace that in MS SQL, the leaf level of a nonclustered index contains the clustered key columns that point back to the clustered index and allow the row to be looked up. If there are duplicate clustered keys for a non-unique clustered index, there may also be a uniquifer.
IMHO: This sounds a lot like the ROWID created in ORACLE for non-unique rows in a table. :w00t:
February 3, 2011 at 2:57 pm
SanDroid (2/3/2011)
Very Odd... :w00t:I was reading someplace that in MS SQL, the leaf level of a nonclustered index contains the clustered key columns that point back to the clustered index and allow the row to be looked up. If there are duplicate clustered keys for a non-unique clustered index, there may also be a uniquifer.
IMHO: This sounds a lot like the ROWID created in ORACLE for non-unique rows in a table. :w00t:
You're correct. What Hugo is trying to point out is the difference between Oracle and SQL mechanics here. Oracle uses physical addressing, MS SQL uses logical internal addressing.
Defrag an oracle database while it's offline and everything goes to heck in a handbasket.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 3, 2011 at 3:13 pm
Craig Farrell (2/3/2011)
SanDroid (2/3/2011)
Very Odd... :w00t:I was reading someplace that in MS SQL, the leaf level of a nonclustered index contains the clustered key columns that point back to the clustered index and allow the row to be looked up. If there are duplicate clustered keys for a non-unique clustered index, there may also be a uniquifer.
IMHO: This sounds a lot like the ROWID created in ORACLE for non-unique rows in a table. :w00t:
You're correct. What Hugo is trying to point out is the difference between Oracle and SQL mechanics here. Oracle uses physical addressing, MS SQL uses logical internal addressing.
Defrag an oracle database while it's offline and everything goes to heck in a handbasket.
That is exactly what I thought I was not understanding. Thank you so much for pointing it out so clearly.
That Oracle is using a similar virtual ID concept to track row data back to a physical disk location instead of just the "address" of the data in the file.
Certainly helps me understand why Oracle performance is affected by the file systems.
February 4, 2011 at 12:48 am
gary.mazzone (2/3/2011)
Good question... Proves you need to read the question completely before you answer. I didn't read the part that says in a table with a clustered index
For me also i did not read correctly so i put pointer to page.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
February 8, 2011 at 12:08 pm
I would like to see more questions like this.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply