Differences between DATA_OBJECT_ID and OBJECT_ID

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!)

http://tonyhasler.wordpress.com/2010/02/21/what-is-the-difference-between-object_id-and-data_object_id/

 

 

 

 

 

转载于:https://www.cnblogs.com/fangwenyu/archive/2012/06/25/2561309.html