Question on select count()

[JP Belanger]

I have a question on select count(), which may betray my lack of
database knowledge. Here goes.

I have java code driving a transaction which goes like this:

select count(*) from table

(Java code in the same transaction)
if count < 50 then ...

insert into table values(blahblah )
commit;

it seems that if many of these transactions are running concurrently,
count could exceed 50 unless oracle locks the table for inserts or
deletes.

Am I getting this wrong ? Are there better solutions ?

[Syltrem]

Re: Question on select count()

After exactly 51 inserts into a freshly created table, count will exceed 50.
:-)

I don`t see what you`re getting at.

Notes: Committing after each insert will slow down your application.
If you don`t commit, others won`t see the rows inserted. So
another process' "count" will not be affected, until you commit those
inserts.
You shouldn`t do a count iteratively in a loop, until 50 rows
have been inserted. Rather, count in your java code to keep trace of how
many you`ve inserted.

What are you trying to achieve?

Syltrem


"JP Belanger" <jpbelang@hotma il.coma écrit dans le message de
news:b399d6ce.0 408131002.26efb f79@posting.goo gle.com...

I have a question on select count(), which may betray my lack of
database knowledge. Here goes.
>
I have java code driving a transaction which goes like this:
>
select count(*) from table
>
(Java code in the same transaction)
if count < 50 then ...
>
insert into table values(blahblah )
commit;
>
it seems that if many of these transactions are running concurrently,
count could exceed 50 unless oracle locks the table for inserts or
deletes.
>
Am I getting this wrong ? Are there better solutions ?

[Alex Filonov]

Re: Question on select count()

jpbelang@hotmai l.com (JP Belanger) wrote in message news:<b399d6ce. 0408131002.26ef bf79@posting.go ogle.com>...

I have a question on select count(), which may betray my lack of
database knowledge. Here goes.
>
I have java code driving a transaction which goes like this:
>
select count(*) from table
>
(Java code in the same transaction)
if count < 50 then ...
>
insert into table values(blahblah )
commit;
>
it seems that if many of these transactions are running concurrently,
count could exceed 50 unless oracle locks the table for inserts or
deletes.
>
Am I getting this wrong ? Are there better solutions ?

If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...

[JP Belanger]

Re: Question on select count()

afilonov@yahoo. com (Alex Filonov) wrote in message news:<336da121. 0408161150.2c0d 78d9@posting.go ogle.com>...

>
>
If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...

Are you saying I was unclear ? :)

I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.

The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.

[Alex Filonov]

Re: Question on select count()

jpbelang@hotmai l.com (JP Belanger) wrote in message news:<b399d6ce. 0408170627.1284 9129@posting.go ogle.com>...

afilonov@yahoo. com (Alex Filonov) wrote in message news:<336da121. 0408161150.2c0d 78d9@posting.go ogle.com>...

If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...

>
Are you saying I was unclear ? :)
>
I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.
>

You mean, each session is registering itself in some table? Maybe you
can use v$session virtual table?
Another way is to create a table with fixed number of rows. Would
create a lot of headache: you need to update a row when session
is registering itself and update again when it's de-registering
(and you need a monitor which would detect dead session and
de-register them). Another problem with this design is that session needs
to connect to database to try to register itself...

The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.

Maybe not. Depends on what kind of operations do you do against the
database.
BTW, do you really need to restrict the number of sessions?
And, is it possible to use restrictions built-in into Oracle, like
restrictions on number of sessions, processes or transactions?

[Jim Kennedy]

Re: Question on select count()


"JP Belanger" <jpbelang@hotma il.comwrote in message
news:b399d6ce.0 408170627.12849 129@posting.goo gle.com...

afilonov@yahoo. com (Alex Filonov) wrote in message

news:<336da121. 0408161150.2c0d 78d9@posting.go ogle.com>...

If you are trying to restrict number of rows to <= 50, there is

something

wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...

>
Are you saying I was unclear ? :)
>
I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.
>
The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.

Why not just set the parameter in the init.ora file that restricts the
number of logons.
Very simple and reliable.
Jim

[sybrandb@yahoo.com]

Re: Question on select count()

jpbelang@hotmai l.com (JP Belanger) wrote in message news:<b399d6ce. 0408170627.1284 9129@posting.go ogle.com>...

afilonov@yahoo. com (Alex Filonov) wrote in message news:<336da121. 0408161150.2c0d 78d9@posting.go ogle.com>...

If you are trying to restrict number of rows to <= 50, there is something
wrong with database design. It'll help if you post what you are trying
to achieve (in simple words)...

>
Are you saying I was unclear ? :)
>
I keep track of sessions in a database. Exceeding a fixed number of
sessions should not be allowed.
>
The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.

If you want a maximum number of session, just set the processes
parameter in init.ora appropiately.
If you want a maximum number of sessions per user, look up the CREATE
PROFILE command in the Oracle documentation.
You are now trying to hack yourself out, where the database can indeed
help you.

Sybrand Bakker
Senior Oracle DBA

[JP Belanger]

Re: Question on select count()

afilonov@yahoo. com (Alex Filonov) wrote in message news:<336da121. 0408171322.5165 68f2@posting.go ogle.com>...

You mean, each session is registering itself in some table? Maybe you
can use v$session virtual table?

I need to read up :)

Another way is to create a table with fixed number of rows. Would
create a lot of headache: you need to update a row when session
is registering itself and update again when it's de-registering
(and you need a monitor which would detect dead session and
de-register them). Another problem with this design is that session needs
to connect to database to try to register itself...

Essentially had the same thoughts you had about this one.

>

The reason I do not keep the cound in the Java application was that I
wanted to keep the application stateless, to allow for simple load
balancing. I thought the database could help me, but at the same
time, I think helping me, the database would sacrifice a lot of
performance.

>
Maybe not. Depends on what kind of operations do you do against the
database.
BTW, do you really need to restrict the number of sessions?

Hehe. It was required for our application's licensing. Of course,
when you then ask what we should do with excess session, the answer,
as it very often is, was "let them go through anyways".

And, is it possible to use restrictions built-in into Oracle, like
restrictions on number of sessions, processes or transactions?

We may need to support many more sessions (100 000's). So I'll have
to read more on this.

Thanks for everybody's help.