DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: rowid

  1. #1
    Join Date
    Apr 2004
    Posts
    20

    rowid

    Hi,

    does the oracle views has psudeo column like rowid, i need a unique identifier for each row like the rowid

    so when ever select values from that view i get the same ID

    Thanks

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    No.

    May be try with rownum.

    Tamil

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by tamilselvan
    May be try with rownum.
    Disaster Alert!

    The rownum changes depending on your ORDER BY clause. Do not use it as a unique identifier, ever.
    Jeff Hunter

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by Shadis
    Hi,

    does the oracle views has psudeo column like rowid, i need a unique identifier for each row like the rowid

    so when ever select values from that view i get the same ID

    Thanks
    Is there a column or set of columns that uniquely identify every row in the tables in question?
    If not, why not?

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by marist89
    Disaster Alert!

    The rownum changes depending on your ORDER BY clause. Do not use it as a unique identifier, ever.
    The original poster did not say that the view contains order by clause.

    Even if the view uses order by clause, I can get the same answer.
    Example:
    select rownum rn, a.*
    from ( select * from my_view order by 1,2) a;

    Tamil

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    What about this?

    Code:
      1  SELECT *
      2    FROM ( SELECT ROWNUM rnum, table_name
      3             FROM user_tables ) ut
      4   INNER JOIN
      5         ( SELECT ROWNUM rnum, table_name
      6             FROM ( SELECT DISTINCT table_name
      7                      FROM user_tab_columns )) utc
      8*     ON ut.rnum = utc.rnum
    SYSTEM@vizdev01> /
    
    RNUM TABLE_NAME                     RNUM TABLE_NAME
    ---- ------------------------------ ---- ------------------------------
     001 AQ$_INTERNET_AGENTS             001 AQ$DEF$_AQCALL
     002 AQ$_INTERNET_AGENT_PRIVS        002 AQ$DEF$_AQERROR
     003 AQ$_QUEUES                      003 AQ$_INTERNET_AGENTS
     004 AQ$_QUEUE_TABLES                004 AQ$_INTERNET_AGENT_PRIVS
     005 AQ$_SCHEDULES                   005 AQ$_QUEUES
     006 DEF$_AQCALL                     006 AQ$_QUEUE_TABLES
     007 DEF$_AQERROR                    007 AQ$_SCHEDULES
     008 DEF$_CALLDEST                   008 DEF$_AQCALL
     009 DEF$_DEFAULTDEST                009 DEF$_AQERROR
     010 DEF$_DESTINATION                010 DEF$_CALLDEST
     011 DEF$_ERROR                      011 DEF$_DEFAULTDEST
     012 DEF$_LOB                        012 DEF$_DESTINATION
     013 DEF$_ORIGIN                     013 DEF$_ERROR
     014 DEF$_PROPAGATOR                 014 DEF$_LOB
     015 DEF$_PUSHED_TRANSACTIONS        015 DEF$_ORIGIN
     016 DEF$_TEMP$LOB                   016 DEF$_PROPAGATOR
     017 HELP                            017 DEF$_PUSHED_TRANSACTIONS
     018 LOGMNRC_DBNAME_UID_MAP          018 DEF$_TEMP$LOB
     019 LOGMNRC_GSII                    019 HELP
     020 LOGMNRC_GTCS                    020 LOGMNRC_DBNAME_UID_MAP
     021 LOGMNRC_GTLO                    021 LOGMNRC_GSII
     022 LOGMNRT_MDDL$                   022 LOGMNRC_GTCS
     023 LOGMNR_AGE_SPILL$               023 LOGMNRC_GTLO
     024 LOGMNR_ATTRCOL$                 024 LOGMNRT_MDDL$
     025 LOGMNR_ATTRIBUTE$               025 LOGMNR_AGE_SPILL$
     026 LOGMNR_CCOL$                    026 LOGMNR_ATTRCOL$
     027 LOGMNR_CDEF$                    027 LOGMNR_ATTRIBUTE$
     028 LOGMNR_COL$                     028 LOGMNR_CCOL$
     029 LOGMNR_COLTYPE$                 029 LOGMNR_CDEF$
     030 LOGMNR_DICTIONARY$              030 LOGMNR_COL$
     031 LOGMNR_DICTSTATE$               031 LOGMNR_COLTYPE$
     032 LOGMNR_HEADER1$                 032 LOGMNR_DICTIONARY$
     033 LOGMNR_HEADER2$                 033 LOGMNR_DICTSTATE$
     034 LOGMNR_ICOL$                    034 LOGMNR_HEADER1$
     035 LOGMNR_IND$                     035 LOGMNR_HEADER2$
     036 LOGMNR_INDCOMPART$              036 LOGMNR_ICOL$
     037 LOGMNR_INDPART$                 037 LOGMNR_IND$
     038 LOGMNR_INDSUBPART$              038 LOGMNR_INDCOMPART$
     039 LOGMNR_LOB$                     039 LOGMNR_INDPART$
     040 LOGMNR_LOBFRAG$                 040 LOGMNR_INDSUBPART$
     041 LOGMNR_LOG$                     041 LOGMNR_LOB$
     042 LOGMNR_OBJ$                     042 LOGMNR_LOBFRAG$
     043 LOGMNR_PROCESSED_LOG$           043 LOGMNR_LOG$
     044 LOGMNR_RESTART_CKPT$            044 LOGMNR_OBJ$
     045 LOGMNR_RESTART_CKPT_TXINFO$     045 LOGMNR_PROCESSED_LOG$
     046 LOGMNR_SESSION$                 046 LOGMNR_RESTART_CKPT$
     047 LOGMNR_SPILL$                   047 LOGMNR_RESTART_CKPT_TXINFO$
     048 LOGMNR_TAB$                     048 LOGMNR_SESSION$
     049 LOGMNR_TABCOMPART$              049 LOGMNR_SPILL$
     050 LOGMNR_TABPART$                 050 LOGMNR_TAB$
     051 LOGMNR_TABSUBPART$              051 LOGMNR_TABCOMPART$
     052 LOGMNR_TS$                      052 LOGMNR_TABPART$
     053 LOGMNR_TYPE$                    053 LOGMNR_TABSUBPART$
     054 LOGMNR_UID$                     054 LOGMNR_TS$
     055 LOGMNR_USER$                    055 LOGMNR_TYPE$
     056 LOGSTDBY$APPLY_MILESTONE        056 LOGMNR_UID$
     057 LOGSTDBY$APPLY_PROGRESS         057 LOGMNR_USER$
     058 LOGSTDBY$EVENTS                 058 LOGSTDBY$APPLY_MILESTONE
     059 LOGSTDBY$PARAMETERS             059 LOGSTDBY$APPLY_PROGRESS
     060 LOGSTDBY$PLSQL                  060 LOGSTDBY$EVENTS
     061 LOGSTDBY$SCN                    061 LOGSTDBY$PARAMETERS
     062 LOGSTDBY$SKIP                   062 LOGSTDBY$PLSQL
     063 LOGSTDBY$SKIP_SUPPORT           063 LOGSTDBY$SCN
     064 LOGSTDBY$SKIP_TRANSACTION       064 LOGSTDBY$SKIP
     065 MVIEW$_ADV_AJG                  065 LOGSTDBY$SKIP_SUPPORT
     066 MVIEW$_ADV_BASETABLE            066 LOGSTDBY$SKIP_TRANSACTION
     067 MVIEW$_ADV_CLIQUE               067 MVIEW$_ADV_AJG
     068 MVIEW$_ADV_ELIGIBLE             068 MVIEW$_ADV_BASETABLE
     069 MVIEW$_ADV_EXCEPTIONS           069 MVIEW$_ADV_CLIQUE
     070 MVIEW$_ADV_FILTER               070 MVIEW$_ADV_ELIGIBLE
     071 MVIEW$_ADV_FILTERINSTANCE       071 MVIEW$_ADV_EXCEPTIONS
     072 MVIEW$_ADV_FJG                  072 MVIEW$_ADV_FILTER
     073 MVIEW$_ADV_GC                   073 MVIEW$_ADV_FILTERINSTANCE
     074 MVIEW$_ADV_INFO                 074 MVIEW$_ADV_FJG
     075 MVIEW$_ADV_JOURNAL              075 MVIEW$_ADV_GC
     076 MVIEW$_ADV_LEVEL                076 MVIEW$_ADV_INFO
     077 MVIEW$_ADV_LOG                  077 MVIEW$_ADV_JOURNAL
     078 MVIEW$_ADV_OUTPUT               078 MVIEW$_ADV_LEVEL
     079 MVIEW$_ADV_PARAMETERS           079 MVIEW$_ADV_LOG
     080 MVIEW$_ADV_PLAN                 080 MVIEW$_ADV_OUTPUT
     081 MVIEW$_ADV_PRETTY               081 MVIEW$_ADV_PARAMETERS
     082 MVIEW$_ADV_ROLLUP               082 MVIEW$_ADV_PLAN
     083 MVIEW$_ADV_SQLDEPEND            083 MVIEW$_ADV_PRETTY
     084 MVIEW$_ADV_TEMP                 084 MVIEW$_ADV_ROLLUP
     085 MVIEW$_ADV_WORKLOAD             085 MVIEW$_ADV_SQLDEPEND
     086 REPCAT$_AUDIT_ATTRIBUTE         086 MVIEW$_ADV_TEMP
     087 REPCAT$_AUDIT_COLUMN            087 MVIEW$_ADV_WORKLOAD
     088 REPCAT$_COLUMN_GROUP            088 MVIEW_EVALUATIONS
     089 REPCAT$_CONFLICT                089 MVIEW_EXCEPTIONS
     090 REPCAT$_DDL                     090 MVIEW_FILTER
     091 REPCAT$_EXCEPTIONS              091 MVIEW_FILTERINSTANCE
     092 REPCAT$_EXTENSION               092 MVIEW_LOG
     093 REPCAT$_FLAVORS                 093 MVIEW_RECOMMENDATIONS
     094 REPCAT$_FLAVOR_OBJECTS          094 MVIEW_WORKLOAD
     095 REPCAT$_GENERATED               095 PRODUCT_PRIVS
     096 REPCAT$_GROUPED_COLUMN          096 REPCAT$_AUDIT_ATTRIBUTE
     097 REPCAT$_INSTANTIATION_DDL       097 REPCAT$_AUDIT_COLUMN
     098 REPCAT$_KEY_COLUMNS             098 REPCAT$_COLUMN_GROUP
     099 REPCAT$_OBJECT_PARMS            099 REPCAT$_CONFLICT
     100 REPCAT$_OBJECT_TYPES            100 REPCAT$_DDL
     101 REPCAT$_PARAMETER_COLUMN        101 REPCAT$_EXCEPTIONS
     102 REPCAT$_PRIORITY                102 REPCAT$_EXTENSION
     103 REPCAT$_PRIORITY_GROUP          103 REPCAT$_FLAVORS
     104 REPCAT$_REFRESH_TEMPLATES       104 REPCAT$_FLAVOR_OBJECTS
     105 REPCAT$_REPCAT                  105 REPCAT$_GENERATED
     106 REPCAT$_REPCATLOG               106 REPCAT$_GROUPED_COLUMN
     107 REPCAT$_REPCOLUMN               107 REPCAT$_INSTANTIATION_DDL
     108 REPCAT$_REPGROUP_PRIVS          108 REPCAT$_KEY_COLUMNS
     109 REPCAT$_REPOBJECT               109 REPCAT$_OBJECT_PARMS
     110 REPCAT$_REPPROP                 110 REPCAT$_OBJECT_TYPES
     111 REPCAT$_REPSCHEMA               111 REPCAT$_PARAMETER_COLUMN
     112 REPCAT$_RESOLUTION              112 REPCAT$_PRIORITY
     113 REPCAT$_RESOLUTION_METHOD       113 REPCAT$_PRIORITY_GROUP
     114 REPCAT$_RESOLUTION_STATISTICS   114 REPCAT$_REFRESH_TEMPLATES
     115 REPCAT$_RESOL_STATS_CONTROL     115 REPCAT$_REPCAT
     116 REPCAT$_RUNTIME_PARMS           116 REPCAT$_REPCATLOG
     117 REPCAT$_SITES_NEW               117 REPCAT$_REPCOLUMN
     118 REPCAT$_SITE_OBJECTS            118 REPCAT$_REPGROUP_PRIVS
     119 REPCAT$_SNAPGROUP               119 REPCAT$_REPOBJECT
     120 REPCAT$_TEMPLATE_OBJECTS        120 REPCAT$_REPPROP
     121 REPCAT$_TEMPLATE_PARMS          121 REPCAT$_REPSCHEMA
     122 REPCAT$_TEMPLATE_REFGROUPS      122 REPCAT$_RESOLUTION
     123 REPCAT$_TEMPLATE_SITES          123 REPCAT$_RESOLUTION_METHOD
     124 REPCAT$_TEMPLATE_STATUS         124 REPCAT$_RESOLUTION_STATISTICS
     125 REPCAT$_TEMPLATE_TARGETS        125 REPCAT$_RESOL_STATS_CONTROL
     126 REPCAT$_TEMPLATE_TYPES          126 REPCAT$_RUNTIME_PARMS
     127 REPCAT$_USER_AUTHORIZATIONS     127 REPCAT$_SITES_NEW
     128 REPCAT$_USER_PARM_VALUES        128 REPCAT$_SITE_OBJECTS
     129 SQLPLUS_PRODUCT_PROFILE         129 REPCAT$_SNAPGROUP
    
    129 rows selected.
    Last edited by gandolf989; 12-27-2005 at 12:48 PM.

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by gandolf989
    What about this?

    Code:
      1  SELECT *
      2    FROM ( SELECT ROWNUM rnum, table_name
      3             FROM user_tables ) ut
      4   INNER JOIN
      5         ( SELECT ROWNUM rnum, table_name
      6             FROM ( SELECT DISTINCT table_name
      7                      FROM user_tab_columns )) utc
      8*     ON ut.rnum = utc.rnum
    SYSTEM@vizdev01> /
    You don't know the difference between user_tables and user_tab_columns.

    Tamil

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    Quote Originally Posted by tamilselvan
    You don't know the difference between user_tables and user_tab_columns.

    Tamil
    I know the difference between user_tables and user_tab_columns. I also know that values for rownum vary for each rown based on how it is sorted.

    Code:
     
      1  select rownum rn, a.*
      2    from ( select rownum rna, table_name
      3*            from user_tables order by 1 DESC) a
    CCDOTTSVR@vizdev01> /
    
            RN        RNA TABLE_NAME
    ---------- ---------- ------------------------------
             1        129 SQLPLUS_PRODUCT_PROFILE
             2        128 REPCAT$_USER_PARM_VALUES
             3        127 REPCAT$_USER_AUTHORIZATIONS
             4        126 REPCAT$_TEMPLATE_TYPES
             5        125 REPCAT$_TEMPLATE_TARGETS
             6        124 REPCAT$_TEMPLATE_STATUS
             7        123 REPCAT$_TEMPLATE_SITES
             8        122 REPCAT$_TEMPLATE_REFGROUPS
             9        121 REPCAT$_TEMPLATE_PARMS
            10        120 REPCAT$_TEMPLATE_OBJECTS
            11        119 REPCAT$_SNAPGROUP
            12        118 REPCAT$_SITE_OBJECTS
            13        117 REPCAT$_SITES_NEW
            14        116 REPCAT$_RUNTIME_PARMS
            15        115 REPCAT$_RESOL_STATS_CONTROL
            16        114 REPCAT$_RESOLUTION_STATISTICS
            17        113 REPCAT$_RESOLUTION_METHOD
            18        112 REPCAT$_RESOLUTION
            19        111 REPCAT$_REPSCHEMA
            20        110 REPCAT$_REPPROP
            21        109 REPCAT$_REPOBJECT
            22        108 REPCAT$_REPGROUP_PRIVS
            23        107 REPCAT$_REPCOLUMN
            24        106 REPCAT$_REPCATLOG
            25        105 REPCAT$_REPCAT
            26        104 REPCAT$_REFRESH_TEMPLATES
            27        103 REPCAT$_PRIORITY_GROUP
            28        102 REPCAT$_PRIORITY
            29        101 REPCAT$_PARAMETER_COLUMN
            30        100 REPCAT$_OBJECT_TYPES
            31         99 REPCAT$_OBJECT_PARMS
            32         98 REPCAT$_KEY_COLUMNS
            33         97 REPCAT$_INSTANTIATION_DDL
            34         96 REPCAT$_GROUPED_COLUMN
            35         95 REPCAT$_GENERATED
            36         94 REPCAT$_FLAVOR_OBJECTS
            37         93 REPCAT$_FLAVORS
            38         92 REPCAT$_EXTENSION
            39         91 REPCAT$_EXCEPTIONS
            40         90 REPCAT$_DDL
            41         89 REPCAT$_CONFLICT
            42         88 REPCAT$_COLUMN_GROUP
            43         87 REPCAT$_AUDIT_COLUMN
            44         86 REPCAT$_AUDIT_ATTRIBUTE
            45         85 MVIEW$_ADV_WORKLOAD
            46         84 MVIEW$_ADV_TEMP
            47         83 MVIEW$_ADV_SQLDEPEND
            48         82 MVIEW$_ADV_ROLLUP
            49         81 MVIEW$_ADV_PRETTY
            50         80 MVIEW$_ADV_PLAN
            51         79 MVIEW$_ADV_PARAMETERS
            52         78 MVIEW$_ADV_OUTPUT
            53         77 MVIEW$_ADV_LOG
            54         76 MVIEW$_ADV_LEVEL
            55         75 MVIEW$_ADV_JOURNAL
            56         74 MVIEW$_ADV_INFO
            57         73 MVIEW$_ADV_GC
            58         72 MVIEW$_ADV_FJG
            59         71 MVIEW$_ADV_FILTERINSTANCE
            60         70 MVIEW$_ADV_FILTER
            61         69 MVIEW$_ADV_EXCEPTIONS
            62         68 MVIEW$_ADV_ELIGIBLE
            63         67 MVIEW$_ADV_CLIQUE
            64         66 MVIEW$_ADV_BASETABLE
            65         65 MVIEW$_ADV_AJG
            66         64 LOGSTDBY$SKIP_TRANSACTION
            67         63 LOGSTDBY$SKIP_SUPPORT
            68         62 LOGSTDBY$SKIP
            69         61 LOGSTDBY$SCN
            70         60 LOGSTDBY$PLSQL
            71         59 LOGSTDBY$PARAMETERS
            72         58 LOGSTDBY$EVENTS
            73         57 LOGSTDBY$APPLY_PROGRESS
            74         56 LOGSTDBY$APPLY_MILESTONE
            75         55 LOGMNR_USER$
            76         54 LOGMNR_UID$
            77         53 LOGMNR_TYPE$
            78         52 LOGMNR_TS$
            79         51 LOGMNR_TABSUBPART$
            80         50 LOGMNR_TABPART$
            81         49 LOGMNR_TABCOMPART$
            82         48 LOGMNR_TAB$
            83         47 LOGMNR_SPILL$
            84         46 LOGMNR_SESSION$
            85         45 LOGMNR_RESTART_CKPT_TXINFO$
            86         44 LOGMNR_RESTART_CKPT$
            87         43 LOGMNR_PROCESSED_LOG$
            88         42 LOGMNR_OBJ$
            89         41 LOGMNR_LOG$
            90         40 LOGMNR_LOBFRAG$
            91         39 LOGMNR_LOB$
            92         38 LOGMNR_INDSUBPART$
            93         37 LOGMNR_INDPART$
            94         36 LOGMNR_INDCOMPART$
            95         35 LOGMNR_IND$
            96         34 LOGMNR_ICOL$
            97         33 LOGMNR_HEADER2$
            98         32 LOGMNR_HEADER1$
            99         31 LOGMNR_DICTSTATE$
           100         30 LOGMNR_DICTIONARY$
           101         29 LOGMNR_COLTYPE$
           102         28 LOGMNR_COL$
           103         27 LOGMNR_CDEF$
           104         26 LOGMNR_CCOL$
           105         25 LOGMNR_ATTRIBUTE$
           106         24 LOGMNR_ATTRCOL$
           107         23 LOGMNR_AGE_SPILL$
           108         22 LOGMNRT_MDDL$
           109         21 LOGMNRC_GTLO
           110         20 LOGMNRC_GTCS
           111         19 LOGMNRC_GSII
           112         18 LOGMNRC_DBNAME_UID_MAP
           113         17 HELP
           114         16 DEF$_TEMP$LOB
           115         15 DEF$_PUSHED_TRANSACTIONS
           116         14 DEF$_PROPAGATOR
           117         13 DEF$_ORIGIN
           118         12 DEF$_LOB
           119         11 DEF$_ERROR
           120         10 DEF$_DESTINATION
           121          9 DEF$_DEFAULTDEST
           122          8 DEF$_CALLDEST
           123          7 DEF$_AQERROR
           124          6 DEF$_AQCALL
           125          5 AQ$_SCHEDULES
           126          4 AQ$_QUEUE_TABLES
           127          3 AQ$_QUEUES
           128          2 AQ$_INTERNET_AGENT_PRIVS
           129          1 AQ$_INTERNET_AGENTS
    
    129 rows selected.

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by tamilselvan
    The original poster did not say that the view contains order by clause.
    This is totaly irrelevant! Using ROWNUM to uniquely (and repeatedly, in a way that certain ROWNUM allways identifies the same row returned from view) identify rows returned from view is GUARANTEED way to disaster, just like Jeff said. You would never ever use a ROWNUM to identify rows from view, no matter if it contains ORDERBY or not.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  10. #10
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by jmodic
    This is totaly irrelevant! Using ROWNUM to uniquely (and repeatedly, in a way that certain ROWNUM allways identifies the same row returned from view) identify rows returned from view is GUARANTEED way to disaster, just like Jeff said. You would never ever use a ROWNUM to identify rows from view, no matter if it contains ORDERBY or not.
    Your points are good. I am not denying it.
    However, there are certain situation where rownum is useful to generate a unique key (number).
    Consider this:
    The result set of a view is inserted into another table, and the table has a PK on a number column.
    And the SQL statement may be:
    INSERT INTO MY_TABLE (PK_num_col, c2 ,c3)
    SELECT ROWNUM, v1.c1, v1.C2
    FROM MY_VIEW v1;

    Isn't ROWNUM useful here?

    In my first reply, I said "may be try with rownum". Till now, the original poster did not give full details what he looks for.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width