--- In firebird-support@yahoogroups.com, Aage Johansen <aagjohan@...> >

> Maybe two tables will be better?
> create table DAILYMAIN (
> ID integer not null primary key,
> SYMB varchar(20) not null,
> YMD date not null
> );
> create table DAILYBLOB (
> ID integer not null primary key,
> TB blob ...,
> MB blob ...,
> DB blob ...
> );
> Individual indexes on SYMB and YMD (whatever proves beneficial).

1) I think I need an index on YMD definitely for fast search of the latest date.
2) I will never query for a multiple symbols but always for a single symbol only.

Considering 1) and 2), what indexes do I need to create? Please show me appropriate sql statements.

> These tables are in a one-to-one relationship (on their IDs, use a
> single generator).
>

I have never used a generator. After reading LANGREF.PDF,
CREATE GENERATOR ID_GEN;
COMMIT;
SET TERM !! ;
CREATE TRIGGER CREATE_ID FOR DAILYMAIN
BEFORE INSERT POSITION 0
AS BEGIN
NEW.ID = GEN_ID(ID_GEN, 1);
END
SET TERM ; !!

1) Is this right?
2) Create generator for DAILYMAIN only?
3) How do I use this generator?

> Keeping blobs outside of the "main" table might make for more
> efficient search and retrieval (if you don't always need the blobs). YMMV.
>

1) Can you tell me more how efficient by keeping blobs outside of the main table?
2) As I said in my first post, there are three main retrieval operations:
- retrieval of the latest date for a specific symbol
Select statement for this was like (in terms of a single table DAILYBLOB):
SELECT FIRST 1 YMD FROM DAILYBLOB WHERE SYMB = 'S1234' ORDER BY YMD DESC;
- retrieval of either TB or MB for a specific symbol and a specific date
- retrieval of DBs for a specific symbol and a specific date
Select statements for these were like (in terms of a single table DAILYBLOB):
SELECT TB FROM DAILYBLOB WHERE SYMB='S1234' AND YMD='2011-07-25';
SELECT MB FROM DAILYBLOB WHERE SYMB='S1234' AND YMD='2011-07-25';
SELECT DB FROM DAILYBLOB WHERE SYMB='S1234' AND (YMD>='2007-01-01' AND
YMD<='2011-12-24');

How can I change these statements if I use two tables(DAILYMAIN and DAILYBLOB) following your suggestion?

3) insert was like:
INSERT INTO DAILYBLOB VALUES('S0001', '2011-12-01', <TB>, <MB>, <DB>);
...
INSERT INTO DAILYBLOB VALUES('S3000', '2011-12-01', <TB>, <MB>, <DB>);
...
...
INSERT INTO DAILYBLOB VALUES('S0001', '2011-12-23', <TB>, <MB>, <DB>);
...
INSERT INTO DAILYBLOB VALUES('S3000', '2011-12-23', <TB>, <MB>, <DB>);

I am not sure how to change these insert statements using two table architecture.

4) BTW, what's YMMV?

thx again
HR