Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2016
    Posts
    2

    Smile Unanswered: informix explain plan

    Hi All,

    I am an informix newbie. I am using server studio for connecting to IDS 11. I have a query that searches by name. it is a wild card search. for example, if I search on 'John doe' it will result in 'John Doe*'. we have about 120K rows in the table. currently the search query runs quite slowly and I need to fine tune the same. my question is when the search is based on wild card, will the index be applied for the search? how can I run the explain plan to decipher this information?

    regards,
    Prasad

  2. #2
    Join Date
    May 2008
    Posts
    19
    I do not know whether you can switch it on via Serverstudio, but you can try one of the following:
    1st:
    - identify the session in "onstat -g ses"
    - run "onmode -Y <sessionid> 1 <filename>" for that session to capture the plan (look in the working directory for it)

    Alternatively 2nd:
    create a stored procedure like:

    create procedure "<username>".sysdbopen()
    set explain file to <file>;
    set explain on;
    end procedure;

    So, every time the user logs in, a new plan will be created.

    Normally, if the column is indexed and the statistics a up to date, he should use the index, but only if the wildcard is not at the beginning of the string.

  3. #3
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Prasad,


    the answer to your question is:
    if the wildcard is not starting your search expression (i;e where column matches "string*"), the index will be taken
    if the wildcard starts your search expression (i.e where column matches "*string"), the index will NOT be taken

  4. #4
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Prasad,


    the answer to your question is:
    if the wildcard is not starting your search expression (i;e where column matches "string*"), the index will be taken
    if the wildcard starts your search expression (i.e where column matches "*string"), the index will NOT be taken

    Watch your statistics, if they are outdated, they may havethe optimizer to take a wrong path.
    use onmode -Y session number and check the file name on the server. this may vary between 11.50 and 11.70

Posting Permissions

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