Viewing or Modifying Table Index Properties

Display and edit the properties for the selected table index.

Prerequisites
Database Version Table Index Privileges
SAP Sybase IQ 15.3 and 15.4

View table index fragmentation property page – Requires EXECUTE permission on the sp_iqindexfragmentation system procedure to display content of page.

View any other table index property page – None required.

Modify any index property – Requires one of:
  • DBA authority.
  • RESOURCE authority with REFERENCE permission on the underlying table of the index.
  • You own the underlying table of the index.
SAP Sybase IQ 16.0
View table index fragmentation property page –
  • If the system procedure security model* of the selected database is set to Definer, you require EXECUTE permission on the sp_iqindexfragmentation system procedure to display content of page.
  • If the system procedure security model* of the selected database is set to Invoker, you require the MANAGE ANY DBSPACE system privilege to display content of page.
*The system procedure security model of the selected database appears on the General page of database properties.

View any other table index property page – None required.

Modify a table index comment – Requires one of:
  • CREATE ANY INDEX system privilege.
  • ALTER ANY INDEX system privilege.
  • CREATE ANY OBJECT system privilege.
  • ALTER ANY OBJECT system privilege.
  • COMMENT ANY OBJECT system privilege.
  • You own the underlying table of the index.
Note: ALTER permission on the table is not required to modify the comment only.
Modify any other table index property –
  • Requires one of:
    • ALTER ANY OBJECT system privilege.
    • ALTER ANY INDEX system privilege,
    • REFERENCE permission on the underlying table of the index.
    • You own the underlying table of the index.
Task
  1. In the Perspective Resources view, select the resource and select Resource > Administration Console.
  2. In the left pane, select IQ Servers > Schema Objects > Tables.
  3. Select Tables or Global Temporary Tables.
  4. Select a table from the right pane and either:
    • Click the arrow to the right of the name and select Properties, or
    • From the Administration Console menu bar, select Resource > Properties.
    The Table Properties view appears.
  5. In the left pane, select Indexes.
    Note: Indexes does not appear for Proxy tables as they are not supported.
  6. Click Edit.
    The Indexes Properties view appears.
  7. View or modify the properties.
    • Any modifications to the properties are saved when either Apply or OK is clicked
    • If you do not have privileges to modify properties, Sybase Control Center displays the properties view in read-only mode.
    Area Description
    General

    Name – Name of the index.

    Unique – (Read-only) Whether values in the index must be unique. The unique value is set when you create a new index.

    Table – (Read-only) Name and owner of the table with which the index is associated.

    Dbspace – (Read-only) Database file, or dbspace, where the index is located.

    Index Type – (Read-only) (IQ store (main store) tables only) Index type (High Non-Group, for example).

    Clustered – (Read-only) (IQ system store (catalog store) tables only) Whether this is a clustered index. Clustered indexes store the table rows in approximately the same order as they appear in the corresponding index. Using a clustered index can lead to performance benefits by reducing the number of times each page needs to be read into memory. Only one index on a table can be a clustered index.

    Format – (Read-only) (IQ system store tables only) The store type of the index.

    Comment – User-defined text description of the index. For example, the index's purpose in the system.
    Columns

    (Read-only)

    Name – Name of the columns indexed.

    Sequence – Columns are sorted by their sequence, which is a unique number starting at 0. The order of the numbers determines the relative position of the columns in the index.

    Order – Either ascending or descending order. Set the order when you create a new index.

    Data Type – Data type of the columns indexed.
    Placement

    (Read-only)

    (IQ store tables only)

    Dbspace – Dbspace occupied by the object.

    Size – Size of the object.

    % File – Percentage of the file used by this object.

    First Block – First block used by this object.

    Last Block – Last block used by this object.
    Fragmentation

    (Read-only)

    (IQ store tables only)

    Fill Percent – Range of how full pages are, for example, between 11 and 20% full. All percentages are truncated to the nearest percentage point.

    BTree Pages – Number of B-tree pages in the index with a particular fill percent.

    GArray Pages – Number of GArray pages in the index with a particular fill percent.

    Bitmap Pages – Number of bitmap pages in the index with a particular fill percent.
  8. Click OK.
    Index property modifications are not yet saved to the database.
  9. Do one of the following:
    • Click Apply to upload any property changes to the database and remain in the properties view.
    • Click OK to upload any property changes to the database and exit the properties view.
    • Click Cancel to cancel ALL unuploaded property changes and exit the properties view.
Parent topic: Table Indexes
Related tasks
Authenticating a Login Account for a Managed Resource