As it happens, the "data_object_id" and "object_id" jumped in the way as I was trying to use the package DBMS_ASSERT. Things went like this --
I was wondering whther taking advantage of DBMS_ASSERT.sql_object_name can bring in some benifit from the performance perspective, so just did this in sql*plus --
SQL> select object_name from user_objects where object_name='ENTITY'; OBJECT_NAME ---------------------------------------------------------------------------- ENTITY ENTITY ENTITY Elapsed: 00:00:00.01
I don't knwo your response now. But I was "shocked" at that momement, WTH? Why there are 3 "duplicated" records coming out? Try select more columns? why not--
SQL> select object_name, subobject_name, data_object_id, object_id from user_objects where object_name='ENTITY'; OBJECT_NAME SUBOBJECT_NAME DATA_OBJECT_ID OBJECT_ID ------------------------------ ------------------------------ -------------- ---------- ENTITY 203043 ENTITY P199001010000 203044 203044 ENTITY P199101010000 228096 228096
OK, things get clear now. The "duplicated" records results from there are two partitions in the table "ENTITY" which can be told from the column "SUBOBJECT_NAME". Done? wait a minute, what about the column "DATA_OBJECT_ID" and "OBJECT_ID"? Most of the time, I noticed these two columns are of the same value, but this time we can see "DATA_OBJECT_ID" is NULL in the first row!
So, obvioulsy, those two columns are not that close as they appear. :) What's the difference? Initial feeling is that, "OBJECT_ID" is the identifier for the databse object (no matter whethere there is a physical segment or not) while "DATA_OBJECT_ID" is associated with the physical data segment. A simple test shows that stored code (pacakge, function, procedure, etc) doesn't have data_object_id at all --
SQL> select object_name, subobject_name, data_object_id, object_id from user_objects where object_type='FUNCTION' and rownum <=5; OBJECT_NAME SUBOBJECT_NAME DATA_OBJECT_ID OBJECT_ID ------------------------------ ------------------------------ -------------- ---------- ADD_WORKING_DAYS 205166 ANALYTIC_PRODUCT 205174 APPLY_OPERATOR 205175 APPLY_OPERATOR_C 205176 CROSS_CCY 205154 Elapsed: 00:00:00.04 SQL>
A simple google search, I got the following page which gave a detailed explanation about the difference between "DATA_OBJECT_ID" and "OBJECT_ID", why not have a look? (Strongly recommended!)
