IDS Forum

Re: Index on temporary table?

Posted By: Art Kagel
Date: Monday, 23 August 2010, at 2:32 p.m.

In Response To: RE: Index on temporary table? (Wyza, Jonathon)

Ahh, you said "I have a script" and I thought SPL procedure not Perl. OK.
Try doing the several indexes as separate "do" executions. Probably the
first create index is invalidating the query plan for the second index etc.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.

On Mon, Aug 23, 2010 at 2:20 PM, Wyza, Jonathon <wyzaj@bethelcollege.edu>wrote:

> EXECUTE IMMEDIATE doesn't work for me (I'm using Perl+DBI). Here is my code
> (watered down):
>
> $dbh->do("select stuff from stuff where stuff into temp t_data with no
> log;",
> undef, ($var1, $var2));
>
> my $indicies = <<EOS;
> create index t_data_cl on t_data(cl);
> create index t_data_st on t_data(st);
> create index t_data_zip on t_data(zip);
> create index t_data_id on t_data(id);
> EOS
>
> $dbh->do($indicies);
>
> Jonathon Wyza
> CX & CBORD System Administrator
> CX Programmer/Analyst
> Administrative Computing
> Bethel College
> (574)-257-3381
> AIM: Iamwyza
> jonathon.wyza@bethelcollege.edu
> ==============================
> SLES 11x64 & IDS 11.50.FC6
>
> "Don't document the problem, fix it."
> - Atli Björgvin Oddsson
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Monday, August 23, 2010 1:23 PM
> To: ids@iiug.org
> Subject: Re: Index on temporary table? [21005]
>
> The problem is that the table didn't exist when the stored procedure was
> created so the stored query plan is invalidated by the create temp table
> statement. If you change the CREATE INDEX to a dynamic statement it should
> be
> OK. So:
>
> EXECUTE IMMEDIATE "CREATE INDEX t_data_ix1 ON t_data(....)";
>
> Since the dynamic statement does not depend on a pre-compiled query plan
> you
> should avoid the -710 error.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors
> (art@iiug.org)
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and
> do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
> organization with which I am associated either explicitly, implicitly, or
> by
> inference. Neither do those opinions reflect those of other individuals
> affiliated with any entity with which I am affiliated nor those of the
> entities themselves.
>
> On Mon, Aug 23, 2010 at 11:52 AM, Wyza, Jonathon
> <wyzaj@bethelcollege.edu>wrote:
>
> > Is it possible to create an index on a temporary table? I have a
> > script that creates a temporary table worth of data and then does a
> > great deal of selects and parsing of the data inside of it. I was
> > thinking of putting an index on a few key columns in the table to
> > speed it up but when I try I get the following
> > message:
> >
> > execute failed: SQL: -710: Table (wyzaj.t_data) has been dropped,
> > altered or renamed
> >
> > However the table definitely exists. I thought that it might need its
> > statistics updated first, so I did a low & high update before I called
> > the create index, but still the same response. Is it simply not possible?
> >
> > Jonathon Wyza
> > CX & CBORD System Administrator
> > CX Programmer/Analyst
> > Administrative Computing
> > Bethel College
> > (574)-257-3381
> > AIM: Iamwyza
> > jonathon.wyza@bethelcollege.edu<mailto:jonathon.wyza@bethelcollege.edu
> > >
> > ==============================
> > SLES 11x64 & IDS 11.50.FC6
> >
> > "Don't document the problem, fix it."
> > - Atli Björgvin Oddsson
> >
> >
> >
> >
>
>
> *******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --001636af0209524588048e80e557
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636af0211099047048e81dd5a

Messages In This Thread

• Index on temporary table?
  Wyza, Jonathon -- Monday, 23 August 2010, at 11:52 a.m.
  • RE: Index on temporary table?
    Plugge, Joe R. -- Monday, 23 August 2010, at 11:58 a.m.
  • Re: Index on temporary table?
    Fernando Nunes -- Monday, 23 August 2010, at 11:59 a.m.
  • Re: Index on temporary table?
    Art Kagel -- Monday, 23 August 2010, at 1:22 p.m.
    • RE: Index on temporary table?
      Wyza, Jonathon -- Monday, 23 August 2010, at 2:20 p.m.
      • Re: Index on temporary table?
        Art Kagel -- Monday, 23 August 2010, at 2:32 p.m.
        • RE: Index on temporary table?
          Wyza, Jonathon -- Monday, 23 August 2010, at 2:37 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.