By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,264 Members | 1,718 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,264 IT Pros & Developers. It's quick & easy.

Question on select count()

P: n/a
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 ?
Jul 19 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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" <jp******@hotmail.com> a écrit dans le message de
news:b3**************************@posting.google.c om...
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 ?

Jul 19 '05 #2

P: n/a
jp******@hotmail.com (JP Belanger) wrote in message news:<b3**************************@posting.google. 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)...
Jul 19 '05 #3

P: n/a
af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. 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.
Jul 19 '05 #4

P: n/a
jp******@hotmail.com (JP Belanger) wrote in message news:<b3**************************@posting.google. com>...
af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. 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?
Jul 19 '05 #5

P: n/a

"JP Belanger" <jp******@hotmail.com> wrote in message
news:b3**************************@posting.google.c om...
af******@yahoo.com (Alex Filonov) wrote in message

news:<33**************************@posting.google. 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
Jul 19 '05 #6

P: n/a
jp******@hotmail.com (JP Belanger) wrote in message news:<b3**************************@posting.google. com>...
af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. 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
Jul 19 '05 #7

P: n/a
af******@yahoo.com (Alex Filonov) wrote in message news:<33**************************@posting.google. 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.
Jul 19 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.