Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Can the autoindex feature be disabled?

  • 1.  Can the autoindex feature be disabled?

    Posted 26 days ago
    Hi,

    I would like to know if there is a way to prevent autoindex from being constructed when executing a query.

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------


  • 2.  RE: Can the autoindex feature be disabled?

    Posted 26 days ago
    Hi SangGyu,

    As far as I know, we can not disable that because it was created automatically by optimizer.

    If the query plan includes an autoindex path to a large table, you can generally improve performance by adding an index on that column. If you perform a query regularly, you can save time by creating a permanent index.

    If you perform the query occasionally, you can reasonably let the database server build and discard an index.

    And may I know the version you are running or you are asking this question in general?



    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 3.  RE: Can the autoindex feature be disabled?

    Posted 26 days ago
    Edited by SangGyu Jeong 26 days ago
    Hi Gaurav, 

    My customer is using version 12.10.FC6WE. They want to know how to avoid automatic index creation when certain queries are executed.

    I know that the autoindex feature cannot be controlled, but I just wanted to know if there are any related parameters.

    They'd better tune the query (or use directives) or create an index..

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 4.  RE: Can the autoindex feature be disabled?

    Posted 26 days ago
    Hi SangGyu,

    The current algorithm checks for temp dbspaces first. If none of the temp dbspaces provides enough space to hold that table the database dbspace or even the
    rootdbs is chosen as last resort


    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 5.  RE: Can the autoindex feature be disabled?

    Posted 25 days ago
    I'm not aware of any, maybe ask support.   I know this can be avoided by using a directive (FULL).

    ------------------------------
    ~~~~~~~~~
    Eric Rowell
    ------------------------------