如何绕过太多的索引？ [英] How to get around too many indexes??

问题描述

这是我第一次记得在

表上有太多索引，但它已经发生了。


我有en employees表并存储在许多地方，桌子上，执行某些操作的员工的身份证明。是的，我知道，这可能是在审计中，但是不是。例如，谁打印了销售订单，谁处理它

等存储在销售订单表中。


好​​吧，我已经用完了索引在尝试设置

关系时的员工表。现在我已经把索引从像雇员这样的东西上取下来了，因为它们并不是真正需要的，并没有设置一些

关系，我很高兴他们不是需要 - 但它让我感到烦恼

我已经违背了自己关于人际关系的规则。


有没有其他人遇到过这个问题，你是如何处理它的？ br />

Jeff Pritchard

________________

Asken Research Pty.Ltd。

访问数据库开发人员 http://www.asken.com.au

This is the first time that I remember ever having too many indexes on a
table, but it has happened.

I have en employees table and store in many places, on tables, the id of the
employee that performed some action. Yes, I know, that could be in an audit
trail but it isn''t. For example, who printed a sales order, who processed it
etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to set up
relationships. For now I have take indexes off of things like employee
postcode fields as they are not really needed, and have not set some
relationships where I am happy that they are not needed - but it bugs me
that I have broken my own rule about relationships.

Has anyone else come across this and how have you handled it?

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

推荐答案

消除低优先级后索引，下一步是子类表 -

本质上是一对一的相关表，它是一个扩展表。

原始表 - 从而允许更多索引可用 - 需要在查询中使用联接的成本

。

-


Ken Snell

< MS ACCESS MVP>


" Jeff" ; < je ************ @ asken.com.auwrote in message

news：44 **************** ******* @ per-qv1-newsreader-01.iinet.net.au ...

After eliminating "low-priority" indices, next step is a subclass table --
essentially, a one-to-one related table that is an "extension" of the
original table -- thus allowing more indices to be available -- at the cost
of having to use a join in queries.

--

Ken Snell
<MS ACCESS MVP>

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

这是我第一次记得在

表上有太多索引，但它已经发生了。


我有很多地方的员工表和商店，在桌子上，id为

执行某些操作的员工。是的，我知道，这可能是在一个

的审计线索中，但事实并非如此。例如，谁打印了销售订单，谁b $ b b处理它等等存储在销售订单表中。


好​​吧，我已经用完了索引在试图设置

关系时员工表。现在我已经把索引从像雇员这样的东西上取下来了，因为它们并不是真正需要的，并没有设置一些

关系，我很高兴他们不是需要 - 但它让我感到烦恼

我已经违背了自己关于人际关系的规则。


有没有其他人遇到过这个问题，你是如何处理它的？ br />

Jeff Pritchard

________________

Asken Research Pty.Ltd。

访问数据库开发人员 http://www.asken.com.au

This is the first time that I remember ever having too many indexes on a
table, but it has happened.

I have en employees table and store in many places, on tables, the id of
the employee that performed some action. Yes, I know, that could be in an
audit trail but it isn''t. For example, who printed a sales order, who
processed it etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to set
up relationships. For now I have take indexes off of things like employee
postcode fields as they are not really needed, and have not set some
relationships where I am happy that they are not needed - but it bugs me
that I have broken my own rule about relationships.

Has anyone else come across this and how have you handled it?

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

嗨肯


考虑到这一点，它是一个真正的选择。只是不是真的不喜欢

创建额外的记录并维护它。我知道只需要
包含员工ID。但仍然是一个合理的选择。


杰夫


" Ken Snell" < kt *********** @ ncoomcastt.renaetlwrote in message

news：mP ******************* ***********@comcast.com。 ..

Hi Ken

Considered this and it is a real option. Just don''t really like having to
create the extra record and maintain it. I know it would only have to
include the employee id. But still a reasonable option.

Jeff

"Ken Snell" <kt***********@ncoomcastt.renaetlwrote in message
news:mP******************************@comcast.com. ..

消除低优先级后索引，下一步是子类表 -

本质上是一对一的相关表，它是一个扩展表。

原始表 - 从而允许更多索引可用 -

成本必须在查询中使用联接。

-


Ken Snell

< MS ACCESS MVP>


" Jeff" ; < je ************ @ asken.com.auwrote in message

news：44 **************** ******* @ per-qv1-newsreader-01.iinet.net.au ...

After eliminating "low-priority" indices, next step is a subclass table --
essentially, a one-to-one related table that is an "extension" of the
original table -- thus allowing more indices to be available -- at the
cost of having to use a join in queries.

--

Ken Snell
<MS ACCESS MVP>

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

>这是我第一次记得在桌子上有太多的索引，但它已经发生了。

我有很多地方的员工桌子和商店，在桌子上，工作人员的身份是
做了一些动作。是的，我知道，这可能是在审计线索中，但事实并非如此。例如，谁打印销售订单，谁处理它等等都存储在销售订单表中。

好吧，我试图设置<员工表上的索引用完了关系密切。现在我已经把索引从员工
邮政编码字段中取出，因为它们并不是真正需要的，并且没有设置一些我很高兴他们不需要的关系 - 但它让我感到烦恼我打破了自己关于人际关系的规则。

有没有其他人遇到过这个，你是如何处理的？

杰夫普里查德
________________
Asken Research Pty.Ltd。
访问数据库开发人员
http：/ /www.asken.com.au

>This is the first time that I remember ever having too many indexes on a
table, but it has happened.

I have en employees table and store in many places, on tables, the id of
the employee that performed some action. Yes, I know, that could be in an
audit trail but it isn''t. For example, who printed a sales order, who
processed it etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to set
up relationships. For now I have take indexes off of things like employee
postcode fields as they are not really needed, and have not set some
relationships where I am happy that they are not needed - but it bugs me
that I have broken my own rule about relationships.

Has anyone else come across this and how have you handled it?

Jeff Pritchard
________________
Asken Research Pty. Ltd.
Access Database Developers
http://www.asken.com.au

杰夫，这个这是一个真正的问题，而且我可能比其他任何人更频繁地点击访问限制

。


大概你已经以编程方式检查了索引集合

查看是否存在重复索引。在表格中找到2或3个
相同的索引是很常见的。例如，如果您创建一个名为

ClientID的外键，Access会自动索引该字段，因为它是后缀ID。

然后您在该字段上手动创建索引，因此你现在有2.然后你

在这个表和主表之间创建一个关系，使用参考

完整性当然，Access创建另一个隐藏索引来管理

约束。你可能不会在表格设计中使用查找向导，但是它还会创建索引。所以第一步通常是消除多个多余的b $ b冗余索引，包括任何隐藏的索引。


一旦你完成了这个，并删除索引你没有它可以生活，它有时候可以归结为手动管理一些关系，就像我们过去在旧的dBase III中所做的那样。


-

Allen Browne - 微软MVP。西澳大利亚州珀斯。

访问用户提示 - http：// allenbrowne.com/tips.html

回复群组，而不是mvps dot org的allenbrowne。


" Jeff" < je ************ @ asken.com.auwrote in message

news：44 **************** ******* @ per-qv1-newsreader-01.iinet.net.au ...

Jeff, this is a real issue, and probably the Access limit I hit more often
than any other.

Presumably you have already programmatically examined the Indexes collection
to see if there are duplicated indexes. It is quite common to find 2 or 3
identical indexes in a table. For example, if you create a foreign key named
ClientID, Access automatically indexes the field because it is suffixed ID.
Then you manually create an index on the field, so you now have 2. Then you
create a relation between this table and the primary one, with Referential
Integrity of course, and Access creates another hidden index to manage the
constraint. You probably don''t use the Lookup Wizard in table design, but it
also creates indexes. So the first step is usually eliminating the multiple
redundant indexes, including any hidden ones.

Once you''ve done that, and dropped the indexes you can live without, it
sometimes does come down to managing some relationships manually like we
used to do in the old dBase III days.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

Hi Ken


考虑到这一点，这是一个真正的选择。只是不是真的不喜欢

创建额外的记录并维护它。我知道只需要
包含员工ID。但仍然是一个合理的选择。


杰夫


" Ken Snell" < kt *********** @ ncoomcastt.renaetlwrote in message

news：mP ******************* ***********@comcast.com。 ..

Hi Ken

Considered this and it is a real option. Just don''t really like having to
create the extra record and maintain it. I know it would only have to
include the employee id. But still a reasonable option.

Jeff

"Ken Snell" <kt***********@ncoomcastt.renaetlwrote in message
news:mP******************************@comcast.com. ..

>消除低优先级后索引，下一步是一个子类
表 - 基本上是一对一的相关表，它是原始表的扩展名 - 因此允许更多索引可用 -




Ken Snell
< MS ACCESS MVP>

杰夫 < je ************ @ asken.com.auwrote in message
新闻：44 ******************** *** @ per-qv1-newsreader-01.iinet.net.au ...

>After eliminating "low-priority" indices, next step is a subclass
table -- essentially, a one-to-one related table that is an "extension"
of the original table -- thus allowing more indices to be available -- at
the cost of having to use a join in queries.

--

Ken Snell
<MS ACCESS MVP>

"Jeff" <je************@asken.com.auwrote in message
news:44***********************@per-qv1-newsreader-01.iinet.net.au...

>>这是我第一次记得有过
表上的索引太多了，但它已经发生了。

我有很多地方的员工桌子和商店，在桌子上，工作的员工的ID是
行动。是的，我知道，这可能是审计线索，但事实并非如此。例如，谁打印销售订单，谁处理它等等都存储在销售订单表中。

好吧，我试图让我觉得我已经打破了自己关于人际关系的规则。

>>This is the first time that I remember ever having too many indexes on a
table, but it has happened.

I have en employees table and store in many places, on tables, the id of
the employee that performed some action. Yes, I know, that could be in
an audit trail but it isn''t. For example, who printed a sales order, who
processed it etc is stored on the sales orders table.

Well, I have run out of indexes on the employees table when trying to
set up relationships. For now I have take indexes off of things like
employee postcode fields as they are not really needed, and have not set
some relationships where I am happy that they are not needed - but it
bugs me that I have broken my own rule about relationships.

这篇关于如何绕过太多的索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！