Re: can derby have a sequence column?
how do you get the last serial used in a table? On 9/16/05, Jean T. Anderson [EMAIL PROTECTED] wrote: firstname lastname wrote: If I remember correctly, Oracle can define a column as being a sequence, which is a garaunteed unique integer for each record. Can derby do this? A create table example would be nice. Derby supports generated identity columns; examples are here: http://db.apache.org/derby/docs/10.1/ref/rrefsqlj37836.html regards,-jean
Re: can derby have a sequence column?
Good example but, how do insert a row to this table from JDBC ? Using ij, it's just a matter of using the DEFAULT parameter, however, the JDBC preparedstatement class has no setDefault() method. On Fri, 16 Sep 2005 20:18:04 -0700, Ali Demir [EMAIL PROTECTED] said: Yes. For example: CREATE TABLE GROUPS( ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (start with 0, increment by 1), NAME VARCHAR(25) NOT NULL, DESCRIPTION VARCHAR(255), PRIMARY KEY (ID) ) Regards, Suavi Demir At 06:52 PM 9/16/2005, you wrote: If I remember correctly, Oracle can define a column as being a sequence, which is a garaunteed unique integer for each record. Can derby do this? A create table example would be nice. -- firstname lastname [EMAIL PROTECTED] -- firstname lastname [EMAIL PROTECTED] -- http://www.fastmail.fm - Access your email from home and the web
Re: can derby have a sequence column?
The IDENTITY_VAL_LOCAL function returns the most recently assigned number: http://db.apache.org/derby/docs/10.1/ref/rrefidentityvallocal.html#rrefidentityvallocal cheers, -jean Mag Gam wrote: how do you get the last serial used in a table? On 9/16/05, *Jean T. Anderson* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: firstname lastname wrote: If I remember correctly, Oracle can define a column as being a sequence, which is a garaunteed unique integer for each record. Can derby do this? A create table example would be nice. Derby supports generated identity columns; examples are here: http://db.apache.org/derby/docs/10.1/ref/rrefsqlj37836.html http://db.apache.org/derby/docs/10.1/ref/rrefsqlj37836.html regards, -jean
Re: can derby have a sequence column?
firstname lastname wrote: Good example but, how do insert a row to this table from JDBC ? Using ij, it's just a matter of using the DEFAULT parameter, however, the JDBC preparedstatement class has no setDefault() method. The DEFAULT keyword is part of SQL, not ij. These SQL statements should all work in JDBC as a PreparedStatement INSERT INTO GROUPS(ID, NAME, DESCRIPTION) VALUES (DEFAULT, ?, ?) INSERT INTO GROUPS(NAME, DESCRIPTION) VALUES (?, ?) INSERT INTO GROUPS VALUES (DEFAULT, ?, ?) Dan. On Fri, 16 Sep 2005 20:18:04 -0700, Ali Demir [EMAIL PROTECTED] said: Yes. For example: CREATE TABLE GROUPS( ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (start with 0, increment by 1), NAME VARCHAR(25) NOT NULL, DESCRIPTION VARCHAR(255), PRIMARY KEY (ID) )
RE: can derby have a sequence column?
Each column has the option to have a identity column which automatically
increases a counter if a row is inserted.
.. here a section of a JUnit test of mine using this ..
...
Statement s = _connection.createStatement();
/*
We create a table, add a few rows, and update one.
*/
s.execute(create table +testTable1+(seq bigint not null
generated always as identity (start with 1, increment by 1), dummy
varchar(1)));
System.out.println(Created table +testTable1);
s.execute(insert into +testTable1+ (dummy) values('a'));
s.execute(delete from +testTable1+ where dummy='a');
System.out.println(Inserted to +testTable1);
String skey = stringQuery(_connection,values
IDENTITY_VAL_LOCAL());
System.out.println(Derby: created DUAL: +skey);
...
The IDENTITY_VAL_LOCAL gives you the latest number assigned
About the concept of a global seaquence generate please vote for
http://issues.apache.org/jira/browse/DERBY-103
Bernd
-Original Message-
From: Mag Gam [mailto:[EMAIL PROTECTED]
Sent: Saturday, September 17, 2005 8:38 AM
To: Derby Discussion
Subject: Re: can derby have a sequence column?
how do you get the last serial used in a table?
On 9/16/05, Jean T. Anderson [EMAIL PROTECTED] wrote:
firstname lastname wrote:
If I remember correctly, Oracle can define a column as being a
sequence, which is a garaunteed unique integer for
each record. Can
derby do this? A create table example would be nice.
Derby supports generated identity columns; examples are here:
http://db.apache.org/derby/docs/10.1/ref/rrefsqlj37836.html
regards,
-jean
--
This e-mail, including any attached files, may contain confidential and
privileged information for the sole use of the intended recipient. Any review,
use, distribution, or disclosure by others is strictly prohibited. If you are
not the intended recipient (or authorized to receive information for the
intended recipient), please contact the sender by reply e-mail and delete all
copies of this message.
Re: derby performance and 'order by'
Hi Scott,How have you set up the test? Are you using ij and displaying all of the data or using jdbc to access the data?What do you do in 0.010 seconds? Do you read all of the rows into memory, or just record the time until you get the first row? Are you measuring the time taken to return all the rows or just the first row?Another reader has already commented on the fact that the second query is doing a lot more work than the first. The second query must sort the results after filtering the data, whereas the first and third queries can simply use the indexes and filter on the fly.I'm a little suspicious of the third query returning 720,000 results in 0.010 seconds.CraigOn Sep 16, 2005, at 4:42 PM, Scott Ogden wrote:I have observed some interesting query performance behavior and am hoping someone here can explain. In my scenario, it appears that an existing index is not being used for the ‘order by’ part of the operation and as a result the performance of certain queries is suffering. Can someone explain if this is supposed to be what is happening and why? Please see below for the specific queries and their performance characteristics.Here are the particulars:- create table orders(order_id varchar(50) NOT NULLCONSTRAINT ORDERS_PK PRIMARY KEY,amount numeric(31,2),time date,inv_num varchar(50),line_num varchar(50),phone varchar(50),prod_num varchar(50)); --Load a large amount of data (720,000 records) into the ‘orders’ table --Create an index on the time column as that will be used in the ‘where’ clause. create index IX_ORDERS_TIME on orders(time); --When I run a query against this table returning top 1,000 records, this query returns very quickly, consistently less than .010 seconds. select * from orderswhere time '10/01/2002' and time '11/30/2002'order by time; --Now run a similarly query against same table, returning the top 1,000 records.--The difference is that the results are now sorted by the primary key (‘order_id’) rather than ‘time’. --This query returns slowly, approximately 15 seconds. Why?? select * from orderswhere time '10/01/2002' and time '11/30/2002'order by order_id; --Now run a third query against the same ‘orders’ table, removing the where clause--This query returns quickly, around .010 seconds. select * from ordersorder by order_id; - Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:[EMAIL PROTECTED] P.S. A good JDO? O, Gasp! smime.p7s Description: S/MIME cryptographic signature
