Re: Renumber a set of grupped rows?
Just out of curiocity, and while I am trying to learn about Row_NUMBER(), how would you code the following to do an update on the 2nd column? select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) x from emp thx maa > > the analytical functions are available in 8i > (..) wrap then in "execute immediate" within PL/SQL > > An update is possible in PL/SQL but it's easier to create a new table and > swap them around - unless that creates some major headaches for you. At 08:04 PM 1/20/2004, you wrote: Maryann SQL> select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) x from emp 2 ; DEPTNO X -- -- 10 1 10 2 10 3 20 1 20 2 20 3 20 4 20 5 30 1 30 2 30 3 30 4 30 5 30 6 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Renumber a set of grupped rows?
At 06:49 PM 1/20/2004, you wrote: Not pretty but it should work: It does, thanks! maa SQL> create or replace procedure emprec 2 is 3cursor empcursor is 4 select empno, recno from emptest order by empno for update of recno; 5v_empno number := 9; 6v_count number := 0; 7 begin 8for x in empcursor loop 9 if x.empno <> v_empno then v_count:=1; 10 v_empno := x.empno; 11 end if; 12 update emptest 13 set recno = v_count 14 where current of empcursor; 15 v_count:=v_count+1; 16end loop; 17 end emprec; 18 / Procedure created. SQL> execute emprec; PL/SQL procedure successfully completed. SQL> commit; Commit complete. SQL> drop procedure emprec; Procedure dropped. --Jeff -Original Message- Sent: Tuesday, January 20, 2004 3:24 PM To: Multiple recipients of list ORACLE-L I have a 1-rows table with 2 columns, Emp_ID and Req_ID. There are about 150 different emp_ids in these 1+ records. What I want to do is the following: For every different Emp_id, I need the Rec_ids that corresponds to it to be updated/renumbered starting from 1 and keep going up by 1. So I want it to look something like this: Emp_ID Req_ID 10001 001 10001 002 10001 003 10001 004 10001 005 10001 006 10001 007 10001 008 10002 001 10002 002 10002 003 10002 004 10002 005 10003 001 10004 001 10004 002 10004 003 10004 004 10004 005 10004 006 etc Any ideas? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Eberhard, Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Renumber a set of grupped rows?
> select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) > from emp > / Hm... but thats not available in 8i, is it? and besides, I want to update the table, not just select from it... ... thx maa At 06:19 PM 1/20/2004, you wrote: Maryann You can use the new windowing function, here is test select working on emp :-) select deptno, row_number() over (PARTITION BY DEPTNO order by deptno) from emp / DEPTNO ROW_NUMBER()OVER(PARTITIONBYDEPTNOORDERBYDEPTNO) -- 101 102 103 201 202 203 204 205 301 302 303 304 305 30 6 Maryann Atkinson wrote: I have a 1-rows table with 2 columns, Emp_ID and Req_ID. There are about 150 different emp_ids in these 1+ records. What I want to do is the following: For every different Emp_id, I need the Rec_ids that corresponds to it to be updated/renumbered starting from 1 and keep going up by 1. So I want it to look something like this: Emp_ID Req_ID 10001 001 10001 002 10001 003 10001 004 10001 005 10001 006 10001 007 10001 008 10002 001 10002 002 10002 003 10002 004 10002 005 10003 001 10004 001 10004 002 10004 003 10004 004 10004 005 10004 006 etc Any ideas? Thanks, maa -- Best regards/Venlig hilsen /*Peter Gram*/ <mailto:[EMAIL PROTECTED]> Miracle A/S <http://www.miracleas.dk/> Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> SQL Server Master Class 8-10 Marts, Database Forum 28-30 October Master Class 17-19 Januar 2005. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Peter Gram INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Renumber a set of grupped rows?
I have a 1-rows table with 2 columns, Emp_ID and Req_ID. There are about 150 different emp_ids in these 1+ records. What I want to do is the following: For every different Emp_id, I need the Rec_ids that corresponds to it to be updated/renumbered starting from 1 and keep going up by 1. So I want it to look something like this: Emp_ID Req_ID 10001 001 10001 002 10001 003 10001 004 10001 005 10001 006 10001 007 10001 008 10002 001 10002 002 10002 003 10002 004 10002 005 10003 001 10004 001 10004 002 10004 003 10004 004 10004 005 10004 006 etc Any ideas? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: var source_data varchar2(12)
Got it Tim, thanks. maa At 05:04 PM 11/11/2003, you wrote: Maryann, SOURCE_DATA is a SQL*Plus variable, declared using the VAR (a.k.a. VARIABLE) command. It can be referenced inside the PL/SQL block and then used by SQL*Plus commands (such as PRINT) or SQL commands (such as SELECT) outside of the block. My guess is that it is being used for communicating data values into or out of the PL/SQL block. Hope this helps... -Tim > My original posting had a few lines truncated, so here I > go again: > I'm looking at a PL/SQL script that goes like this > > -- Header > var Source_Data VARCHAR2(12) > > DECLARE > Num1 NUMBER; > > BEGIN > Source_Data := '&1'; > > What's the purpose of having a VAR statement in front or > Before of the Declare section? Is it used for > bind-variables, is that what it is? > thx > maa > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.net -- > Author: Maryann Atkinson > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com San Diego, California-- > Mailing list and web hosting services > -- > --- To REMOVE yourself from this mailing list, > send an E-Mail message to: [EMAIL PROTECTED] (note > EXACT spelling of 'ListGuru') and in the message BODY, > include a line containing: UNSUB ORACLE-L (or the name of > mailing list you want to be removed from). You may also > send the HELP command for other information (like > subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Mladen Re: var source_data
At 04:59 PM 11/11/2003, you wrote: The purpose is to confuse the heck out of the person who tries to understand it. Using military vocabulary, so popular these these days, the "var" before the declare block is here to confuse and deceive the enemy. You see, if the program was written in such a way that another human being could understand it and, god forbid, maintain it, the job security of the original coder would be in double jeopardy. With unemployment numbers as high as they are, coding clearly and documenting your code is a direct attack on the still frail economy. ... really?!?!?! > Hopefully, my explanation answers your question. No, actually it didnt. It wasted my time, thats for sure, not only the one reading it but the one replying to it too, but as far as answering it, I've read other postings where you've done a better job. > Feel free to ask more questions. Certainly. And you feel free to answer them the way you've answered the ones you did a better job on. :-) maa On 11/11/2003 04:44:24 PM, Maryann Atkinson wrote: > My original posting had a few lines truncated, so here I go again: > > I'm looking at a PL/SQL script that goes like this > > -- Header > var Source_Data VARCHAR2(12) > > DECLARE > Num1 NUMBER; > > BEGIN > Source_Data := '&1'; > > What's the purpose of having a VAR statement in front or Before > of the Declare section? Is it used for bind-variables, is that what it is? > > thx > maa > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Maryann Atkinson > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
var source_data varchar2(12)
My original posting had a few lines truncated, so here I go again: I'm looking at a PL/SQL script that goes like this -- Header var Source_Data VARCHAR2(12) DECLARE Num1 NUMBER; BEGIN Source_Data := '&1'; What's the purpose of having a VAR statement in front or Before of the Declare section? Is it used for bind-variables, is that what it is? thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
var source_data varchar2(12)
I'm looking at a PL/SQL script that goes like this -- Header var Source_Data VARCHAR2(12) DECLARE Num1 NUMBER; BEGIN Source_Data := '&1';
Re: PCTFREE and PCTUSED
CREATE TABLESPACE DATA01 DATAFILE '\data01.dbf' size 8M reuse AUTOEXTEND ON NEXT 4096M MAXSIZE 32M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO Our prod sys is using 8i on solaris, but I quickly tried that on 9i on my own pc running on XP, before I go try it anywhere else, and it worked. thx maa At 01:59 PM 11/7/2003, you wrote: On the other hand, you might have overallocated the space, which would leave plenty of blocks on the free list, thus minimizing the impact. These things are best seen on "almost full" tables with things like row chaining, row migration, waits on ITL entries and other lovely things. Looks like you've benn lucky so far. As I've told you before, having tablespaces created with "SEGMENT SPACE MANAGEMENT AUTO" option takes care of that. if your tablespace is created with a command like "CREATE TABLESPACE DATA01 DATAFILE '/data01/SID1/data01.dbf' size 8192M reuse AUTOEXTEND ON NEXT 4096M MAXSIZE 32769M EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO" then you can create tables without any additional parameters and everything will be kosher. The tablespace above requires something called "large files support" from the file system and cannot be used on FAT file systems or ISO9660-RR/Joliet file system. Practically anyhing else (UFS,VxFS,ReiserFS,Ext(2|3),XFS,JFS,OCFS,NTFS (scheduled to go away when the new virus propagataion engine enters production)). BTW, what OS and database version do you use? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Why does script have more than one slash(/) inside?
Sorry if I bore you all with my dumb questions, its just that the simplest and silliest things appear complex, if we dont know them... Often when I learn something, I go : was that all? and they go, yes, yes, that was all... Well, let me show you what I mean.. What does it mean when a .sql script has more than one slashes(/) inside it? code
Re: PCTFREE and PCTUSED
The answer is that free list handling is overhead, which means that the database is working on its own structures and not working on the user data. It's easy to conceive a busy transaction table to which records are frequently added and from which they're frequently removed. Having only one parameter would significantly increase the amount of time spent in moving blocks to and from the free list, and significantly increase the overhead. You can test it by setting up a table with PCTFREE+PCTUSED=100. In other word, the answer to your question is that two parameters are needed to reduce the overhead of the free list maintenance. By the way, I just wanted to let you know I tried that PCTFREE+PCTUSED=100 I used PCTFREE 40 and PCTUSED 60 on a specific table space, and created a table there which I updated/deleted records quite a bit, but I didnt see much of a difference. It might have been because there werent many people on the system at the time. thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PCTFREE and PCTUSED
At 06:34 PM 11/4/2003, you wrote: > So, PCTFREE and PCTUSED are kind of like boundary values, or kind-of-like > FREE SPACE IS BETWEEN PCTUSED and PCTFREE values, right? I think I should have said : > USED SPACE IS BETWEEN PCTUSED and PCTFREE values, right? maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PCTFREE and PCTUSED
At 06:34 PM 11/4/2003, you wrote: > 1) If the free space percentage in the block falls below PCTFREE, the block is > taken off the free list. Heuristically speaking, we can say that oracle does > its best to keep the block PCTFREE free. So, if pctfree is 10%, oracle does its best to keep it full 90%. Fine. > 2) When the block is taken off the free list and records are deleted, the > block is not returned to the free list until the percentage of used space > doesn't fall below PCTUSED. Again, heuristically speaking, oracle tries to > keep blocks at least PCTUSED used. So, Oracle lets the block get emptier and eptier, but does it best to stop this downfall of emptiness(!) at 40%, right? > I believe that your question was about the need for two parameters, in other > words, why do we need both of them, why don't we return block to free list > after the percentage of free space grows above PCTFREE? Yes, oh YES!!! :-) The answer is that free list handling is overhead, which means that the database is working on its own structures and not working on the user data. It's easy to conceive a busy transaction table to which records are frequently added and from which they're frequently removed. Having only one parameter would significantly increase the amount of time spent in moving blocks to and from the free list, and significantly increase the overhead. You can test it by setting up a table with PCTFREE+PCTUSED=100. In other word, the answer to your question is that two parameters are needed to reduce the overhead of the free list maintenance. GOT IT! I see, its beginning to make sense... Let me see... If the block were to become 85% full(ie 15% empty), then if Oracle were to put it on the free list again, that would cause overhead because it would fill it pretty soon, then would have to move it off the free list, therefore NOT doing work for me but itself, etc... right? So, PCTFREE and PCTUSED are kind of like boundary values, or kind-of-like FREE SPACE IS BETWEEN PCTUSED and PCTFREE values, right? Many, many thanks! maa On 2003.11.04 18:09, Maryann Atkinson wrote: Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the "free-list". My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PCTFREE and PCTUSED
> Your example block can be 60% full and not be on the free list. > Once your block reaches 90% full, it is removed from the free list. So, you are implying that it can be removed from the free list based on the PCTFREE value too, right? So far I was under the impression that it can be removed from the free list based on the PCTUSED value. > It will not be put on the free list again until used space in the block > falls below PCTUSED, which is 40% in your example. Well, why cant it be placed back on the list based on the fact that it dropped below 90%? > So, a block fills up, it is removed from the free list, then a couple of > rows are deleted and used space falls to 60%. The block is still > not on the free list, as the space used has remained above 40%. and > PCTUSED comes into play when rows are deleted from the block. If enough > data is deleted from a block to cause the block to fall below 60% used > (PCTUSED), the block goes back on the freelist for subsequent > inserts/updates. Nice explanation, thanks. It could have been setup so that if it falls below 90%, then its placed on the free list. This would not require the second paramater PCTUSED. But thats hypothetical and not the real case. Oracle will not put it back on the free list, unless its full-percentage drops below 40%. Why is that important, and why has Oracle set it up that way? Why couldnt they say something like, we'll put it on the free list again, since its now less than 90% full? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
PCTFREE and PCTUSED
Suppose I have the following settings which happen to be the defaults as well: PCTFREE 10 PCTUSED 40 I am trying to figure out what PCTUSED is really used for. My book is telling me that is used so that Oracle knows whether to keep a block in the "free-list". My point is this: If PCTFREE is 10%, that means the block can be up to 90% full, right? Well, if the block happens to be 60% full at the moment, then Oracle knows that this block is not full enough because 60 is less than 90, so it can keep it in the free list. I dont see what PCTUSED is needed, it kind of seems I can accomplish the same with just one parm, that being PCTFREE. But Oracle wouldnt have just put a parm there without any usage, so I guess there's something I dont see... Any ideas/examples? Any good reasoning anywhere? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: sometimes pressure seems to get the best of us.
I am the one who needs to apologize, dont hold it agaist me though, its just sometimes pressure seems to get the best of us. Sorry, maa --- Melanie Caffrey <[EMAIL PROTECTED]> wrote: > MaryAnn, > > I, of course, do not have a problem with you asking a question, > absolutely not. > > It was just your inference that we have you read it in the "National > Enquirer", if I'm quoting your email correctly, seemed pretty harsh. > > I thought you worked with Oracle stuff. If you do not, then I > apologize. > > And of course, you can pose any question you want to, on this list. > > No flames, I promise. > > Peace, > Melanie > > -Original Message- > Maryann Atkinson > Sent: Friday, October 17, 2003 5:04 PM > To: Multiple recipients of list ORACLE-L > > For Melanie Caffrey, > > Woww... Was your interference RUDE or what? > > > > Several people have offered you very clear and very good > suggestions. > > First of all, mel, I didnt reply to "several people", I replied to > one. > Was that one you? > > > > And really, you SHOULD be reading the Oracle manuals. > > Second of all, mel, I dont really need a lecture, thank you very > much! > Third of all missy, what I was told was to read up on this and that. > I had not been told to read "the manual", or even which one. > > > > Otherwise, why did you take a job working with Oracle technology? > > Fourth of all, mel, noone ever told you I took a job working with > Oracle Technology. > > Fifth of all, I didnt know that having a job working with Oracle > technology is a requirement for me to be in this list and ask a > question. > Is it? > > Please, very please, pretty please, flames belong back to the 90s, > I never meant any, so please dont give me any yourself. > > I just dont get that mentality, I just asked a question, a simple > little lowly question, if you dont have an answer, please dont reply > at all, I will not hold it against you, or against anyone, I couldnt > > possibly. > But please dont make me feel bad for asking it telling me to go read > on > (vaguely like that), just let it go... > > many-many thanks, > maa > > > And anything of personal nature, you can email me personally, > you know, there is no reason to involve the whole list... > > > At 04:09 PM 10/17/2003, you wrote: > >MaryAnn, > >First of all, Wow! Is your reply to this group ever rude. > > > >Several people have offered you very clear and very good > suggestions. > > > >And really, you SHOULD be reading the Oracle manuals. Otherwise, > why > >did you take a job working with Oracle technology? > > > >I don't think the problem is with the answers you've received, but > in > >your disinterest in listening to them. > > > >Melanie > > > > > >-Original Message- > >MaryAnn Atkinson > >Sent: Friday, October 17, 2003 2:43 PM > >To: Multiple recipients of list ORACLE-L > > > >I still dont get it... > >I dont know what I have done to have me confused more > >than I first asked the question... > > > > > >--- [EMAIL PROTECTED] wrote: > > > My bad. The SQL is not quite right: 'append' is a hint: > > > alter table resource nologging; > > > >dont know what nologging does. > > > > > > > insert /*+ append */ into resource > > > select * from rqmt; > > > >me no understand... me no see 1000 anywhere... > > > > > Read up on direct load insert in the concepts manual, > > > along with nologging. > > > >And one more thing... If I asked the question, thats just it, > >I asked a question. If anyone knows the answer, please offer it > here, > >but dont tell me to go read it up in the national enquirer or > >I-dont-know-where-you-mean... > > > >Folks, please, if we have something to offer, lets go ahead, > >if not, just bypass that email and read another one... > > > >thanks, > >maa > > > > > > > > > > > > > > Bypass the redo and undo - no need for commits. > > > > > > Just back it up when finished. > > > > > > Jared > > > > > > > > > > > > > > > > > > > > > > > > MaryAnn Atkinson <[EMAIL PROTECTED]> > > > Sent by: [EMAIL PROTECTED] > > > 10/16/2003 01:49 PM > > > Please respond to ORACLE-L > > > > > > > > > To: Multiple recipients of
For Melanie Caffrey
For Melanie Caffrey,
Woww... Was your interference RUDE or what?
> Several people have offered you very clear and very good suggestions.
First of all, mel, I didnt reply to "several people", I replied to one.
Was that one you?
> And really, you SHOULD be reading the Oracle manuals.
Second of all, mel, I dont really need a lecture, thank you very much!
Third of all missy, what I was told was to read up on this and that.
I had not been told to read "the manual", or even which one.
> Otherwise, why did you take a job working with Oracle technology?
Fourth of all, mel, noone ever told you I took a job working with
Oracle Technology.
Fifth of all, I didnt know that having a job working with Oracle
technology is a requirement for me to be in this list and ask a question.
Is it?
Please, very please, pretty please, flames belong back to the 90s,
I never meant any, so please dont give me any yourself.
I just dont get that mentality, I just asked a question, a simple
little lowly question, if you dont have an answer, please dont reply
at all, I will not hold it against you, or against anyone, I couldnt
possibly.
But please dont make me feel bad for asking it telling me to go read on
(vaguely like that), just let it go...
many-many thanks,
maa
And anything of personal nature, you can email me personally,
you know, there is no reason to involve the whole list...
At 04:09 PM 10/17/2003, you wrote:
MaryAnn,
First of all, Wow! Is your reply to this group ever rude.
Several people have offered you very clear and very good suggestions.
And really, you SHOULD be reading the Oracle manuals. Otherwise, why
did you take a job working with Oracle technology?
I don't think the problem is with the answers you've received, but in
your disinterest in listening to them.
Melanie
-Original Message-
MaryAnn Atkinson
Sent: Friday, October 17, 2003 2:43 PM
To: Multiple recipients of list ORACLE-L
I still dont get it...
I dont know what I have done to have me confused more
than I first asked the question...
--- [EMAIL PROTECTED] wrote:
> My bad. The SQL is not quite right: 'append' is a hint:
> alter table resource nologging;
dont know what nologging does.
> insert /*+ append */ into resource
> select * from rqmt;
me no understand... me no see 1000 anywhere...
> Read up on direct load insert in the concepts manual,
> along with nologging.
And one more thing... If I asked the question, thats just it,
I asked a question. If anyone knows the answer, please offer it here,
but dont tell me to go read it up in the national enquirer or
I-dont-know-where-you-mean...
Folks, please, if we have something to offer, lets go ahead,
if not, just bypass that email and read another one...
thanks,
maa
>
> Bypass the redo and undo - no need for commits.
>
> Just back it up when finished.
>
> Jared
>
>
>
>
>
>
>
> MaryAnn Atkinson <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 10/16/2003 01:49 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject:Re: insert and commit 1000 records at a time
>
>
>
> --- [EMAIL PROTECTED] wrote:
> > That will work, slowly.
> > You might like to try something like this
> > insert into resource
> > nologging
> > select * from rqmt
> > append;
>
>
> How's that commiting every 1000 records?
>
> > Read up on the 'append' and 'nologging' first.
>
> ???
>
>
>
>
>
> >
> >
> >
> >
> >
> >
> >
> > Maryann Atkinson <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 10/16/2003 08:54 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > cc:
> > Subject:insert and commit 1000 records at a time
> >
> >
> > I have 2 tables, Rqmt and Resource, same structure.
> >
> > I need to take all almost-one-million records from Rqmt and
> > insert them to Resource. So far this worked ok:
> >
> > DECLARE
> > RowCount NUMBER:= 0;
> >
> > BEGIN
> > SELECT Count(*)
> > INTO RowCount
> > FROM RQMT;
> >
> > IF RowCount > 0 THEN
> >
> > INSERT INTO RESOURCE
> > SELECT Resource_Id, Classification
> > FROM RQMT;
> >
> > RowCount := SQL%RowCount;
> >
> > DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
> > Rows
> > transitioned.');
> > COMMIT;
> > E
Re: insert and commit 1000 records at a time
At 04:44 PM 10/17/2003, you wrote: Got it, thanks to both of you, I really appreciate it. maa MaryAnn, To get back to the original question, use a MOD function on rownum to get your commit point. Check the docs to make sure, but try If mod(rownum, 1000) = 0 then commit; end if; Daniel Fink - Organization: Fat City Network Services, San Diego, California Precedence: bulk 1. Define a variable to count inserted rows. insert_count number := 0; 2. Increment it after inserting the row insert_count:=insert_count + 1; 3. Check if insert_count = 1000 then commit and reset counter to zero insert_count :=0; 4. At the end when no rows found, and insert_count > 0 then commit. I think you get the idea - Kirti MaryAnn Atkinson wrote: > I still dont get it... > I dont know what I have done to have me confused more > than I first asked the question... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: insert and commit 1000 records at a time
I still dont get it...
I dont know what I have done to have me confused more
than I first asked the question...
--- [EMAIL PROTECTED] wrote:
> My bad. The SQL is not quite right: 'append' is a hint:
> alter table resource nologging;
dont know what nologging does.
> insert /*+ append */ into resource
> select * from rqmt;
me no understand... me no see 1000 anywhere...
> Read up on direct load insert in the concepts manual,
> along with nologging.
And one more thing... If I asked the question, thats just it,
I asked a question. If anyone knows the answer, please offer it here,
but dont tell me to go read it up in the national enquirer or
I-dont-know-where-you-mean...
Folks, please, if we have something to offer, lets go ahead,
if not, just bypass that email and read another one...
thanks,
maa
>
> Bypass the redo and undo - no need for commits.
>
> Just back it up when finished.
>
> Jared
>
>
>
>
>
>
>
> MaryAnn Atkinson <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 10/16/2003 01:49 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject:Re: insert and commit 1000 records at a time
>
>
>
> --- [EMAIL PROTECTED] wrote:
> > That will work, slowly.
> > You might like to try something like this
> > insert into resource
> > nologging
> > select * from rqmt
> > append;
>
>
> How's that commiting every 1000 records?
>
> > Read up on the 'append' and 'nologging' first.
>
> ???
>
>
>
>
>
> >
> >
> >
> >
> >
> >
> >
> > Maryann Atkinson <[EMAIL PROTECTED]>
> > Sent by: [EMAIL PROTECTED]
> > 10/16/2003 08:54 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <[EMAIL PROTECTED]>
> > cc:
> > Subject:insert and commit 1000 records at a time
> >
> >
> > I have 2 tables, Rqmt and Resource, same structure.
> >
> > I need to take all almost-one-million records from Rqmt and
> > insert them to Resource. So far this worked ok:
> >
> > DECLARE
> > RowCount NUMBER:= 0;
> >
> > BEGIN
> > SELECT Count(*)
> > INTO RowCount
> > FROM RQMT;
> >
> > IF RowCount > 0 THEN
> >
> > INSERT INTO RESOURCE
> > SELECT Resource_Id, Classification
> > FROM RQMT;
> >
> > RowCount := SQL%RowCount;
> >
> > DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
> > Rows
> > transitioned.');
> > COMMIT;
> > ELSE
> > DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
> > transitioned.');
> > END IF;
> >
> > EXCEPTION
> > WHEN OTHERS THEN
> > Raise;
> > END;
> > /
> >
> >
> > But now I need to commit every 1000 records. Any suggestions as to
> > what would be the best way? I dont think ROWNUM would help here,
> > because it would pick the same 1000 records every time, causing
> > primary key violation...
> >
> >
> > thx
> > maa
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Maryann Atkinson
> > INET: [EMAIL PROTECTED]
> >
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting
> services
> >
> ---------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like
> subscribing).
> >
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> Author: MaryAnn Atkinson
> INET: [EMAIL PROTECTED]
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: insert and commit 1000 records at a time
--- [EMAIL PROTECTED] wrote:
> That will work, slowly.
> You might like to try something like this
> insert into resource
> nologging
> select * from rqmt
> append;
How's that commiting every 1000 records?
> Read up on the 'append' and 'nologging' first.
???
>
>
>
>
>
>
>
> Maryann Atkinson <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 10/16/2003 08:54 AM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> cc:
> Subject:insert and commit 1000 records at a time
>
>
> I have 2 tables, Rqmt and Resource, same structure.
>
> I need to take all almost-one-million records from Rqmt and
> insert them to Resource. So far this worked ok:
>
> DECLARE
> RowCount NUMBER:= 0;
>
> BEGIN
> SELECT Count(*)
> INTO RowCount
> FROM RQMT;
>
> IF RowCount > 0 THEN
>
> INSERT INTO RESOURCE
> SELECT Resource_Id, Classification
> FROM RQMT;
>
> RowCount := SQL%RowCount;
>
> DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || '
> Rows
> transitioned.');
> COMMIT;
> ELSE
> DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
> transitioned.');
> END IF;
>
> EXCEPTION
> WHEN OTHERS THEN
> Raise;
> END;
> /
>
>
> But now I need to commit every 1000 records. Any suggestions as to
> what would be the best way? I dont think ROWNUM would help here,
> because it would pick the same 1000 records every time, causing
> primary key violation...
>
>
> thx
> maa
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Maryann Atkinson
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
insert and commit 1000 records at a time
I have 2 tables, Rqmt and Resource, same structure.
I need to take all almost-one-million records from Rqmt and
insert them to Resource. So far this worked ok:
DECLARE
RowCount NUMBER:= 0;
BEGIN
SELECT Count(*)
INTO RowCount
FROM RQMT;
IF RowCount > 0 THEN
INSERT INTO RESOURCE
SELECT Resource_Id, Classification
FROM RQMT;
RowCount := SQL%RowCount;
DBMS_OUTPUT.PUT_LINE ('TABLE Resource: ' || RowCount || ' Rows
transitioned.');
COMMIT;
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE Resource is empty. No data
transitioned.');
END IF;
EXCEPTION
WHEN OTHERS THEN
Raise;
END;
/
But now I need to commit every 1000 records. Any suggestions as to
what would be the best way? I dont think ROWNUM would help here,
because it would pick the same 1000 records every time, causing
primary key violation...
thx
maa
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Maryann Atkinson
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
info on SQL Loader?
Does anyone happen to know of any links/info etc on the SQL loader starting from the basics? thanks, maa load data replace preserve blanks into table working_asc ( TRANposition(1:1)char , SPECIALTY_CODE position(3:7)char , SUFFIX position(8:8)char , DESCRIPTION position(9:28) char , EXPIRED_IND constant "N" ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: comments on EXECUTE IMMEDIATE
--- Stephane Paquette <[EMAIL PROTECTED]> wrote:
> execute immediate IS dynamic sql, it is just a new simpler synthax.
> declare
> v_count number;
> begin
> execute immediate 'select count(*) from dba_objects'
>into v_count;
> dbms_output.put_line ('Count:'||v_count);
> end;
> Count:3681
I wouldnt use an execute immediate statement on a case like that.
I would just leave it as:
SELECT COUNT(*)
FROM DBA_OBJECTS
INTO v_Count;
I have a feeling Oracle wanted to provide different kind of
functionality with the execute immediate.
I saw somewhere else a case like the following:
Sql_Stmt := 'UPDATE table
SETcol1 = :parm1
WHERE col2 = :parm2';
EXECURE IMMEDIATE Sql_Stmt USING parm1, parm2;
-
which again I dont see any advantage. I would have just
coded the UPDATE statement without any indirection.
Both above examples give me indirection, thats all,
which I dont really think I gain anything by incorporating,
actually I feel I am losing...
thx
maa
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: MaryAnn Atkinson
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
comments on 8i's autonomous PL/SQL block?
Does anyone have any examples on the autonomous block provided in 8i? I read that it can have a transaction scope thats independent of the transaction scope of the calling block, and that it can perform operations, commit and rollback independent of the transactions of the calling block, before returning to the calling block? How is that? Does it mean it can start a transaction, update something, commit the change, and then return to the calling block where a change may still be uncommitted, even though the called block issued a commit? thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
comments on EXECUTE IMMEDIATE
Does anyone have any examples on use of EXECUTE IMMEDIATE starting from small little ones? Is EXECUTE IMMEDIATE more efficient than Dynamic SQL? It certainly appears simpler to syntax, but would it be wise to use it or prefer the stability of dynamic SQL? thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
full usefullness of CURRENT OF ???
Could anyone give us an idea as to the full usefullness of CURRENT OF? Here are my two separate examples: /* 1 this one has a CURRENT OF */ DECLARE CURSOR EmpCursor IS SELECT * FROMEmp FOR UPDATE; BEGIN FOR EmpRec IN EmpCursor LOOP UPDATE EMP SETSALARY = SALARY * 1.08 WHERE CURRENT OF EmpCursor END LOOP; END; /* 2 same as above, except without the CURRENT OF */ DECLARE CURSOR EmpCursor IS SELECT * FROMEmp FOR UPDATE; BEGIN FOR EmpRec IN EmpCursor LOOP UPDATE EMP SETSALARY = SALARY * 1.08 WHERE EMP_ID = EmpRec.Emp_ID; END LOOP; END; / Is one more efficient than the other? If I could have done business without the CURRENT OF, then why did oracle made it available? thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: full usefullness of CURRENT OF ???
--- Kevin Toepke <[EMAIL PROTECTED]> wrote: > Yes, using WHERE CURRENT OF is faster. It is the same as saying > "WHERE rowid = emprec.rowid", just more readable. And accesses by > rowid are faster than index accesses. > > Also, what happens if you don't have a unique or primary constraint > on the table and whole rows can be duplicated? Then you pretty much > need to use WHERE CURRENT OF. > HTH > Kevin I see, its beginning to make sense... thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: full usefullness of CURRENT OF ???
--- Chris Stephens <[EMAIL PROTECTED]> wrote: > Also, the CURRENT OF locks the table so that no one can modify (or > even read) it while the transaction is taking place. This guarantees > nothing is changing between retrieving values from the cursor and > updating the table based on those values. OK, fair enough. > The second example has to run the update statement seperately. > CURRENT OF can go directly to the row(s) affected. CURRENT OF > still has to modify each block header in the table to lock which > is a small performance hit. So CURRENT OF has to lock, so its slower, right? Does it have any advantages after all? >To prevent that you could update by rowid How? How can I update by ROWID? I was thinking to update by PRIMARY_KEY... > and avoid the header updates. ... what "header" updates? thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
full usefullness of CURRENT OF ???
Could anyone give us an idea as to the full usefullness of CURRENT OF? Here are my two separate examples: /* 1 this one has a CURRENT OF */ DECLARE CURSOR EmpCursor IS SELECT * FROMEmp FOR UPDATE; BEGIN FOR EmpRec IN EmpCursor LOOP UPDATE EMP SETSALARY = SALARY * 1.08 WHERE CURRENT OF EmpCursor END LOOP; END; /* 2 same as above, except without the CURRENT OF */ DECLARE CURSOR EmpCursor IS SELECT * FROMEmp FOR UPDATE; BEGIN FOR EmpRec IN EmpCursor LOOP UPDATE EMP SETSALARY = SALARY * 1.08 WHERE EMP_ID = EmpRec.Emp_ID; END LOOP; END; / Is one more efficient than the other? If I could have done business without the CURRENT OF, then why did oracle made it available? thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Commit 1000 at a time, thats what I ended up doing
> IF you are looking to break up the update into more 'manageable' > pieces, here is a thought... > > UPDATE EMP2 > SET GENDER = 'F' > WHERE GENDER = ' ' > AND ROWNUM <= 100; Yes, that was exactly my objective, to update a large number of records and commit them 1000 at a time. Here is what I did: DECLARE RCBINARY_INTEGER := 1; BEGIN WHILE RC != 0 LOOP UPDATE EMP3 SETGENDER = 'F' WHERE GENDER = ' ' ANDROWNUM <= 1000; RC := SQL%ROWCOUNT; COMMIT; END LOOP; END; / If anyone has a better way please let me know, you'are all very helpful. thx maa --- Daniel Fink <[EMAIL PROTECTED]> wrote: > IF you are looking to break up the update into more 'manageable' > pieces, here is a thought... > > UPDATE EMP2 > SET GENDER = 'F' > WHERE GENDER = ' ' > AND ROWNUM <= 100; > > UPDATE EMP2 > SET GENDER = 'F' > WHERE GENDER = ' ' > AND ROWNUM <= 100; __________ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ROWNUM - Can the inner-outer idea be extended to an update?
Can the inner-outer idea be extended to an update? UPDATE EMP2 SETGENDER = 'F' WHERE GENDER = ' ' ANDROWNUM BETWEEN 10 AND 20; That does NOT work, because as we explained the returned rows are numbered starting from 1, and so rownum never really matches a 10 or 20 or anything in between. Thanks, maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ROWNUM's peculiarities...
Thanks for the explanations you've all provided. > I still believe that it's easier and faster to expand the RBS > tablespace ... Not an option. It never was. The requirements were not set by me but the agency(dont ask which one).We cant just go there and tell them, "hey you, increase RBS, NOW!" Function Based Index was not an option either, for the same reason. And dbms_transaction.use_rollback_segment, pretty fancy, I have to admit, but made many assumptions. We have a table, and thats that. We couldnt create no other objects, we just had to work with that. If I had my way I'd update them all in one shot, and commit afterwards and be done with it. But in the process, and thanks to you, I wouldnt have learned about ROWNUM's peculiarities... Many thanks, maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ROWNUM - THATS THE ONE!!! THAT DID IT!!!
THATS THE ONE!!! THAT DID IT!!! --- Freeman Robert - IL <[EMAIL PROTECTED]> wrote: > To add on to my previous comments, change your code a bit: > SQL> SELECT RN, GENDER > 2 FROM (SELECT ROWNUM RN, GENDER > 3 FROM EMP2 > 4 WHERE ROWNUM <= 20) > 5 WHERE RN > 10; The last WHERE is testing on RN, not ROWNUM. Thats the trick right there! If its true that we can only test < and <= for ROWNUM, and that sure appears to be the case, then we cannot have ROWNUM on the last WHERE clause on line 5, but its alias is fine. Robert Freeman? I had a professor Robert Freeman, he taught Comp Engr at UMC... Many thanks, maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: ROWNUM -- HOW ARE ROWS SELECTED?
Its obvious I hadnt fully understood ROWNUM yet, as you see we are learning bits and pieces as we go along. Help me out here, will you? Talk to me like I'm a 10-year old, its ok. > MaryAnn, the best way to understand rownum is to do the following: > > SQL> SELECT ROWNUM, GENDER > 2 FROM (SELECT ROWNUM, GENDER > 3 FROM EMP2 > 4 WHERE ROWNUM <= 20) > You will quickly see that, no matter how you order the result set, > the first record returned is rownum #1, second is rownum #2 etc. > The rownum value is assigned as rows are RETURNED or DISPLAYED, > not as they are selected. Fine, the rows are numbered as returned or displayed(not selected). BUT THEN HOW ARE THEY SELECTED? My emp2 table has 1+ rows in it, how are only 20 selected? Based on what criteria? How do I get 20 back? I want to understand this first. Then, once I get these 20 back, then fine, they are numbered starting from 1, that part I kind of figured it out, or so I think. The part I dont get, is HOW ARE THEY SELECTED? thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
ROWNUM is driving me nuts - queries suggested produced no results
I definitely dont fully understand ROWNUM yet, and you guys so far provided more info than a couple of books by Oracle, that I have here. For a second I thought I'm beginning to get it, but the queries suggested produced no results... SQL> SELECT ROWNUM, GENDER 2 FROM (SELECT ROWNUM, GENDER 3 FROM EMP2 4 WHERE ROWNUM <= 20) 5 WHERE ROWNUM > 10; no rows selected SQL> SELECT r, GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM <= 20) 5 WHERE ROWNUM > 10; no rows selected SQL> SELECT r "ROWNUM", GENDER 2 FROM (SELECT ROWNUM r, GENDER 3 FROM EMP2 4 WHERE ROWNUM <= 20) 5 WHERE ROWNUM > 10; no rows selected SQL> ... so, any help is appreciated - rownum is driving me nuts... thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
something appears to be funny with ROWNUM
1. this first query gave me 600 lines of output. SELECT Rownum, Gender FROM EMP2 WHERE ROWNUM <= 600; 2. this one right here told me no rows selected... SELECT Rownum, Gender FROM EMP2 WHERE ROWNUM BETWEEN 500 and 600; I dont get it... something is funny with ROWNUM... Any ideas? thx maa __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: update about 100K records
Actually, we just upgraded to 8.0 I guess this lowers my options, doesnt it? I'm 30. Not quite senile. Sorry. maa --- Ryan <[EMAIL PROTECTED]> wrote: > if your in 8i you can only use one field for a bulk collect( i think > ic ant > remember). I think they fixed that in 9i. > > i meant whatever. > > i also 'think' to do a bulk collect in 8i you cant anchor the pl/sql > table. > > has to be varchar2, number, etc... im pretty sure you can do it with > a > rowid. I dont remember either. > > Im 29. Im senile. Sorry. > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 8:04 PM > > > > --- Ryan <[EMAIL PROTECTED]> wrote: > > > uhhh... this is pretty easy. > > > > > > declare > > > type mytable is table of Whatever > > > > should I really put whatever, or table%rowtype? > > > > > > > l_table mytable; > > >l_updatevalue mytable > > > > are both variables of the same type? > > > > > > > > > > begin > > > > > > select rowid, updateValue > > > > do you mean column-to-be-updated as opposed to "updatevalue"? > > > > > > > > >bulk collect into > > >l_table; > > > from table; > > > > > > now update off the rowid for your value. > > > > > > go through like 5000 records in the pl/sql table at a time. > > > > like how? Its not an array... > > > > > > > > end; > > > > > > - Original Message - > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > > Sent: Tuesday, July 08, 2003 7:29 PM > > > > > > > > > > huh??? > > > > > > > > --- Ryan <[EMAIL PROTECTED]> wrote: > > > > > bulk collect the flag into a pl/sql table. forall with a > limit > > > clause > > > > > and then commit after hitting each limit. > > > > > this is on asktom. > > > > > > > > anything more down the earth for me please? > > > > > > > > thx > > > > maa > > > > > > > > > > > > > > > > > - Original Message - > > > > > To: "Multiple recipients of list ORACLE-L" > <[EMAIL PROTECTED]> > > > > > Sent: Tuesday, July 08, 2003 7:09 PM > > > > > > > > > > > > > > > > I have a table of about one million records. > > > > > > > > > > > > About 100,000 of them have a flag which I need it set to > 0. > > > > > > > > > > > > Because of the size of the rollback segment, I cannot > > > > > > update them all and then commit, I need to do it in > > > > > > sets of 1000 records. > > > > > > > > > > > > Do I need a cursor for this? > > > > > > > > > > > > I had something like that: > > > > > > DECLARE > > > > > > I BINARY_INTEGER; > > > > > > > > > > > > > > > > > > Cursor MyCursor IS > > > > > > SELECT * > > > > > > FROM (Table_Name) > > > > > > WHERE(Condition) > > > > > > FOR UPDATE; > > > > > > > > > > > > BEGIN > > > > > > I := 0; > > > > > > FOR MyRec IN MyCursor LOOP > > > > > > UPDATE (Table_name) > > > > > > SET delete_flag = 1 > > > > > > WHERE CURRENT OF MyCursor; > > > > > > > > > > > > I := I + 1; > > > > > > IF MOD(I, 1000) = 0 THEN > > > > > > COMMIT; > > > > > > END IF; > > > > > > > > > > > > END LOOP; > > > > > > END; > > > > > > / > > > > > > > > > > > > But "FOR UPDATE" does not really work well, and at the > 1000th > > > > > record > > > > > > when it reaches the commit, its dropping out of the loop. > > > > > > > > > > > > Can I use ROWNUM to update them in batches of 1000 per > time? &
Re: update about 100K records
--- Ryan <[EMAIL PROTECTED]> wrote: > uhhh... this is pretty easy. > > declare > type mytable is table of Whatever should I really put whatever, or table%rowtype? > l_table mytable; >l_updatevalue mytable are both variables of the same type? > > begin > > select rowid, updateValue do you mean column-to-be-updated as opposed to "updatevalue"? >bulk collect into >l_table; > from table; > > now update off the rowid for your value. > > go through like 5000 records in the pl/sql table at a time. like how? Its not an array... > > end; > > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 7:29 PM > > > > huh??? > > > > --- Ryan <[EMAIL PROTECTED]> wrote: > > > bulk collect the flag into a pl/sql table. forall with a limit > clause > > > and then commit after hitting each limit. > > > this is on asktom. > > > > anything more down the earth for me please? > > > > thx > > maa > > > > > > > > > - Original Message - > > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > > Sent: Tuesday, July 08, 2003 7:09 PM > > > > > > > > > > I have a table of about one million records. > > > > > > > > About 100,000 of them have a flag which I need it set to 0. > > > > > > > > Because of the size of the rollback segment, I cannot > > > > update them all and then commit, I need to do it in > > > > sets of 1000 records. > > > > > > > > Do I need a cursor for this? > > > > > > > > I had something like that: > > > > DECLARE > > > > I BINARY_INTEGER; > > > > > > > > > > > > Cursor MyCursor IS > > > > SELECT * > > > > FROM (Table_Name) > > > > WHERE(Condition) > > > > FOR UPDATE; > > > > > > > > BEGIN > > > > I := 0; > > > > FOR MyRec IN MyCursor LOOP > > > > UPDATE (Table_name) > > > > SET delete_flag = 1 > > > > WHERE CURRENT OF MyCursor; > > > > > > > > I := I + 1; > > > > IF MOD(I, 1000) = 0 THEN > > > > COMMIT; > > > > END IF; > > > > > > > > END LOOP; > > > > END; > > > > / > > > > > > > > But "FOR UPDATE" does not really work well, and at the 1000th > > > record > > > > when it reaches the commit, its dropping out of the loop. > > > > > > > > Can I use ROWNUM to update them in batches of 1000 per time? > > > > > > > > Thanks, > > > > maa > > > > > > > > -- > > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > > -- > > > > Author: Maryann Atkinson > > > > INET: [EMAIL PROTECTED] > > > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > > San Diego, California-- Mailing list and web hosting > > > services > > > > > > > > - > > > > To REMOVE yourself from this mailing list, send an E-Mail > message > > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') > and in > > > > the message BODY, include a line containing: UNSUB ORACLE-L > > > > (or the name of mailing list you want to be removed from). You > may > > > > also send the HELP command for other information (like > > > subscribing). > > > > > > > > > > -- > > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > > -- > > > Author: Ryan > > > INET: [EMAIL PROTECTED] > > > > > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > > > San Diego, California-- Mailing list and web hosting > services > > > > - > > > To REMOVE yourself from this mailing list, send an E-Mail message > > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and > in > >
Re: update about 100K records
Ryan's method is pretty hi-tech for me, and he is flying above the clouds and does not want to get down to poor little me, even after the inviation... :-) But this one here, --- Mark Richard <[EMAIL PROTECTED]> wrote: > Yeah, it sounds pretty high-tech. > Create a loop and place the following within it: > update table > set flag = new_value > where condition and flag <> new_value and rownum <= commit_limit; > n_rows_updated := SQL%ROWCOUNT; > commit; > > The exit condition is when n_rows_updated = 0, ie: nothing left to > update. But this one here, I dont know, how are you getting to the next set? What if one set does not have anything to update at all? Then rows_updated will be 0, and we'll exit. > > This approach will cause a few more scans than the ideal option, but > it > might get the job done. Increasing the commit_limit value as high as > possible will reduce the number of iterations. 1,000 rows sounds > very > small - even if your row length was 1,000 bytes you are only talking > about > 1MB of data. Have you tried all 100,000 rows at once? How about > 10,000 at > a time? > > > > > > > MaryAnn Atkinson > > <[EMAIL PROTECTED]To: Multiple > recipients of list ORACLE-L <[EMAIL PROTECTED]> > .com>cc: > > Sent by: Subject: Re: update > about 100K records > [EMAIL PROTECTED] > > .com > > > > > > 09/07/2003 09:29 > > Please respond to > > ORACLE-L > > > > > > > > > > huh??? > > --- Ryan <[EMAIL PROTECTED]> wrote: > > bulk collect the flag into a pl/sql table. forall with a limit > clause > > and then commit after hitting each limit. > > this is on asktom. > > anything more down the earth for me please? > > thx > maa > > > > > - Original Message - > > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > > Sent: Tuesday, July 08, 2003 7:09 PM > > > > > > > I have a table of about one million records. > > > > > > About 100,000 of them have a flag which I need it set to 0. > > > > > > Because of the size of the rollback segment, I cannot > > > update them all and then commit, I need to do it in > > > sets of 1000 records. > > > > > > Do I need a cursor for this? > > > > > > I had something like that: > > > DECLARE > > > I BINARY_INTEGER; > > > > > > > > > Cursor MyCursor IS > > > SELECT * > > > FROM (Table_Name) > > > WHERE(Condition) > > > FOR UPDATE; > > > > > > BEGIN > > > I := 0; > > > FOR MyRec IN MyCursor LOOP > > > UPDATE (Table_name) > > > SET delete_flag = 1 > > > WHERE CURRENT OF MyCursor; > > > > > > I := I + 1; > > > IF MOD(I, 1000) = 0 THEN > > > COMMIT; > > > END IF; > >
Re: update about 100K records
huh??? --- Ryan <[EMAIL PROTECTED]> wrote: > bulk collect the flag into a pl/sql table. forall with a limit clause > and then commit after hitting each limit. > this is on asktom. anything more down the earth for me please? thx maa > - Original Message - > To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> > Sent: Tuesday, July 08, 2003 7:09 PM > > > > I have a table of about one million records. > > > > About 100,000 of them have a flag which I need it set to 0. > > > > Because of the size of the rollback segment, I cannot > > update them all and then commit, I need to do it in > > sets of 1000 records. > > > > Do I need a cursor for this? > > > > I had something like that: > > DECLARE > > I BINARY_INTEGER; > > > > > > Cursor MyCursor IS > > SELECT * > > FROM (Table_Name) > > WHERE(Condition) > > FOR UPDATE; > > > > BEGIN > > I := 0; > > FOR MyRec IN MyCursor LOOP > > UPDATE (Table_name) > > SET delete_flag = 1 > > WHERE CURRENT OF MyCursor; > > > > I := I + 1; > > IF MOD(I, 1000) = 0 THEN > > COMMIT; > > END IF; > > > > END LOOP; > > END; > > / > > > > But "FOR UPDATE" does not really work well, and at the 1000th > record > > when it reaches the commit, its dropping out of the loop. > > > > Can I use ROWNUM to update them in batches of 1000 per time? > > > > Thanks, > > maa > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Maryann Atkinson > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web hosting > services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like > subscribing). > > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Ryan > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MaryAnn Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
update about 100K records
I have a table of about one million records. About 100,000 of them have a flag which I need it set to 0. Because of the size of the rollback segment, I cannot update them all and then commit, I need to do it in sets of 1000 records. Do I need a cursor for this? I had something like that: DECLARE I BINARY_INTEGER; Cursor MyCursor IS SELECT * FROM (Table_Name) WHERE(Condition) FOR UPDATE; BEGIN I := 0; FOR MyRec IN MyCursor LOOP UPDATE (Table_name) SET delete_flag = 1 WHERE CURRENT OF MyCursor; I := I + 1; IF MOD(I, 1000) = 0 THEN COMMIT; END IF; END LOOP; END; / But "FOR UPDATE" does not really work well, and at the 1000th record when it reaches the commit, its dropping out of the loop. Can I use ROWNUM to update them in batches of 1000 per time? Thanks, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
which util do I use to see oracle's execution plan?
I am so novice it'll probably take me years to learn oracle like some of you know it in here... I am just studying a book, and try to learn from some of its questions. a. Which util do I use to see oracle's execution plan for a SQL statement? Which statement would you recommend me, so I can actually try it and see results? b. If I need to tune the SGA ONLY, should I concentrate on the log buffers, the library cache or the shared pool? thx maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
where is info stored when using the ANALYZE cmd?
1. If I analyze the code using the explain plan, where is info created from this explain plan stored at? 2. If I added more buffers to the buffer-cache, where should I go check if they made any positive difference? thx, maa -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Maryann Atkinson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
