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.

asked 08 Apr '11, 13:50

Justin%20Willey's gravatar image

Justin Willey
6.7k108141208
accept rate: 20%


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%20Lewis's gravatar image

Tyson Lewis
2.2k1641
accept rate: 22%

edited 09 Apr '11, 06:52

Volker%20Barth's gravatar image

Volker Barth
30.6k306456663

Thanks - that's very helpful

(18 Apr '11, 15:35) Justin Willey
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

Question tags:

×242
×137
×107

question asked: 08 Apr '11, 13:50

question was seen: 1,573 times

last updated: 18 Apr '11, 15:35