|
|
c**t
发帖数: 2744 | 3
procedure Foo(pResult OUT type_return_cursor)
as
type MyRow is object (
f1 varchar2(10),
f2 NUMBER,
f3 Date);
type MyTbl is VARRAY(10) of MyRow;
myTbl MyTbl;
stmt varchar2(5000) := '';
begin
myTbl := MyTbl();
for i IN 1..10 loop
myTbl(i).f1 := 'string of ' || to_char(i);
myTbl(i).f2 := i;
myTbl(i).f3 := trunc(sysdate, 'HH24') + i / 12;
end loop;
for i in myTbl.First..myTbl.Last
Loop
if i > 1 then stmt := stmt || CHR(10) || 'UNION' || CHR(10); e |
|
b******g
发帖数: 81 | 4 If it is just one dynamic SQL, the recompile time is not an issue.
The thing is the design is using a cursor, looping through each record.
Inside the loop, there is the dynamic SQL (INSERT INTO). Then every time
calling the dynamic SQL in the loop, it is recompiled - the recompile time
will multiply the number of records.
For thousands of records, the performance might be acceptable, but for
millions level, it will be a big overhead. |
|
j*****n
发帖数: 1781 | 5 I assume your cursor is only for retrieving the table names from the
configure table. Then it should be fine since no million of rows there.
Just curious, why SSIS cannot be used? |
|
b******g
发帖数: 81 | 6 Negative. The cursor is for retrieving at least 100K records.
If I were the original designer, I would have chosen SSIS. Now it is the
halfway, with the design approved, no one would want to go backwards. |
|
b******g
发帖数: 81 | 7 CURSORs surely can be turned into queries. Validation and Data cleaning is
for each column. The way I can think of writing the query is:
=======================================================
INSERT INTO Dest_xxx (COL_A, COL_B,...)
SELECT ufn_CLEAN(COL_A), ufn_CLEAN(COL_B),...
FROM Source
WHERE ufn_VAL(COL_A)=true AND ufn_VAL(COL_B)=true AND ...
=======================================================
There is no index on COL_A, COL_B,...
The above SELECT statement is doing a full table scan. I |
|
B*****g
发帖数: 34098 | 8 任何一个query都是通过cursor实现的，如果没记错是汤姆凯特说的
column 的。 这样的话，我能想到的改写就成了这样：
吧。
loop比起来，速度上没什么优势吧。
queries |
|
j*****n
发帖数: 1781 | 9 who told you that table scan will be the same performance as cursor loop?
give a try man, i bet your query is still much faster. that's the power of
SQL engine.
btw, i still believe SSIS is the best solution for your case. these ETL
steps can be done within one single data flow task. of course, by doing so
your server's memory should be large enough...
column 的。 这样的话，我能想到的改写就成了这样：
吧。
loop比起来，速度上没什么优势吧。 |
|
b******g
发帖数: 81 | 10 我在另外一个Application已经做过这种改写。情况和这次类似，都是对所有的Columns
进行Validation,然后做Data Clean,导表。我当时非常希望CURSOR LOOP改写成Static
Query后速度有很大提高，然而结果却是基本没什么变化（if you wanna bet, you
still can）。
SSIS的确是我对ETL这类Application的首选工具，不过技术路线可不是我定的。 |
|
|
k**0
发帖数: 19737 | 12 使用ROW_NUMBER function可以代替CURSOR, 一个QUERY就OK了.
给包子吧. |
|
w*m
发帖数: 1806 | 13 其实说起来很简单，有两张表，
A(a1, a2, a3, a4)
B(b1, b2, b3, b4)
B表大约有30 million记录，A表大概有12万记录
为了加新的速度，我用了下面的script，可是总是出现
ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$"
too small
请教大家，这种情况如何改进？包子相赠求解
1. create two indexes for two tables,
create index Ai on A(a1, a2);
create index Bi on B(b1, b2);
2. save the following script as x.sql, and run it,
set SERVEROUTPUT ON
DECLARE
CURSOR v_cursor IS
SELECT a1, a2 FROM A;
total NUMBER :=0;
BEGIN
FOR i IN v_cur |
|
|
a9
发帖数: 21638 | 15 不是。
可以用cursor啊。不知道row number行不行。
吗？ |
|
a****b
发帖数: 489 | 16 原则上能有T-sql写的就尽量不用SSIS, IS和Tsql比有performance问题。不过IS的
transformation可以在一定程度上替代cursor。 |
|
n*****y
发帖数: 36 | 17 我再添点砖：
如果import data之后又rebuild index,那么不用update stat相关index,
首先是重复劳动，如果不带fullscan的update stat得到的statistics
还不如rebuild index时得到的准确。
虽然SQL Server会根据import数据量（20%)自动update stat,
但因为data import的任务通常是在maintenance window进行，
完成importhou 我习惯用cursor statement来更新其他的statistics
下面是其中的部分语句(更新指定表12小时内未更新的statistics)
请zenny和各位指正
SELECT
'UPDATE STATISTICS [' + sc.name + '].[' + t.name +'] [' + st.name +']
WITH FULLSCAN'
FROM
sys.stats st
JOIN sys.tables t ON t.object_id = st.object_id
JOIN sys... 阅读全帖 |
|
n*****y
发帖数: 36 | 18 感觉大家是在讨论SQL server,另外俺的query也是在SQL Server
下写的，针对的就是那些user defined statistics 和auto created
statistics(没有对应index)因而会在index rebuild中遗漏。
我把该query包在一个cursor运行来保证所有的stat
都会在import data和index rebuild后updated,不用管是否已触发
auto stat update, 也不用担心做无用功。
谢谢对Oracle的补充，很久没有用到Oracle了，都快忘光了。 |
|
|
i****a
发帖数: 36252 | 20 trying to avoid cursor and loops... |
|
a9
发帖数: 21638 | 21 cursor and loops exists for a reason |
|
g***l
发帖数: 18555 | 22 ID VALUE
1 1
1 2
1 3
1 4
2 1
2 2
2 3
3 1
3 2
3 3
3 4
3 5
3 6
3 7
3 8
3 9
3 10
4 1
我想弄成
ID VALUE
1 12345
2 123
3 123456789
4 1
每个ID不知道有多少VALUE，我取到9就结束了
写了个CURSOR，倒腾了半天没搞对 |
|
B*****g
发帖数: 34098 | 23 ppl usually do not ask plsql questions.
if I ask sql questions I will ask index/join/hint.
if I ask plsql question I will ask diff between (function/procedure/package)
/dynamic sql.
if I ask sql code I will ask Regular Expression/Analysis Function/String
Aggregation/XML/MODEL.
if I ask plsql code I will ask get data from temp table to permanent table
and mark indicator in temp table. (use cursor without bulk, not have
exception in code will be consider as don't know plsql by me, not my boss)
Q... 阅读全帖 |
|
h**e
发帖数: 410 | 24 嗯，谢谢Beijing大侠送上及时雨。
你说的对，gejkl朋友也说过，online coding 考“table join”是个知识点，我想，
可以绕上三个tables，两个以上的joins(先outer left再outer right或者加上self)把
几个columns连在一起。电面是两个人问问题，我原先估计就是考稍微复杂一些的SQL，
select from（比如两张表，加上analytical function，orderby这类的），现在要重
点复习plsql，估计考起来时间不够。
会不会让写个trigger，procedure，cursor？
再次谢谢德艺双馨的Beijing。
生也 |
|
B*****g
发帖数: 34098 | 25 *****only do it if you cannot do it by yourself*****
if you have oracle and toad installed, when they ask create trigger，
procedure, fucntion ....., use toad create a template, then type (don't copy
, and make some change, upper/lower case ...) the template to them.
After type the template, 5 minutes past, hehe, and everything you have done
is right and good. Then you can fill the template with real code.
for others like create cursor, print out one sample as template(also can
print create trigg... 阅读全帖 |
|
h**e
发帖数: 410 | 26 面了一个多小时，感觉已经出局了，大概以下几个问题：
1）SQL online coding
email给了两个表，弄个out join，group by，我忘了用having，被他们提醒（语言问
题，我不知道他们需要的更精细的结果），我还是不明白，后来才想起加上having，他
们才作罢
2）加procedure以及cursor，大致如此。我作的不理想，只能说作对一半
3）nomorlization，denomorlization，概念，引到到DB以及DW话题。
4）Star schema，给个例子，详细说明fact table以及dimensional table
5）java online programming test
6）How JDBC work？
7）Oracle里view有三种，说出来
8）Oracle里面数据库的连接文件是什么
后面两题我彻底投降。还有几个问题我想不起来了，反正没戏了。 |
|
z***y
发帖数: 7151 | 27 这个不奇怪啊。
你可以看一看那个procedure, 你看里面有几个对temp table 的改变， 这些数据的改
变都会触发
recompilation。
我这个版本是2008 SP2的。
create procedure sys.sp_replmonitorrefreshagentdata
as
begin
set nocount on
declare @retcode int
,@agent_id int
,@agent_id2 int
,@publisher_id int
,@xact_seqno varbinary(16)
,@logreader_latency int
,@publisher_db sysname
,@publication sysname
-- security check
-- Has to b... 阅读全帖 |
|
S**H
发帖数: 1256 | 28 student course score
ZT 302 A
ZT 301 B
ZT 303 C
怎样写成这样的一行输出.
ZT A B C
谢谢!!!!
能不能不用cursor..... |
|
n********6
发帖数: 1511 | 29 Background:
Collected phone number from 10 sources for each household. Past validation
indicates phone number may have one of 20 status (1, valid, 2, fax, 3, busy,
4, person died, 5, ill, 6, in jail, ... 20)
Question:
Any way to do one query without using cursor go through whole table? (no
concern on performance issue)
Old Table:
HouseID (PK)
APhoneNumber
AStatus
BPhoneNumber
BStatus
...
GPhoneNumber
GStatus |
|
i****a
发帖数: 36252 | 30 damn it... there are a lot of points within the look up range of each
other...
hum... maybe this cannot be solved without using cursor...
401. |
|
u*********e
发帖数: 9616 | 31 thanks for responding. I use VS to design the rdlc file. It's was a straight
Fields!Stmt_Created_Date.Value drop in textbox. The field of "Group_Code"
and "Group_Member_Code" can be displayed without any problem but not for the
rest.
I did some research. One person raised the similar issue like mine. His
storeprocedure created a table variable and used cursor to combine different
rows into one row then put into the table variable and select the result as
the return set. That's very much like min... 阅读全帖 |
|
u*********e
发帖数: 9616 | 32 thanks for the suggestion. Not a fan of cursor myself either. I will see
what I can do to improve the sp.
the question I have is that, what could cause the report not returning all
the data even though the sp itself returned the data as expected? I thought
SSRS just treated all returned dataset as strings. |
|
g***l
发帖数: 18555 | 33 你把这个TABLE VARIABLE换成TEMP TABLE就可以了。date都用DATETIME，没事不要弄变
量，CURSOR全去掉。不要用SELECT *,有什么COLUMN就写什么COLUMN
declare @Results table (Group_Code nvarchar(10) not null,
Group_Member_Code nvarchar(10) not null,
Stmt_Date_Created nvarchar(10) null,
Stmt_Date_Updated nvarchar(10) null,
Stmt_Date_Approved nvarchar(10) null,
Supp_Date_Entered nvarchar(200) null,
... 阅读全帖 |
|
u*********e
发帖数: 9616 | 34 gejkl,
Thank you very much for helping me answering my question. I was doing
research myself and figuring out the issue. You are right. I rewrite the
query to get rid of cursor and use PATH XML instead.
One interesting thing I observed is that after I updated my sp and run the
report, it gave out an error msg:
"An error occurred during local report processing.Exception has been thrown
by the target of an invocation. String was not recognized as a valid
DateTime.Couldn't store <> in Stmt_Date_Cre... 阅读全帖 |
|
g***l
发帖数: 18555 | 35 其实我发现了CODE的写得好不好，不能只看出不出结果
*第一个是思路要清楚，谁JOIN谁，KEY是什么，什么JOIN，
*我的目标是什么，进来的是什么参数，出去的是什么
*复杂的QUERY,先把FLOW CHART画好，
大家讨论一下，看看有没有什么更好办法， SUBQUERY放到CTE或者TEMP TABLE里，
*不挂不需要的数据和COLUMN
*避免用*，避免用CURSOR，避免长时间的，或者繁琐的UPDATE DELETE (JOIN)
*READ ONLY一律用WITH (NOLOCK)
*容错性，进来的错参数会不会出烂数据,表中有错数据，会不会ERROR OUT
*最后看扩展性，再加个参数要花多长时间改。 |
|
u***t
发帖数: 3986 | 36 Anything better than below? thanks.
1) ...Select... OVER...
2) using Cursor object
3Q |
|
y****w
发帖数: 3747 | 37 loader工具对insert的优势也不在fragment这方面。这里处理大数据就suppose没那么其他东西来捣乱，乱是乱不了的，就是乱了也不怕，这很容易控制和纠正，
loader的问题是怎么和内存(cursor)挂接起来，没有方便的api。似乎只能通过文件（哪怕是内存文件）中转下。
800M是一个极端，就是讲效率必须被重视。
btw, staging表暂时禁用log也是有价值的。
issue:
, |
|
y****w
发帖数: 3747 | 38 源库其实有比较复杂的逻辑，这里简化为一个大表；要把这部分数据copy到另一个数据
库中。怎样做效率最高？linked server已经配置。
bulk insert好像只能导入文件。
有没有什么办法实现其他数据库系统里面的load from cursor？
多谢~ |
|
y****w
发帖数: 3747 | 39 目前就是这样。想看看有没有什么办法优化。
我找了下好像sql server目前还没有load cursor之类的支持。不知道是不是我漏掉了什么重要东西。 |
|
y****w
发帖数: 3747 | 40 这些都不是没想到过。
应用是别人的，不可能大动的。就好比我知道怎样设计能更完美，但一个东西已经在那
里了，哪能随便改架构。
其实我就想问下有没有load cursor或类似的高效实现。 |
|
g***l
发帖数: 18555 | 41 你就是因为不懂人家的ARCHITECTURE，所以就没法动人的东西，cursor从来都是最慢的
，如果搞不懂COPY的数据是什么，有多少，多久更新一次，更新数量是多少，你怎么可
能设计出高效的COPY方法？谁让你做的，找谁要REQUIREMENT，穷糊弄是不行的，将来出
了问题，有的数据没COPY过去，DEVELOPER添加或者改了数据没告诉你，或者JOB FAILED，
到底是谁的责任，还不都赖在你身上。 |
|
g***l
发帖数: 18555 | 42 随便总结了一下，不包括ETL,抛砖引玉了，
1. never use * in query, only use specific columns and rows you need, never
use extra
2. table uses dbo prefix, selection uses with (nolock)
3. repeated code section can be replace by UDF or stored procedure
4. join is always on the key. if join is not the key, you need to be very
careful
5. layout flow chart for complicated stored procedure before coding
6. CTE is a good choice to replace subquery, table variable and temp table
7. all the work should be done on the temp t... 阅读全帖 |
|
g***l
发帖数: 18555 | 43 有时候确实要用CURSOR的，虽然慢，但不LOCK，对特别忙的OLTP |
|
n********6
发帖数: 1511 | 44 Background:
The airline is auditing its Passengers Reservation. Each Passenger may have
unlimited flight changes, and the flight tariff will be adjusted.
Question: To find the changes for each passenger.
Reservation_Table:
ReservationID, Name, flight, ..., Tariff, NewReservationID
1, Beijing, UA917, ..., 3
2, ..., null
3, Beijing, UA816, ..., 100
4, ..., null
...
100, Beijing, UA998, .., m
...
m, Beijing, ...., null
...
n,...
One approach: cursor, identify the reservation update one by one, and ... 阅读全帖 |
|
j****s
发帖数: 881 | 45 多谢多谢，gejkl 和beijing的解法看着比较简单。
我能想到的是用cursor一行行读，但是比较麻烦。
以上各位各领一个包子以表感谢。 |
|
j****s
发帖数: 881 | 46 表1
CM CN CL
M1 N1 A
M1 N1 C
M3 N3 B
M3 N3 D
M4 N4 A
。。。
变表2
CM CN CA CB CC CD
M1 N1 A C
M3 N3 B D
M4 N4 A
。。。
是不是就只能用cursor了？还有什么简便方法吗？ |
|
y****9
发帖数: 144 | 47 the cursor returned by using sp_describe_cursor_columns has a column called:
data_type_sql which is smallint type?
How can I obtain the data type name (like: varchar2 int etc) from data_type
_sql intergers?
I guess there should be a way to do, like from object_id to get object_name.
Thanks! |
|
v***e
发帖数: 2108 | 48 我觉得这种说法在以前是可以理解，但是现在未必是这样
interview倒也罢了，但是如果是实际工作，XML这种东西一
定比pl/sql快么？ 好像很难说吧。
写一个SQL statment并不能简化solution的内在逻辑，只是把所有
东西压缩在一个stmt里面了。
而且即使是写一个巨大，复杂，难以维护的single SQL statement，
也未必一定比一个简单，逻辑清晰的compiled pl/sql function
更efficient，其中一个原因是一旦你SQL stmt复杂之后，plan
generation 和plan execution都很难做到opitimized。而且复杂的sql
像cursor sharing，result cache这种feature就更难运用。
从RDBMS内核的角度看，我个人不太赞成把任何东西都写在一个SQL里面。 |
|
n****n
发帖数: 59 | 49 真是魔鬼定律啊，发帖后再google一下找到了好多。还是没法用云兄说的partition
window （俺用的是SQL Sever 2008，真的有这个功能吗？）。现在是这样做的
SELECT t1.ID, t1.Date, AVG(t1.Value), STDEV(t2.Value)
FROM table as t1
JOIN table as t2
ON t1.ID = t2.ID
AND t1.Date BETWEEN DATEADD(dd, -10, t2.Date) AND DATEADD(dd, -1, t2.Date)
GROUP BY t1.ID, t1.Date
ORDER BY t1.ID, t1.Date
另外看到有人说居然这里用cursor比set based方法好， http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911
不知真假，回头我再试试。 |
|
B*****g
发帖数: 34098 | 50 CINAOUG征文
主要真对PL/SQL Advanced技术的一些应用，初学者或者不太熟悉PL/SQL的同学请先自学PL/SQL的基本概念和技术。这个系列主要是着重在When Where Why而不How，不可能取代大家看书学习。欢迎有经验，有兴趣的朋友一起参与分享PL/SQL技术。初步想法系列包括一
下几个方面:
1. Best Practice for starting PL/SQL programming
2. New features of Oracle 10g/11g for basic PL/SQL
3. Record
4. Cursor
5. Collection
6. Exception Handle
7. Dynamic SQL and PL/SQL
8. Large Objects
9. Debug PL/SQL with TOAD
10. XML in PL/SQL
11. Java In PL/SQL
12. Tuning PL/SQL
13. Security in PL/SQL
14. Scheduled Jobs and Advance... 阅读全帖 |
|
