indexes of a Sybase table

Hello,

Which is the best way to prove that the index of a table is up to date?

I'm trying with this query:

select substring(name,1,30),substring(moddate,1,30) from sysobjects o, sysstatistics s where o.id *= s.id and name like '%table_name%'

And it gives more than one row with different moddates, which I don't understand. In the beggining I though it was because the table was partitioned, but it isn't.

Why does this happen and how do you check the indexes of the tables?

Thanks in advance.
lulonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ak50304Commented:
The query which you have written, returns more than one row because sysstatistics contains rows for each index and a row for each column for which statistics is created. Typically if you create a clustered/non-clustered index on a single column, it'll have 3 rows with different formatid.

If you create an index on more than 2 or more coulmns, there would be more than three rows for each table.

Now, when you execute update statis <tablename> for the first time, the moddate all the rows would be affected.

When you execute update index statistics, mod date of all the rows would be affected.

But, when you exec update statistics again, after you have once updated index statistics, only the moddate of Leading column would be updated.

so, you can use below query( source: - http://dbaspot.com/forums/sybase/344092-finding-histogram-steps.html) to find out the last time when statistics of an index was updated -

select TableName=object_name(ss.id), RowCnt=st.rowcnt,
ColName=col_name (ss.id, convert (smallint, left (ss.colidarray, 2))),
RequestedSteps=convert(int,ss.c5),
ActualSteps=convert(int,ss.c4),
ApproxDistincts=convert(int,
round(1/convert(double precision,ss.c15),0)),
Uniqueness=str(1.0/(convert(double precision,ss.c15)*st.rowcnt),7,5),
RangeDensity=str(round(convert(double precision,ss.c2),10),12,10),
TotalDensity=str(round(convert(double precision,ss.c3),10),12,10),
UniqueRangeValues=str(round(convert(double precision,ss.c14),10),12,10),
UniqueTotalValues=str(round(convert(double precision,ss.c15),10),12,10),
UpdStatsDate=convert(varchar(20),moddate,100),
DaysAgo=datediff(dd,moddate,getdate())
from sysstatistics ss, systabstats st
where ss.id > 100 and st.id > 100
and ss.id=st.id
and ss.formatid=100
and st.indid in (0,1)
and ss.c4 is not null
order by TableName, ColName

HTH,
Ankur

0
Joe WoodhousePrincipal ConsultantCommented:
I'm not sure of what you're asking here. Sybase indexes are always up to date. If you do any inserts/updates/deletes to a table, all indexes on that table are updated as part of the main transaction.

The moddate column in sysstatistics has nothing to do with how "up to date" an index is. It shows the last time update statistics was run. Note that statistics are not kept per index but per column... so actually there is also no such thing as "index statistics". These statistics help describe the density and the distribution of the data - ie cardinality plus what proportion of the rows are clustered around certain values.

Suppose you have a table with five columns: A, B, C, D & E, with an index IDX1 on columns A & B.

- If you run the regular "update statistics" command, you get stats refreshed on the leading index column only, ie. column A.
- If you run "update index statistics" you get stats refreshed on all index columns, ie. columns A & B.
- If you run "update statistics [table].[column], you get stats on that column only.
- If you run "update all statistics" you get stats for all columns in the table.

You see multiple rows for the same table because of the above - ie. stats might have been run on different columns and/or indexes at different times.

Also even if an index is dropped, its stats don't go away (unless separately deleted using the "delete statistics" command), so you could be seeing the results of some old housekeeping on indexes that don't even exist anymore.

A smarter thing to do would be to ask what the most recent (ie. max(moddate)) date is for each table, as you can guarantee no form of update statistics have been run since then. But that won't tell you if there are old statistics laying around.

Of course, this only makes sense if this was the question you wanted to ask - when was update statistics last run?

If instead you really do want the question you asked in your original post - how to prove the index is up to date - then the answer is "by definition the index is always up to date". :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.