SQL Anywhere database - Unable to defragment index

Author

Unable to defragment index

2005-06-20, 7:23 am

ASA 9.0.2.3131 (DB file created with ASA 9.0.2.3044)

I have a select that should lists indexes which should be defragmented:

select il.tablename, il.levels, id.density, il.indextype, il.indexname
from sa_index_levels() il join sa_index_density() id on il.tablename =
id.tablename join sysobjects so on id.tablename = so.name
where il.levels > 1
and so.type ='U'
and so.uid = 1
order by density asc;

The first 15 rows are like this:
tablename level density indextype indexname
-----------------------------------------------------------
mail 3 0,338942 FKEY update_log_to_mail
mail 3 0,338942 FKEY zip_range_to_mail1
mail 2 0,338942 FKEY zip_range_to_mail
mail 3 0,338942 NUI rep_index
mail 3 0,338942 FKEY recevier_to_mail
mail 3 0,338942 NUI org_bundle
mail 3 0,338942 FKEY mail_type_to_mail
mail 3 0,338942 FKEY mail_to_link
mail 3 0,338942 FKEY mail_to_chipcard_typ
e
mail 3 0,338942 PKEY mail
mail 3 0,338942 FKEY induction_method_to_
mail
mail 3 0,338942 UI i_mail_item_id
mail 3 0,338942 FKEY fk11_mail

Seeing the density is below 65%, I thought an index reorganize
would be in appropriate, so I've tried these four approaches:

REORGANIZE TABLE mail PRIMARY KEY;
REORGANIZE TABLE mail INDEX i_mail_item_id;
REORGANIZE TABLE mail FOREIGN KEY zip_range_to_mail1;
DROP INDEX mail.i_mail_item_id;
CREATE UNIQUE INDEX i_mail_item_id ON mail (item_id DESC);

But none of them change the density listed in above select.

Any ideas why?
TIA!

regards/Peter Simonsen

BTW
Table mail is not fragmented (segs_per_row=1.003).

Nick Elson

2005-06-21, 9:23 am

You should probably contact technical support since you seem
to have a reproducible case. I have been unable to reproduce the
problem behaviour here.

The only thing I could think of as being a possible contributing factor
is possible due to your regional settings (the ',' character being your
decimal point ) and the fact that all densities shown are the same
value.

If you can reproduce this using dbisql and a US-EN setup then
I would think this is a concern that someone should look into.
If the problem is not due to the back end operation but the conversion
of the density values for display, then we should look into your
client tool and the regional settings you have.

<Peter Simonsen> wrote in message news:42b68692@forums
-1-dub...
> ASA 9.0.2.3131 (DB file created with ASA 9.0.2.3044)
>
> I have a select that should lists indexes which should be defragmented:
>
> select il.tablename, il.levels, id.density, il.indextype, il.indexname
> from sa_index_levels() il join sa_index_density() id on il.tablename =
> id.tablename join sysobjects so on id.tablename = so.name
> where il.levels > 1
> and so.type ='U'
> and so.uid = 1
> order by density asc;
>
> The first 15 rows are like this:
> tablename level density indextype indexname
> -----------------------------------------------------------
> mail 3 0,338942 FKEY update_log_to_mail
> mail 3 0,338942 FKEY zip_range_to_mail1
> mail 2 0,338942 FKEY zip_range_to_mail
> mail 3 0,338942 NUI rep_index
> mail 3 0,338942 FKEY recevier_to_mail
> mail 3 0,338942 NUI org_bundle
> mail 3 0,338942 FKEY mail_type_to_mail
> mail 3 0,338942 FKEY mail_to_link
> mail 3 0,338942 FKEY mail_to_chipcard_typ
e
> mail 3 0,338942 PKEY mail
> mail 3 0,338942 FKEY induction_method_to_
mail
> mail 3 0,338942 UI i_mail_item_id
> mail 3 0,338942 FKEY fk11_mail
>
>
> Seeing the density is below 65%, I thought an index reorganize
> would be in appropriate, so I've tried these four approaches:
>
> REORGANIZE TABLE mail PRIMARY KEY;
> REORGANIZE TABLE mail INDEX i_mail_item_id;
> REORGANIZE TABLE mail FOREIGN KEY zip_range_to_mail1;
> DROP INDEX mail.i_mail_item_id;
> CREATE UNIQUE INDEX i_mail_item_id ON mail (item_id DESC);
>
> But none of them change the density listed in above select.
>
> Any ideas why?
> TIA!
>
> regards/Peter Simonsen
>
> BTW
> Table mail is not fragmented (segs_per_row=1.003).
>
>

Glenn Paulley

2005-06-21, 11:23 am

<Peter Simonsen> wrote in news:42b68692@forums
-1-dub:

> select il.tablename, il.levels, id.density, il.indextype,
> il.indexname
> from sa_index_levels() il join sa_index_density() id on
> il.tablename =
> id.tablename join sysobjects so on id.tablename = so.name
> where il.levels > 1
> and so.type ='U'
> and so.uid = 1
> order by density asc;
>

This query is incorrect - you need to join sa_index_levels() and
sa_index_density on both tablename and index name; otherwise you'll get
lots of duplicates, with duplicate density values as in your results
below (which may be why you don't believe the REORGANIZE statement is
working correctly):

The first 15 rows are like this:
tablename level density indextype indexname
-----------------------------------------------------------
mail 3 0,338942 FKEY update_log_to_mail
mail 3 0,338942 FKEY zip_range_to_mail1
mail 2 0,338942 FKEY zip_range_to_mail
mail 3 0,338942 NUI rep_index
mail 3 0,338942 FKEY recevier_to_mail
mail 3 0,338942 NUI org_bundle
mail 3 0,338942 FKEY mail_type_to_mail
mail 3 0,338942 FKEY mail_to_link
mail 3 0,338942 FKEY mail_to_chipcard_typ
e
mail 3 0,338942 PKEY mail
mail 3 0,338942 FKEY induction_method_to_
mail
mail 3 0,338942 UI i_mail_item_id
mail 3 0,338942 FKEY fk11_mail

I would only bother with REORGANIZE on indexes of significant size, as a
rule of thumb more than 500 leaf pages (also reported by the
sa_index_density() procedure). Low numbers of pages (remember than any
index that requires more than one page of entries will have two levels)
render the density value somewhat meaningless, because a significant
proportion of the pages will be empty due, simply, to a lack of entries
because the table is so small.

--
Glenn Paulley
Research and Development Manager, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

2005-06-21, 8:23 pm

Ok, so it's not just me.
I'll double check everything and submit it if there's no change.

Thanks.

Regards/Peter Simonsen

"Nick Elson" < no_spam_nicelson@syb
ase.com> wrote in message
news:42b81454$1@foru
ms-2-dub...
> You should probably contact technical support since you seem
> to have a reproducible case. I have been unable to reproduce the
> problem behaviour here.
>
> The only thing I could think of as being a possible contributing factor
> is possible due to your regional settings (the ',' character being your
> decimal point ) and the fact that all densities shown are the same
> value.
>
> If you can reproduce this using dbisql and a US-EN setup then
> I would think this is a concern that someone should look into.
> If the problem is not due to the back end operation but the conversion
> of the density values for display, then we should look into your
> client tool and the regional settings you have.
>
> <Peter Simonsen> wrote in message news:42b68692@forums
-1-dub...
>
>

2005-06-21, 8:23 pm

"Glenn Paulley" <paulley@ianywhere.com> wrote in message
news:Xns967C6F0C4347
Bpaulleyianywherecom
@10.22.241.106...
> <Peter Simonsen> wrote in news:42b68692@forums
-1-dub:
> This query is incorrect - you need to join sa_index_levels() and
> sa_index_density on both tablename and index name; otherwise you'll get
> lots of duplicates, with duplicate density values as in your results
> below (which may be why you don't believe the REORGANIZE statement is
> working correctly):

That could be it. I'll test it again.

> I would only bother with REORGANIZE on indexes of significant size, as a
> rule of thumb more than 500 leaf pages (also reported by the
> sa_index_density() procedure). Low numbers of pages (remember than any
> index that requires more than one page of entries will have two levels)
> render the density value somewhat meaningless, because a significant
> proportion of the pages will be empty due, simply, to a lack of entries
> because the table is so small.

Thanks for that tip.

regards/Peter Simonsen

2005-06-24, 3:23 am

> If you can reproduce this using dbisql and a US-EN setup then
> I would think this is a concern that someone should look into.

It turns out that Glenn Paully was right. It was my select that was
at fault (missing join). After fixing that, it worked fine.

regards/Peter Simonsen

Sponsored Links