Thread:
>>> > I've been attempting to port applications from Pervasive SQL to PG. >>> > Pervasive is interesting because it runs on top of btrieve. This allow >>> > legacy apps >>> > and SQL systems to co-exist. It's quirky and buggy, but it's better than PG >>> > because it can do the following. >>> > >>> > >>> > 1. Have more than 7 fields on a btree index >>> >>> We have never understood why someone would want an index with more than >>> seven columns. Legacy apps, Bruce. Sometimes you come across tables with ten fields in the index. I'm working on a (fairly specialised) system now where the primary key of one of the tables has twenty-four fields in it. It is a summary table, and probably not the best design, but that's the way it is, and I have to live with it. Unfortunately, because we don't have a database that can have that many fields in a key, we have to construct manual indices in code. Even Oracle (which is what we are using) only goes up to about 21 or something. However, if you are going to summarise heavily, then the number of fields in your primary key can get quite large. How difficult would it be to make the number of fields in an index unlimited (or arbitrarily large). Perhaps there could be a compile-time variable which is defaulted to 7, but which can be increased using the configure script (./configure --with-index-fields=24). I realise that there are performance issues, but that's a trade off that only the system designers can make. MikeA...
> Legacy apps, Bruce. Sometimes you come across tables with ten fields in the > index. I'm working on a (fairly specialised) system now where the primary > key of one of the tables has twenty-four fields in it. It is a summary > table, and probably not the best design, but that's the way it is, and I > have to live with it. Unfortunately, because we don't have a database that > can have that many fields in a key, we have to construct manual indices in > code. Even Oracle (which is what we are using) only goes up to about 21 or > something. However, if you are going to summarise heavily, then the number > of fields in your primary key can get quite large. > How difficult would it be to make the number of fields in an index unlimited > (or arbitrarily large). Perhaps there could be a compile-time variable > which is defaulted to 7, but which can be increased using the configure > script (./configure --with-index-fields=24). I realise that there are > performance issues, but that's a trade off that only the system designers > can make. There is a special oid8 type that holds eight oids the represent the index entries. We would have to expand that field to a larger value. I don't believe there is any real reason we have to limit it to eight. We don't do any other fancy stuff with it. The only other issue is that all indexes will have to use the maximum space. We could make it a varlena() field, and do it that way. We have not had any real demand for more than eight, but I can see your point. I will put it on the TODO list. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Not only legacy apps, but data warehousing. Frequently in a production
environment you use two sets of tables -- production and data
warehousing...One (production) with "bare bones" indicies to maximize
transaction performance, and one (a replicate in the data warehouse) that
you "index the living daylights out of" so that the non db saavy managers
who want to do ungodly joints and sorts on tables for organizational
reporting get decent performance.
just my .02
Jim
At 03:59 6/25/99 -0400, you wrote:
>>>> > I've been attempting to port applications from Pervasive SQL to PG.
>>>> > Pervasive is interesting because it runs on top of btrieve. This allow
>>>> > legacy apps
>>>> > and SQL systems to co-exist. It's quirky and buggy, but it's better
>than PG
>>>> > because it can do the following.
>>>> >
>>>> >
>>>> > 1. Have more than 7 fields on a btree index
>>>>
>>>> We have never understood why someone would want an index with more than
>>>> seven columns.
>
>Legacy apps, Bruce. Sometimes you come across tables with ten fields in the
>index. I'm working on a (fairly specialised) system now where the primary
>key of one of the tables has twenty-four fields in it. It is a summary
>table, and probably not the best design, but that's the way it is, and I
>have to live with it. Unfortunately, because we don't have a database that
>can have that many fields in a key, we have to construct manual indices in
>code. Even Oracle (which is what we are using) only goes up to about 21 or
>something. However, if you are going to summarise heavily, then the number
>of fields in your primary key can get quite large.
>How difficult would it be to make the number of fields in an index unlimited
>(or arbitrarily large). Perhaps there could be a compile-time variable
>which is defaulted to 7, but which can be increased using the configure
>script (./configure --with-index-fields=24). I realise that there are
>performance issues, but that's a trade off that only the system designers
>can make.
>
>MikeA...
>
>
,-,-. ,-,-. ,-,-. ,-,-. ,-
/ / \ \ / / \ \ / / \ \ / / \ \ / /
\ \ / / \ \ / / \ \ / / \ \ / /
`-'-' `-'-' `-'-' `-'-'
--------------------------------------------------------
FSC - Building Better Information Technology Solutions-
From the Production Floor to the Customer's Door.
--------------------------------------------------------
Jim Jennis, Technical Director, Commercial Systems
Fuentez Systems Concepts, Inc.
1 Discovery Place, Suite 2
Martinsburg, WV. 25401 USA.
Phone: +001 (304) 263-0163 ext 235
FAX: +001 (304) 263-0702
Email: jjennis@fuentez.com
jhjennis@shentel.net
---------------------------------------------------
On Fri, 25 Jun 1999, Jim Jennis wrote:
# Not only legacy apps, but data warehousing. Frequently in a production
# environment you use two sets of tables -- production and data
# warehousing...One (production) with "bare bones" indicies to maximize
# transaction performance, and one (a replicate in the data warehouse)
# that you "index the living daylights out of" so that the non db saavy
# managers who want to do ungodly joints and sorts on tables for
# organizational reporting get decent performance.
Creating lots of indices is far different from creating a single
index on a lot of fields. Data warehousing is the former. The problem is
that you can't create a single index with a large number of fields.
--
SA, beyond.com My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin@spy.net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________