Professional Web Applications Themes

who can explain this? - IBM DB2

ENVIRONMENT: DB2 OS/390 V6 Peoplesoft v8 I have a strange situation: I have a table called TABLEA and it has indexes Ind1 Ind2 Ind3 Now when I do this on the table Select * from table where NAME='XYZ' It is using an index IND1 and it is very slow. But if I do this Select * from table where substr(name,1,8)='XYZ' now it using IND2 and it is very fast. Now my question is why is this behaviour? And what caused substr to use another index and efficiently. Thanks...

Sponsored Links
  1. #1

    Default who can explain this?

    ENVIRONMENT:

    DB2 OS/390 V6 Peoplesoft v8
    I have a strange situation:

    I have a table called TABLEA and it has indexes
    Ind1
    Ind2
    Ind3

    Now when I do this on the table

    Select * from table where NAME='XYZ'

    It is using an index IND1 and it is very slow.

    But if I do this

    Select * from table where substr(name,1,8)='XYZ' now it using
    IND2 and it is very fast.

    Now my question is why is this behaviour? And what caused substr to
    use another index and efficiently.

    Thanks
    Sponsored Links
    radhika Guest

  2. #2

    Default Re: who can explain this?

    "radhika" <com> wrote in message
    news:google.com... 

    Please show the DDL of the indexes.

    But often times, unexpected behavior of indexes is caused by disorganization
    of the table and indexes. To resolve this, you should reorg the table and
    indexes, then run runstats utility with full statistics captured, and then
    rebind the plan (if static).


    Mark Guest

  3. #3

    Default Re: who can explain this?

    Can you post the CREATE statements for the tables and indexes? You might
    be able to do this with db2look from a client workstation on Unix,
    Windows or Linux?

    radhika wrote:
     

    Blair Guest

  4. #4

    Default Re: who can explain this?

    O.K Here is the statements.

    These are the CREATE INDEX statements.

    Create unique index ind1 on table
    (col1 , col2, col3, col4, col5)

    create index ind2 on table (col1, col2, col3, col5, col6)

    create index ind3(col1, col3)

    create index ind4(col4)

    Now without the substr it is using ind3 and it is very bad.
    With the substr it is using ind4 and it is very good.

    why is that?

    This is on os/390 db2 v6

    Thanks




    Blair Adamache <> wrote in message news:<bmperm$km4$torolab.ibm.com>... [/ref]
    radhika Guest

  5. #5

    Default Re: who can explain this?

    > > radhika wrote: [/ref][/ref]
    "radhika" <com> wrote in message
    news:google.com... 
    1. Which column is NAME (col1, col2, col3, col4, col 5, or col6). I think
    you made a mistake because ind3 and ind4 have completely different columns
    in their indexes as described above.
    2. I need the exact SQL statement and working storage definitions of any
    host variables in that SQL statement. Host variables start with a colon.
    3. You can also use NAME LIKE 'XYZ%' which may be better performing than
    substr. In any case it is no worse and more widely accepted method.
    4. It would be better if you disclosed which indexes are unique and which is
    the clustered index. If no index is explicitly defined as clustered, then
    the first index created is the clustered index. BTW, the employee table
    should probably be clustered on last name or department (i.e., in a
    multi-column index these columns need to be the first column in the
    clustered index)..


    Mark Guest

  6. #6

    Default Re: who can explain this?

    maybe there is a variable on mvs/os390/zos that is similar to the luw db2
    udb DB2_LIKE_VARCHAR?

    PM


    PM-pm3iinc-nospam Guest

Similar Threads

  1. can someone explain this?
    By cheemsta in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: November 2nd, 02:13 PM
  2. Please Explain
    By Waris Mirza in forum PERL Beginners
    Replies: 0
    Last Post: November 5th, 04:59 AM
  3. Explain...
    By Sharif Tanvir Karim in forum PHP Development
    Replies: 2
    Last Post: October 20th, 10:52 AM
  4. Replies: 4
    Last Post: September 15th, 06:11 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