分区表和索引

bisal

Yong Huang 发表于 2015-2-28 00:43
> 实际使用id的场景大多是若干表之间关联的情况，那这种(id,date)的复合主键是否有问题呢？

A query can ...

但这种场景下除了where a.id = b.id，一般是不是还需要带有date分区键作为条件？否则就是跨分区检索？

bisal

Yong Huang 发表于 2015-2-28 00:43
> 实际使用id的场景大多是若干表之间关联的情况，那这种(id,date)的复合主键是否有问题呢？

A query can ...

我看Oracle官方文档中说：
“Index Performance Considerations
As you decide which type of index to use, consider that indexes rank in performance in the following order:
    Unique and local index
    Unique and global index
    All other non-unique indexes (normal, bitmap, function-based) and local index.”
优先选择的还是唯一索引，如果需求上必须有全局非唯一索引，是否效率上不会比不分区好呢？

Yong Huang

> 这种场景下除了where a.id = b.id，一般是不是还需要带有date分区键作为条件？否则就是跨分区检索？

Correct. Only referencing the starting columns will use the index, but for partition elimination to work, that's not enough. You have to reference all columns in the partitioning key.

> 优先选择的还是唯一索引，如果需求上必须有全局非唯一索引，是否效率上不会比不分区好呢？

I'm sure the best answer is from your own test. Theoretically, a unique local index is as good as it can be. But it has its own problem. Imagine the overhead of checking for uniqueness every time a new row is inserted. The check is quick because it uses the unique index itself. But nonetheless, it's overhead (especially when the index is composite) compared to not checking at all in case of a non-unique index.

bisal

Yong Huang 发表于 2015-3-2 23:37
> 这种场景下除了where a.id = b.id，一般是不是还需要带有date分区键作为条件？否则就是跨分区检索？

C ...

谢谢您。