IDS Forum

RE: Index on temporary table?

Posted By: Wyza, Jonathon
Date: Monday, 23 August 2010, at 2:37 p.m.

In Response To: Re: Index on temporary table? (Art Kagel)

That was the ticket, thanks for teaching me something new.

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 2:32 PM
To: ids@iiug.org
Subject: Re: Index on temporary table? [21009]

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.e
> > du
> > >
> > ==============================
> > 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

*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.

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.