redundant index log entries

2

Hi. Yesterday, when connecting with one of our older sa clients, I noticed the log messages listed below. What usually causes these messages to be displayed? One developer speculated that indexing a foreign key might be the cause. No one here knows for sure. Thanks, Doug.

Pack 2, v.2825;PID=0xee8;THREAD=0x504;EXE=C:Program FilesSybaseSharedSybase Central 4.3win32scjview.exe;VERSION=9.0.2.2451;API=DBLIB;TIMEZONEADJUSTMENT=-

I. 04/03 15:10:46. Performance warning: Redundant index "xpkrpt_parmas_schedule" for table "rpt_params_schedule" in database "workprod"

I. 04/03 15:15:21. Performance warning: Redundant index "ssapproval_indx1" for table "sched_self_sched_approval" in database "workprod"

I. 04/03 15:15:21. Performance warning: Redundant index "rw_reports_pk_idx" for table "rw_reports" in database "workprod" I. 04/03 15:15:21. Performance warning: Redundant index "rw_columns_pk_idx" for table "rw_columns" in database "workprod"

I. 04/03 15:15:21. Performance warning: Redundant index "rw_columns_fk_idx1" for table "rw_columns" in database "workprod"

I. 04/03 15:15:21. Performance warning: Redundant index "tables_pk_idx" for table "tables" in database "workprod"

I. 04/03 15:15:21. Performance warning: Redundant index "table_columns_pk_idx" for table "table_columns" in database "workprod"

I. 04/03 15:16:09. Performance warning: Redundant index "XPKPRACTICE_GRP" for table "practice_grp" in database "workprod"

I. 04/03 15:16:09. Performance warning: Redundant index "rw_where_clause_ndx" for table "rw_where_clause" in database "workprod"

I. 04/03 15:10:46. Performance warning: Redundant index "xif272rpt_params_scchedule" for table "rpt_params_schedule" in database "workprod"

asked 05 Apr '12, 12:56

dejstone's gravatar image

dejstone
959●40●50●69
accept rate: 0%

edited 05 Apr '12, 15:44

Calvin%20Allen's gravatar image

Calvin Allen
1.5k●23●26●38
3 Answers:
active answersoldest answersnewest answerspopular answers
2

Indexes are automatically added to any primary key or foreign key. Therefore, there is no need to explicitly define one. If you do, however, you get this message.

From the docs:

Adaptive Server Anywhere automatically indexes primary key and foreign key columns. Thus, manually creating an index on a key column is not necessary and generally not recommended. If a column is only part of a key, an index may help.

Page 63

permanent link

answered 05 Apr '12, 14:48

Calvin%20Allen's gravatar image

Calvin Allen
1.5k●23●26●38
accept rate: 25%

edited 05 Apr '12, 15:55

2

FWIW, the problem has disappeared with v10. Since then, redundant indizes are still possible but will share the same physical representation and do not cause performance implications.

To cite from the v10 What's new doc...

Support for index sharing

When you create a primary key, secondary key, foreign key, or unique constraint, you now create a logical index that points to a physical index (an actual indexing structure on disk). The database server automatically determines whether a new physical index is required to satisfy the logical index. This model allows the sharing of physical indexes and prevents the creation and maintenance of duplicate physical indexes, which wastes disk space.

permanent link

answered 08 Apr '12, 06:11

Volker%20Barth's gravatar image

Volker Barth
39.3k●354●538●808
accept rate: 33%

edited 08 Apr '12, 06:12

1

There is nothing to speculate about. Some versions ago, indexes on FKs were of help in certain circumstances. In V9, they are absolutely useless, hence the message.

permanent link

answered 05 Apr '12, 13:00

Dmitri's gravatar image

Dmitri
1.6k●4●11●33
accept rate: 11%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×29
×13

question asked: 05 Apr '12, 12:56

question was seen: 3,299 times

last updated: 08 Apr '12, 06:12

Related questions

SA-9 Using Hash Table; Degrading Performance

Index Consultant Clarification

Where i can buy ASA 9 licenses?

Why include a primary key in a composite index?

How can i get the size of an index in SQL Anywhere 10 or 16?

What does a value of 5 in "SYSIDX.unique" mean?

set option min_table_size_for_histogram

indexing foreign key columns

ODBC ASA 9 64 bit driver

How does a FOREIGN KEY with MATCH UNIQUE SIMPLE treat null values?