SAP SQL Anywhere

Question

I'm adding a maintenance tool to our products to allow users to detect and fix excessive table and index fragmentation using sa_table_fragmentation() and REORGANIZE TABLE.

While I can construct my own table list and then call sa_table_fragmentation() for each table, it would seem much more efficient to start off by calling sa_table_fragmentation() with no params to get all tables and then allow users to decide which ones need attention.

However because sa_table_fragmentation() does not include the owner name in its result set I call REORGANIZE TABLE without either hoping that there's only table of that name, or doing some further look-ups to see if there are multiple tables, who the owners are and then processing all the tables (since I don't know which of the tables sa_table_fragmentation() is referring to).

in this example, two tables called tag, belonging to different owners, but no indication of which is which:

TableName,rows,row_segments,segs_per_row
'tag',231,231,1.0
'tag',0,0,0.0

If sa_table_fragmentation() included the owner name (or the TableID), there would be no problem.

sa_index_density() includes TableID, so in that case there is no problem identifying which table is meant.

Accepted Answer

3

With the procedure as-is, you could gather the results needed with a lateral table:

:::SQL
SELECT sysuser.user_name AS owner, stf.*
FROM sysuser, systab,
   LATERAL (sa_table_fragmentation(systab.table_name,sysuser.user_name)) stf
WHERE sysuser.user_id = systab.creator AND systab.table_type = 1;

permanent link

answered 08 Apr '11, 15:35

Tyson Lewis
2.2k●16●41
accept rate: 22%

edited 09 Apr '11, 06:52

Volker Barth
40.2k●361●550●822

Thanks - that's very helpful

(18 Apr '11, 15:35) Justin Willey

flat view

SAP SQL Anywhere

Add owner name or TableID to sa_table_fragmentation() results set

Follow this question

Related questions