-
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
-
No.
May be try with rownum.
Tamil
-
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
-
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?
-
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
-
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.
-
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
-
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.
-
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?
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|