Professional Web Applications Themes

Correct use of an automatically created index on a primary ke - Informix

You've got the syntax wrong on the optimizer directive. Try: {+INDEX(TableName ColumnName)} Also, if you update statistics on the table per the performance guide recommendations, the optimizer would use the index if appropriate. Regards, Bill > -----Original Message----- > From: Andrew Hardy [SMTP:Andrew.Hardymarconi.com] > Sent: Tuesday, October 14, 2003 5:55 AM > To: [email]informix-listiiug.org[/email] > Subject: Correct use of an automatically created index on a primary > key > > Please excuse me for anything stupid . I am very new to Informix and to > SQL at this level. > > I need to know how to correctly use ...

  1. #1

    Default RE: Correct use of an automatically created index on a primary ke


    You've got the syntax wrong on the optimizer directive. Try:

    {+INDEX(TableName ColumnName)}

    Also, if you update statistics on the table per the performance guide
    recommendations, the optimizer would use the index if appropriate.

    Regards,
    Bill
    > -----Original Message-----
    > From: Andrew Hardy [SMTP:Andrew.Hardymarconi.com]
    > Sent: Tuesday, October 14, 2003 5:55 AM
    > To: [email]informix-listiiug.org[/email]
    > Subject: Correct use of an automatically created index on a primary
    > key
    >
    > Please excuse me for anything stupid . I am very new to Informix and to
    > SQL at this level.
    >
    > I need to know how to correctly use an automatically created index on a
    > primary key, or if I need to remove it and replace it with one of my own,
    > and how I may do that.
    >
    > Andrew H.
    >
    >
    >
    > Detail
    > ================================================== =========
    >
    > I have the following table, which potentially contains 10,000 records, and
    > I think I need an index to do my inserts and my retrievals, because I need
    > to quickly retrive the next row in order of the primary key field (oid).
    >
    > I create the table like this
    >
    > CREATE TABLE user_file_read( oid AsnObjectId, userId integer, reader
    > VARCHAR(255), fileName VARCHAR(255), readStatus integer, PRIMARY KEY (oid)
    > ) LOCK MODE ROW;
    >
    > AsnObjectId is a user defined type for which the server knows about
    > functions for comparison etc and for which the client registers. This
    > appears to work successfully and was written for us by Informix.
    >
    > Then I try to create the index like this
    >
    > CREATE INDEX oid_index ON user_file_read (oid);
    >
    > I get error -350 'Index already exists on column'. So I find out what
    > the
    > index is, it's ' 2828_63' (with a leading space) and appears not to change
    > after table creation, then I do my inserts and selects like these
    > examples.
    > Clearly in the long term the index name ought not to be hard coded.
    >
    > SELECT {+INDEX(' 2828_63')} * FROM user_file_read_table order by oid
    >
    > And for the insert, this is an example
    >
    > INSERT {+INDEX(' 2828_63 )} INTO user_file_read_table ( oid, userId,
    > reader, fileName, readStatus) VALUES ( '99.99.99.99.99.99..99.99', 13,
    > 'hardya', 'testDir1/testDir2/testFile', 1)
    >
    > My inserts are successful.
    >
    > My select is successful and correct and I can do next on that to travers
    > in
    > oid order, but the execution of the select with 10,000 records is
    > currently
    > taking about 30 seconds. A straight forward unordered next is instant. I
    > thought that using the index would imnprovce performance. I must be doing
    > something worng, but I get no clues, because I get no errors.
    >
    >
    >
    > sending to informix-list
    sending to informix-list
    Bill Dare Guest

  2. #2

    Default Re: Correct use of an automatically created index on a primary ke


    Andrew Hardy wrote:
    > Does any one know how to prevent implicit creation of an index when setting
    > the primary key for a table.
    That's how a primary key is enforced. As a matter of interest, how else
    would you like the primary key enforced, if not with an index?

    Cheers,
    --
    Mark.

    +----------------------------------------------------------+-----------+
    | Mark D. Stock mailto:mdstockMydasSolutions.com |//////// /|
    | Mydas Solutions Ltd [url]http://MydasSolutions.com[/url] |///// / //|
    | +-----------------------------------+//// / ///|
    | |We value your comments, which have |/// / ////|
    | |been recorded and automatically |// / /////|
    | |emailed back to us for our records.|/ ////////|
    +----------------------+-----------------------------------+-----------+

    sending to informix-list
    Mark D. Stock Guest

Similar Threads

  1. converting unique index into primary key
    By Ed L. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 23rd, 03:42 AM
  2. default index created for primary key
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 7
    Last Post: December 22nd, 07:30 PM
  3. Replies: 5
    Last Post: October 14th, 09:47 PM
  4. Replies: 1
    Last Post: October 14th, 01:24 PM
  5. Changing Primary Index Question
    By Bill Hamilton in forum Informix
    Replies: 8
    Last Post: September 4th, 05:29 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139