Re: Counter

On Feb 6, 9:53 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> Comments embedded.
> On Feb 6, 9:04 am, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
>
> > Hi All,
>
> > I'm looking around right now to see if this is possible, but I thought
> > I'd ask here too.
>
> > Can you have a record counter in an insert statement?
>
> What is the purpose of this?
>
> > Something like
> > this:
>
> > INSERT INTO table_a (
> > SELECT flda, fldb, fldc, i++, fldd FROM tableb);
>
> > Basically there is one column that is a record counter. And since
> > this is not a loop, I'm not sure how it is done. Maybe a sub-select
> > with a count(*)??
>
> Oh, sure you can try that but how will you guarantee the accuracy of
> that count? You cannot, as you can't capture uncommitted records in
> that count process, and you surely are going to have more than one
> session simultaneously adding records to this table:
>
> Session #1 --
>
> SQL> @counter_ex_1
> SQL> set linesize 132
> SQL>
> SQL> insert into count_test
> 2 values
> 3 (1,2,3,4,(select count(*) from count_test), 5, 6, 7, 8);
>
> 1 row created.
>
> SQL>
> SQL> select count(*)
> 2 from count_test;
>
> COUNT(*)
> ----------
> 1
>
> SQL>
> SQL> pause;
>
> Pausing to allow Session #2 to add a record --
>
> SQL> @counter_ex_2
> SQL> set linesize 132
> SQL>
> SQL> insert into count_test
> 2 values
> 3 (1,2,3,4,(select count(*) from count_test), 5, 6, 7, 8);
>
> 1 row created.
>
> SQL>
> SQL> select count(*)
> 2 from count_test;
>
> COUNT(*)
> ----------
> 1
>
> SQL>
> SQL> pause;
>
> Going back to session #1 let's commit the changes and see how many
> rows are in the table as far as session #1 is concerned --
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select count(*)
> 2 from count_test;
>
> COUNT(*)
> ----------
> 1
>
> SQL>
> SQL> select *
> 2 from count_test;
>
> A B C D E
> F G H I
> ---------- ---------- ---------- ---------- ---------- ----------
> ---------- ---------- ----------
> 1 2 3 4 0
> 5 6 7 8
>
> SQL>
>
> Now let's commit in session #2 and perform the same queries --
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select count(*)
> 2 from count_test;
>
> COUNT(*)
> ----------
> 2
>
> SQL>
> SQL> select *
> 2 from count_test;
>
> A B C D E
> F G H I
> ---------- ---------- ---------- ---------- ---------- ----------
> ---------- ---------- ----------
> 1 2 3 4 0
> 5 6 7 8
> 1 2 3 4 0
> 5 6 7 8
>
> SQL>
>
> Note that column E in both records contains a value of 0, the known
> row count at the time each insert was made. You cannot, therefore,
> guarantee the validity of the results as multiple rows can end up with
> the same count(*) which invalidates the data.
>
> > Thanks a bunch!
>
> What is the purpose of this futile exercise? You might think of
> creating a sequence to track the row numbers:
>
> Again, from session #1 --
>
> SQL> @counter_ex_setup_b
> SQL> create table count_test(
> 2 a number,
> 3 b number,
> 4 c number,
> 5 d number,
> 6 e number,
> 7 f number,
> 8 g number,
> 9 h number,
> 10 i number);
>
> Table created.
>
> SQL>
> SQL> create sequence count_test_seq
> 2 start with 1 increment by 1 nocycle nomaxvalue nocache;
>
> Sequence created.
>
> SQL>
> SQL> @counter_ex_1b
> SQL> set linesize 132
> SQL>
> SQL> insert into count_test
> 2 select
> 3 1,2,3,4,count_test_seq.nextval, 5, 6, 7, 8
> 4 from dual;
>
> 1 row created.
>
> SQL>
> SQL> select count(*)
> 2 from count_test;
>
> COUNT(*)
> ----------
> 1
>
> SQL>
> SQL> pause;
>
> Pausing for session #2 to do some work --
>
> SQL> @counter_ex_2b
> SQL> set linesize 132
> SQL>
> SQL> insert into count_test
> 2 select
> 3 1,2,3,4,count_test_seq.nextval, 5, 6, 7, 8
> 4 from dual;
>
> 1 row created.
>
> SQL>
> SQL> select count(*)
> 2 from count_test;
>
> COUNT(*)
> ----------
> 1
>
> SQL>
> SQL> pause;
>
> Now, committing in session #1 --
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select count(*)
> 2 from count_test;
>
> COUNT(*)
> ----------
> 1
>
> SQL>
> SQL> select *
> 2 from count_test;
>
> A B C D E
> F G H I
> ---------- ---------- ---------- ---------- ---------- ----------
> ---------- ---------- ----------
> 1 2 3 4 1
> 5 6 7 8
>
> SQL>
>
> Notice the row counter (column E) has the correct value. Now, to
> session #2 --
>
> SQL>
> SQL> commit;
>
> Commit complete.
>
> SQL>
> SQL> select count(*)
> 2 from count_test;
>
> COUNT(*)
> ----------
> 2
>
> SQL>
> SQL> select *
> 2 from count_test;
>
> A B C D E
> F G H I
> ---------- ---------- ---------- ---------- ---------- ----------
> ---------- ---------- ----------
> 1 2 3 4 2
> 5 6 7 8
> 1 2 3 4 1
> 5 6 7 8
>
> SQL>
>
> Again notice that column E has the correct count, and that column E is
> unique for both records. Notice also that the sequence was created
> nocache, to help reduce the number of 'holes' it might produce.
>
> Using a sequence is probably the only reliable method of implementing
> a row (record) counter. Of course this also involves changing your
> insert statement from a values list to an insert ... select statement.
>
> David Fitzjarrell

Hi David,

Thanks for the suggestion. Actually only 1 session will be running this process. But, I'll try your suggestion and see what the results are...... Received on Wed Feb 06 2008 - 10:06:31 CST