次要属性中的 ASE 15.x 索引统计信息
如果我们创建这样的索引：
create nonclustered index idx_person on person(gender, name)
在 ASE 12.5 中，将为主要属性（性别）创建统计信息（更准确地说是直方图），但不会为次要属性名称执行任何操作。
因此，优化器在进行如下选择时可以选择最差的查询计划：
select * from person
where gender = 'M'
and name = 'John Doe'
它可能会选择最差的查询计划，因为它将给出列名称的默认选择性值。这样，表扫描可能比选择索引更好。
！
我想知道的是，在 ASE 15.x 中，当使用多个列创建索引时，是否会对所有索引列运行统计信息？
或者，如果我们必须使用以下命令来更新索引统计信息：
update statistics person(name)
update index statistics person index_person
提前致谢！ :)
If we create an index like this:
create nonclustered index idx_person on person(gender, name)
in ASE 12.5, there'll be created statistics (more precisely an histogram) for the major atribute (gender) but nothing is done for the minor atribute name.
Because of that, the optimizer can select a worst query plan when making a select like this:
select * from person
where gender = 'M'
and name = 'John Doe'
It might choose a worst query plan because it'll give the default selectivity values for the column name. With that, a table scan can be better than choosing the index.
!
What I want to know is if in ASE 15.x, when a index is created with more than one column, the statistics are run for all index columns?
Or if we've to use the following commands to update the index statistics:
update statistics person(name)
update index statistics person index_person
Thanks in advance! :)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
默认情况下，您不会获得第二列及后续列的统计信息，我想是因为 Sybase 必须进行更多处理并使用 tempdb 来编译它们。因此，您需要决定哪些索引需要其所有列都包含统计信息。
如果您使用的是 v15 并且优化级别设置为“mix”或“dss”，您可能会遇到一个问题，即您没有保留所有索引列的统计信息，因为优化器无法轻松进行合并和哈希直接连接，无需所有索引列统计信息。在 v12.5 上，如果您打开了 MERGE JOIN，则同样适用。
我认为，仅仅因为第二列和后续列上没有直方图，您可能会遇到比错误计划更多的此类问题，尽管我想这取决于情况。
您可以使用 DATACHANGE 函数来找出哪些表的更改程度超过您给出的某个百分比，以便您可以编写一个定期（每小时一次/几个小时/天/批处理开始/结束）的 shell 脚本（或其他脚本）批次/等）检查数据库并更新受影响表的统计信息。 （实际上我不太确定这是在 12.5 上 - 很可能只是 v15。）
You don't get the stats on 2nd and subsequent columns by default, I suppose because Sybase has to do more processing and use the tempdb to compile them. Therefore you're expected to decide which of your indexes need all their columns with stats.
If you're on v15 and you have optimisation level set to "mix" or "dss" you're likely to have a problem where you don't keep stats on all index columns, because the optimiser can't easily get merge and hash joins right without all index column stats. On v12.5 the same applies if you have MERGE JOIN turned on.
I'd think it might be likely you'd have more problems of that kind than wrong plans just because of no histogram on 2nd and subsequent columns, though I guess it depends.
You can use the DATACHANGE function to find out which tables have changed by more than some percentage you give, so that you could write a shell script (or whatever) that periodically (once an hour/several hours/day/start of batch/end of batch/etc) checks the database and updates stats for affected tables. (Actually I'm not quite sure that's on 12.5 - it might well be v15 only.)