Oninit cdi / Archive
Talk to an expert →

Re: Index on temporary table?

Posted in 2010

Topics: Performance & Tuning, Stored Procedures & SPL

Art Kagel — — source: IIUG Forums & Mailing Lists

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

↪ replying to Art Kagel
Wyza, Jonathon — — source: IIUG Forums & Mailing Lists

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.

↪ replying to Wyza, Jonathon
Art Kagel — — source: IIUG Forums & Mailing Lists

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

↪ replying to Art Kagel
Wyza, Jonathon — — source: IIUG Forums & Mailing Lists

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.

Related threads

Glossary