Version: IDS 10

I am having some difficulties understanding few things such as:

1. I know what indexes to fragment but it's creating data skew, which
then makes it difficult to compute the size of dbspace. So I thought
instead ot fragmenting it in different dbspaces, why not fragment in
same dbspace by partion. But I am now not sure:
A. how Indexes are stored in partition.
B. Does same rule applies as it does for tablespace for eg: Max of
16.2 pages per partition.
C. Does partition have extents same as tablespaces
D. After we delete rows does partition in which we have indexes
shrink or is this like extents where space is not given back for other
indexes to use.

2. Does fragmenting by partion really help in any way ? I was thinking
it will atleast make the search of index Key Value faster as it will
eliminate rest of the partitions.

On Aug 30, 5:55 pm, mohitanch...@gmail.com wrote:
> Version: IDS 10
>
> I am having some difficulties understanding few things such as:
>
> 1. I know what indexes to fragment but it's creating data skew, which
> then makes it difficult to compute the size of dbspace. So I thought
> instead ot fragmenting it in different dbspaces, why not fragment in
> same dbspace by partion. But I am now not sure:
> A. how Indexes are stored in partition.
> B. Does same rule applies as it does for tablespace for eg: Max of
> 16.2 pages per partition.
> C. Does partition have extents same as tablespaces
> D. After we delete rows does partition in which we have indexes
> shrink or is this like extents where space is not given back for other
> indexes to use.
>
> 2. Does fragmenting by partion really help in any way ? I was thinking
> it will atleast make the search of index Key Value faster as it will
> eliminate rest of the partitions.

If somebody has time to reply to my posts. Or please point me to
appropriate resource where I can do the reading myself. I am not
seeing enough information about the information that I am seeking.

mohitanchlia@gmail.com wrote:
> Version: IDS 10
>
> I am having some difficulties understanding few things such as:
>
> 1. I know what indexes to fragment but it's creating data skew, which
> then makes it difficult to compute the size of dbspace. So I thought
> instead ot fragmenting it in different dbspaces, why not fragment in
> same dbspace by partion. But I am now not sure:
> A. how Indexes are stored in partition.

There can be multiple types of pages within a partition. The index page
is such a page type.

> B. Does same rule applies as it does for tablespace for eg: Max of
> 16.2 pages per partition.

There can be 0xffffff pages in a partition.

> C. Does partition have extents same as tablespaces
yes
> D. After we delete rows does partition in which we have indexes
> shrink or is this like extents where space is not given back for other
> indexes to use.

no

>
> 2. Does fragmenting by partion really help in any way ? I was thinking
> it will atleast make the search of index Key Value faster as it will
> eliminate rest of the partitions.

If you are going to attach and detach fragments - yes.

If you have deep indexes and can use fragmentation, then you can achieve
some performance benefits.

>