Re: Counter
Date: Wed, 6 Feb 2008 07:53:23 -0800 (PST)
Message-ID: <72630d5c-fdc2-4f5b-8a95-b6749197f36e@d21g2000prf.googlegroups.com>
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 Received on Wed Feb 06 2008 - 09:53:23 CST