c*****d
发帖数: 6045 | 1 或者这么说
你存完rowid之后，如果一些操作导致这一行的rowid改变了
以后你再以rowid作为where语句的条件就有问题了 |
|
B*****g
发帖数: 34098 | 2 一个procedure， 是用第一种方法好，还是第二种(id 是table的pk，unique index？
为什么？ 谢谢
1.
select rowid，....
into lnRowi1d, ......
from tab1
something between
update tab1
set .....
where rowid = lnRowi1d
2.
select id，....
into lnId, ......
from tab1
something between
update tab1
set .....
where id = lnId |
|
B*****g
发帖数: 34098 | 3 谢谢。
如果用cursor ... for update, 好像记得也是隐含用rowid（这个不确定），会不会也
有影响？ |
|
c*****d
发帖数: 6045 | 4 如果是cursor select ... for update
至少我不知道会隐含用rowid
select ... for update只是上锁 |
|
v*****r
发帖数: 1119 | 5 作为 tom 的 fan, 最喜欢他解释问题时的言简意赅，但发现 tom 偶尔也会迷惑人。他
这个帖子开始解释的很好，rowid 会变，什么情况下会变，然后给出一个例子：
“It is perfectly safe to use the rowid in ALL CASES however, assuming you
combine it with
the primary key as well:
update t
set...
where rowid = :x and primary_key = :pk;
”
看到这就忍不住笑了，rowid = :x， 既然您老人家刚解释了rowid会变，为什么又给出
这么一个 bad example. This update query is assuming the rowid value is
stored somewhere before being passed to update statement, to protect the
update transaction you will have to lock the rows wh... 阅读全帖 |
|
B*****g
发帖数: 34098 | 6 In my test
r1 rowid = 1
r2 rowid = 2
是重复的，只须保留一个。
假设delete先到r1，一看没有比rowid=1小的，好，保留r1。这时候r1没有被lock。然
后经过漫长的时间终于到了r2，现在还能不能保证r1的rowid是1？
如果能，是什么使得r1的rowid不变？
如果不能保证，r1的rowid会不会变成3以致使r2也会被保留?或者这时候的r2的rowid会
不会已经变成0了？
我的例子也不一定就对，只是理论上一种假设，还没有想到怎么测试。大家有可能看不
懂我的例子是因为大家盯在delete上，没有注意到ckeck rowid大小上。 |
|
B*****g
发帖数: 34098 | 7 I know this for normal columns, just not sure about rowid. Did one test,
seems in same SQL (no any SP) works. So for one simple sql your guys are
right, rowid can not be changed which is same as normal column.
CREATE TABLE test_delete (ID NUMBER(1), VAL VARCHAR2(1));
INSERT INTO test_delete VALUES(1, 'A');
INSERT INTO test_delete VALUES(2, 'B');
INSERT INTO test_delete VALUES(3, 'C');
COMMIT;
CREATE OR REPLACE FUNCTION LOCK_TEST(vID NUMBER) RETURN VARCHAR2 IS
tmpVar VARCHAR2(100);
tmpVar1 VA... 阅读全帖 |
|
i*****9
发帖数: 293 | 8 modify the sql to suit your need.
delete from $table_name where rowid in
(
select "rowid" from
(select "rowid", rank_n from
(select rank() over (partition by $primary_key order by rowid) rank
_n, rowid as "rowid"
from $table_name
where $primary_key in
(select $primary_key from $table_name
group by $all_columns
having count(*) > 1
)
)
)
where rank_n > 1
) |
|
M*********e
发帖数: 190 | 9 Don't know TSQL.
In Oracle, use rowid pseudocolumn.
思想就是先找到有discint col1的所有行（得到set 1）和有distict col2的所有行(得
到set 2)。取 set 1和 set 2中rowid相同的行.
不知道有没有漏洞。
#################
create table test(col1 varchar2(5), col2 varchar2(5));
insert into test values ('V1','B');
insert into test values ('V12','F');
insert into test values ('V3','F');
insert into test values ('V2','C');
insert into test values ('V2','D');
insert into test values ('V3','E');
#################
select * from test where rowid i... 阅读全帖 |
|
B*****g
发帖数: 34098 | 10 假设有一对dup,一个rowid是1，一个是2.
delete先执行到rwoid=1，这时候不delete。
然后经过10分钟执行到了rowid=2，此时rowid=1的那个record由于某种愿因变成了
rowid=3，这个时候会delete吗？
the
rowid |
|
B*****g
发帖数: 34098 | 11 rowid=1又没有被delete，不会被lock。其实你关于rowid的解释挺好，要是需要更深了
解的同学可以看tom的link，写的很清楚，还有例子。至于楼主的问题，最简单就是加
一个pk。这些rowid讨论和问题其实不搭边，说的太复杂反而容易把新手搞糊涂。
***********************************
--Test for delete lock
CREATE TABLE test_delete (ID NUMBER(1), VAL VARCHAR2(1));
INSERT INTO test_delete VALUES(1, 'A');
INSERT INTO test_delete VALUES(2, 'A');
COMMIT;
SELECT * FROM test_delete;
DELETE FROM test_delete t1
WHERE EXISTS (SELECT * FROM test_delete t2 WHERE t2.val = t1.val AND t1.
rowid > t2.rowid);
-- No commit here... 阅读全帖 |
|
y****9
发帖数: 144 | 12 I like analytical function solution, repeat in Oracle:
SQL>
SQL>select id,value, row_number() over (partition by id, value order by
rowid) rn from t;
ID VALUE RN
---------- ---------- ----------
1 1 1
2 1 1
2 1 2
3 1 1
3 1 2
3 1 3
6 rows selected.
SQL>
SQL>
SQL>
SQL>delete from t where rowid in (
2 select rowid... 阅读全帖 |
|
v*****r
发帖数: 1119 | 13 That is expected behavior, because the first delete didn't lock row id=1.
Let's go though two deletes in your case:
1. First delete is randomly picking up row to delete, it happens to pickup
id=2 because of your inserting sequence. In terms of all the locks used with
the first delete using rowid, it is same as "delete from test_case where id
=2":
id=2 row is locked against concurrent write, test_delete table is also
locked in shared-exclusive mode to against DDL change.
2. second delete runs tho... 阅读全帖 |
|
v*****r
发帖数: 1119 | 14 ROWID is just a function that returns a row's physical location, which
includs: file location + block location + row_offset_within_a_block.
If a rows move to different physical location, its rowid changes, simple as
that.
During a transaction on a table, all rows' physical locations is protected
by locking. Even you only modify one row in a table, all rows' rowids are
guaranteed not to be changed since there is no way you can trigger row
moving once you have a DML hitting that table.
Across tran... 阅读全帖 |
|
m****d
发帖数: 372 | 15 以前处理过这个问题，用的也是analytical function,与此类似。
delete from $table_name where rowid in
(
select rowid from
(select rank() over (partition by $all_columns order by rowid) rank_n,
rowid as "rowid"
from $table_name
)
where rank_n > 1
) |
|
o****o
发帖数: 8077 | 16 排序需要额外产生文件。你的原始文件就有50GB，你需要确保你的剩余硬盘空间够大才
行。如果你有100G是剩余的，应该没问题。
其实你应该告诉我们你的文件有多少行，有几个需要排序的变量，文件大小本身并不重要
考虑到performance，根据你的具体问题，可以这么做
先对那个50G的文件，每一行写好行号，就是把_N_标出来，vname=rowID
然后把这个rowID连同需要排序的变量分离出来，这样文件一般会小点，除非你的50G的
文件全都是需要排序的变量组成，那就没辙了。这样大的文件会非常长，排序很慢。
把这个小文件排序后，你的rowID也排好了，然后在set排序好的文件的时候，set
original_file point=rowID就把两个文件链接起来了
我在PC上最大sort过10GB的文件，等了我45分钟。你要sort50GB的，就算你的PC能搞，
估计你也等的不耐烦了 |
|
L*******r
发帖数: 8961 | 17 如果是ＳＱＬ Server的话，可以用Common Table Expression解决。
WITH [T] AS
(SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS ROWID FROM table)
SELECT T.[RowID]
FROM T LEFT JOIN table ON T.[ROWID] = table.[key]
WHERE table.[key] IS ＮＵＬＬ
如果以上的ＳＥＬＥＣＴ没有返回结果的话，删掉的就是最后一个最大的数。因为一般
Ａｕｔｏ Ｎｕｍｂｅｒ的列都是有Ｉｎｄｅｘ的，所以以上的操作会比较快。
不知道其他的数据库管理系统有没有ＣｏｍｍｏｎＴａｂｌｅＥｘｐｒｅｓｓｉｏｎ。 |
|
h*******l
发帖数: 22 | 18 N = 行数
M = 列数
k - 第k个
X = Min(k, NM - k) // k 和 NM - k 的最小值
Y = Min(N, M) // 行数和列数的最小值
复杂度： O( X log(Y))
ok, 算法如下，假设行数少，k 比 NM - k 小
取第一列， 做一个heap， 插入 , 即 数值 和 哪一行的数值
ExtractMin() 取出堆里头最小的那个
到rowID 取出下一个，插入堆中
如此反复直到第 k 个 ExtractMin() 得到的就是第 k 个数
END
堆的大小一直都是行数 （如果列数少，就是列数， 因为行列都排好序了， 所以哪个
方向都可以）
想不出 O(k) 的方法了， 我觉得再变态也得至少看到头k个数吧。 所以我觉得这个O(
k log(行数)) 的复杂度已经差不多够低的了 |
|
s****y
发帖数: 581 | 19 In LZ's question, rowid is safe enough to do delete duplicates based on the
uniqueness of the rowid at the same point of time.
In asktom scenario, user try to do something based on assumption that rowid
has never changed historically.Apparently, it is not safe since so many
things can lead to row movement/reoccupied. |
|
y****9
发帖数: 144 | 20
I am not necessarily understanding what you said as I did not go through all
the details. But regarding what you said above few sentence - if at the
time you issue the query (Q1), you saw r1 with rowid=1, then no mather who
updated r1 rowid= whatever value later, commit or not commit, for the Q1's
sake, rowid=1 is always true. Is this a common understanding? |
|
c*****d
发帖数: 6045 | 21 针对3
来做个实验就能说明问题了
SQL> create table tlu.emp as select * from dba_objects;
SQL> select object_name,object_type from user_objects;
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
EMP TABLE
SQL> alter table tlu.emp add constraint pk_emp primary key (object_id);
SQL> select constraint_name,constraint_type,table_name
from user_constraints;
CONSTRAINT_NAME C TABLE_NAME
------------------------------ - --------------... 阅读全帖 |
|
s******y
发帖数: 352 | 22 DATA HAVE;
INFILE CARDS TRUNCOVER;
INPUT A B C D;
ROWID=_N_;
CARDS;
1
7
3 3
2 5
. 5
. 5 3
. . 4 5
. . 5 8
. . 2 3
. . . 1
;
RUN;
PROC TRANSPOSE DATA=HAVE OUT=HAVE1(WHERE=(NOT MISSING(COL1)));
BY ROWID;
VAR A B C D;
RUN;
PROC SORT DATA=HAVE1;
BY _NAME_ ROWID;
RUN;
DATA HAVE2;
SET HAVE1;
BY _NAME_;
ID+1-(FIRST._NAME_)*ID;
RUN;
PROC SORT DATA=HAVE2;
BY ID _NAME_ ;
RUN;
PROC TRANSPOSE DATA=HAVE2 OUT=WANT(DROP=_NAME_ ID);
BY ID ;
VAR COL1;
ID _NAME_;
RUN; |
|
l***o
发帖数: 5337 | 23 一个data set， 结构很简单：
rowid, var1, var2, ... var100.
我现在就对var1-var100 的一种值（‘A')感兴趣，希望查一查 ’A'的位置都在哪儿。
所以想搞个output data set，只有两行：
rowid， location
比如说， 第一行的 var19， var77的值是‘A'， 那输出set中就应该有两行：
rowid location
1 19
1 77
请问这个怎么写才好？谢谢！ |
|
r********3
发帖数: 2998 | 24 呵呵，一般考memory allocate等细节，可以看出这个人是工程派还是面试派的。
那个Predicate是javax.sql.rowset的interface, for all FilteredRowSet。考这道题
，就是看你真的熟悉JDBC不。熟悉JDBC的人，几乎背都可以背得出来。不熟悉的人，一
下子还很难明白题目的意思。这个题目，明显不是靠楼主算法，思维的能力，而仅仅也
是想鉴别楼主到底是工程派还是面试派。
LZ的sql方面问题不是很大，但是也不够好。首先你的课程号怎么没有年份？semester
这些。大家选课的时候，难道不考虑是哪个学期的课程吗？
其次varchar到底有多长？是varchar(10)还是varchar(4096)甚至还是long varchar?
是不是unique的？varchar在数据库里面的存储空间是固定的，所以用不好容易造成空
间浪费，而且在读数据的时候也造成Disk I/O的浪费。这个表很小，disk i/o上的浪费
可能比你实际要用的数据都还大。起码你的rowid应该用unique bigint?还有你的rowid
是auto-increm... 阅读全帖 |
|
i**h
发帖数: 424 | 25 Why 00000100 -> C?
What does 00000011 do?
非专业解答
假设LED电流方向是从01234567 -> abcdefgh
MOV DPTR, #FontData
loop:
MOV R0, P0 ;read current input
CLR C ;clear carry flag
MOV A, 1 ;rowId=0
next_row: ;might need a short delay between rows
MOV R1, DPTR + R0 ;read bitmap from font data
MOV P1, A ;select row
MOV P3, R1 ;light row
RLC A ;rowId++
INC R0 ;next byte... 阅读全帖 |
|
w*r
发帖数: 2421 | 26 来讨论一下join index的用法，说说你们的经历，我先砸一块砖，V2R6.1的optimizer
有问题
我有一个commonly used join(为了简便，很多syntax 的细节错误就不管了）,
Table A Inner Join B on
a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3
and a.start_date <= b.start_date and a.end_date >= b.end_date
Table A 和 B分别对这些join的col都有hash index.
我建立了一个A $ B 的JI
create A_B_JI as
select a.col1, b.col1, a.col2, b.col2 , a.col3, b.col3 a.started_date
a.rowid, b.rowid
from a join b
on a.col1=b.col1 and a.col2=b.col2 and a.col3=b.col3
and a.start_date <= b.start_date an |
|
s****y
发帖数: 581 | 27 if your DB is Oracle, suppose your table name is table_name:
delete from table_name tn_1
where exists(select *
from table_name tn_2
where (
(tn_2.first_name = tn_1.first_name)
or
(tn_2.first_name is null and tn_1.first_name is null)
)
and
(
(tn_2.last_name = tn_1.last_name)
or
(tn_2.l... 阅读全帖 |
|
d********y
发帖数: 2114 | 28 这个rowID是table本身的属性么？
删记录会立刻更新这个rowID么？ |
|
y****9
发帖数: 144 | 29 Don't agree. the point is ROWID also followd Oracle consistent reads
mechanism.
It is not saying ROWID of some rows of a table cannot be changed during soem
DML on the table.
If in session 1 I update a row, can't I move other rows around by delete/
insert? |
|
g*****r
发帖数: 130 | 30 How do I compare two rows to see if they are exactly the same? This means if
all fields are the same for the two rows, the two rows are considered dupes
. How could I remove the dupes quickly?
For example, I have a table called "game_play", and it contains columns
player_id, game_name. I can remove dupes like this:
delete from game_played a
where a.rowid > ( select b.rowid from game_played b
where a.player_id = a.player_id
and a.game_name = b.game_name)
It works, but what if I have 100 columns i... 阅读全帖 |
|
T****U
发帖数: 3344 | 31 try this, which is a little less silly
delete from test1 where rowid not in (select min(rowid) from test1 group by
key1, key2, key3,...);
if
dupes
column |
|
c*****d
发帖数: 6045 | 32 对mview了解不多，一起讨论
1. 如何判断是否需要QUERY REWRITE
[ ] 你是要判断SQL是否REWRITE吗？如果是这个意思，用explain plan或者DBMS_MVIEW
.EXPLAIN_REWRITE就可以看到
2. 如果想每三分钟refresh一次，这么写有何不妥？
BUILD IMMEDIATE REFRESH FAST START WITH (sysdate) NEXT (sysdate+3*60/(60*60*
24)) WITH rowid AS
[ ] 语法没问题，就是refresh太频繁了，除非你们业务有这个需要，我们是一天
refresh一次，oltp交易流水给DW作分析
3. 建Materialized View，加不加primary key有什么区别？如果原table的field就有
了index，在MV里就不存在了？
[ ] 你理解错了，这个PK不是mview上的PK，用来判断数据变化
是基于table上的PK
mview要么基于PK，要么基于rowid
4. 当缺乏大数据库做测试的情况下，使用Materialized View TUN... 阅读全帖 |
|
c*****d
发帖数: 6045 | 33 oracle dba教材里专门讲过这个问题
select a.*
from table_name a, table_name b
where a.parentId = b.parentId
and a.tag = b.tag
and a.rowid <> b.rowid
你要的是update，一样的道理 |
|
m******u
发帖数: 12400 | 34 个人觉得这个题目出的不好（比如第二题），answer key 也不算怎样，第一题就可见
一斑。
。。。。。。。。。。。。。。
Table 1 : DEPT
DEPTNO (NOT NULL , NUMBER(2)), DNAME (VARCHAR2(14)),
LOC (VARCHAR2(13)
Table 2 : EMP
EMPNO (NOT NULL , NUMBER(4)), ENAME (VARCHAR2(10)),
JOB (VARCHAR2(9)), MGR (NUMBER(4)), HIREDATE (DATE),
SAL (NUMBER(7,2)), COMM (NUMBER(7,2)), DEPTNO (NUMBER(2))
MGR is the empno of the employee whom the employee reports to. DEPTNO is a
foreign key.
QUERIES
1. List all the employees who have at least one person reporting to them.
2. L... 阅读全帖 |
|
c**t
发帖数: 2744 | 35 delete TBL WHERE ... RETURNING rowid INTO |
|
c*******s
发帖数: 4 | 36 1 rowid+group by 2 row_number 3 rank |
|
B*********e
发帖数: 9 | 37 真屈辱，心情恢复平静中。
写出来大家面他家的时候也做好思想准备吧
L公司刚上市，现在狂招人中,不知道是不是大家都cash out了。
本来要面9个人，后来只见了3个。就被人家说“I do not think we should carry on, in respectful of your time and our time”
写写吧，攒rp。大家轻拍，知道自己业务知识不强，回家读书过后再来过。
第一场本来要见host manager，后来跑来两个engineer说调换一下顺序。没有寒暄，直接问，我们问你technical的问题。
第一个给你一个FilterIterator Class（predicate p, iterator i), 写
bool hasnext() 还有bool next()
搞了半天什么是predicate，还有那个iterator i到底是哪个list上头的。但是后来这个在对方提示下写的差不多了。
第二个说给你一个很长的string list，让你分行打印。然后告诉你一行只能写L（比如
25）个字符，而且首位和末尾不能是空格，空格只能在中间，两个词中间可以空两... 阅读全帖 |
|
b***r
发帖数: 4186 | 38 把我们公司的table令出来看了，都是varchar，包括rowid |
|
p*****2
发帖数: 21240 | 39
是不是还要存一个currID？这样可以直接取下一个元素。 |
|
c********t
发帖数: 5706 | 40 可以用一个数组存, arr[rowId] = currID;如果用list,也可以iterator next |
|
h******e
发帖数: 52 | 41 设计一个 data structure something like excel's cell.
The cell has two types: value type and formula type (sum of other cells). My
design is as below.
Class Cell
{
rowId;
ColId;
type; //enum-> value or sum of other cells
Value; //if type is value, this field is the value, otherwise
meaningless
List cell; // if type is forumla, the field is used to show which
other cells are needed to sum
}
是不是还有更好的design? | |
|
f*******r
发帖数: 976 | 42 能否展开来讲解？
cells
设计一个 data structure something like excel's cell.
The cell has two types: value type and formula type (sum of other cells). My
design is as below.
Class Cell
{
rowId;
ColId;
type; //enum-> value or sum of other cells
Value; //if type is value, this field is the value, otherwise
meaningless
List cell; // if type is forumla, the field is used to show which
other cells are needed to sum
}
是不是还有更好的design? | |
|
n******6
发帖数: 1829 | 43 【 以下文字转载自 JobHunting 讨论区 】
发信人: BrightSmile (BrightSmile), 信区: JobHunting
标 题: 去某刚上市公司面试被赶出来了。
发信站: BBS 未名空间站 (Thu Aug 11 17:07:23 2011, 美东)
真屈辱，心情恢复平静中。
写出来大家面他家的时候也做好思想准备吧
L公司刚上市，现在狂招人中,不知道是不是大家都cash out了。
本来要面9个人，后来只见了3个。就被人家说“I do not think we should carry on, in respectful of your time and our time”
写写吧，攒rp。大家轻拍，知道自己业务知识不强，回家读书过后再来过。
第一场本来要见host manager，后来跑来两个engineer说调换一下顺序。没有寒暄，直接问，我们问你technical的问题。
第一个给你一个FilterIterator Class（predicate p, iterator i), 写
bool hasnext() 还有bool next()
搞了半天... 阅读全帖 |
|
|
|
|
a**w
发帖数: 11 | 47 Can anyone tell me the default password for DB2 Enterprise?
And is there anything in DB2 that looks like Oracle rowid, or
SQL Server identity column?
Thanks |
|
fu
发帖数: 6 | 48 rowid 不是ORACLE的吗？SQL SERVER 也有吗？ |
|
|
w*r
发帖数: 2421 | 50 I am using Oracle 10g, java store procedure is way more efficient than PLSQL
stored procedure. Indexes were build on the columns I tried to join.
Basically, if the larger table has full statistics, then the optimizer
choose full tablescan for the larger table and hash join. If I delete the
statistics on larger table, only leave the indexes statistics, the optimizer
choose bitmap index join first and then access table by rowid by the result
of the join:D
because |
|
