Re: Explain Plan for queries
On Thu, 16 Oct 2003 12:31:11 -0400, ovkrishna
<member44474@dbforums.com> wrote:
>
>Hi,
>
>
>
>I have a bunch of issues, though mostly related to obtaining the query
>plan in informix. I am working with version 9.30.
>
>I am a newbie to Informix online dynamic server. So my questions might
>be too basic. Please be patient with me.
>
Glad to have you aboard.
>I need to obtain the query plan for a set of queries and tune them. I am
>using DBACCESS to run the queries. Going through the documents I came to
>know that I can get the query plan by executing SET EXPLAIN ON and then
>executing my queries. Now I have the following questions
>
>1. Is there a way in INFORMIX as in ORACLE to obtain the query plan,
> without actually executing the queries? If yes how?
>
In dbaccess, try....
set explain on;select {+avoid_execute()} * from some_table;
After the query returns, press ! and input "vi sqexplain.out". (I'm
presuming UNIX, not sure about Windows).
>2. As I mentioned I need to obtain query plans for multiple
> queries. Is there a way to run a whole file having all these
> queries using DBACCESS?
>
Dbaccess is required to execute the query, unless you're executing
within a program.
>3. All my queries are huge in size. So is there a mechanism to set the
> SQL Edit buffer size in dbaccess, as I am getting a error "801: SQL
> Edit buffer is full."
>
In Unix, I use 'vi' as my editor . . . .
export EDITOR=vi
then go into dbaccess.
>4. As in Oracle SQLPLUS (set spool on), is there a way to redirect the
> output to a file in Informix.
>
>
In dbaccess, there's a ring menu option for Output . . .