Best way to move table contents to another

Hi, Is there a very fast way other than delete and insert by which I could move a table from one table to nother. For example, i have this tableA, at the end of the day i want to move its contents to table B. Is there anyway that this could be done.? Deleting and inserting takes longer especially if they both have lrge record counts,,,,, we are only given an a very small time buffer to move the contents of the tables... Thanks.

Put each day in its own fragment. At the end of the day, detach the = fragment and re-attach it somewhere else. Be advised, that detaching a = fragment loses all sorts of metadata that came with the original table = and may have to be re-applied, NOT NULL for example. Think about what = you really need from that metadata. j. On Feb 3, 2012, at 8:47 AM, NATYURAL HORACIO wrote: > Hi,=20 >=20 > Is there a very fast way other than delete and insert by which I could = move a=20 > table from one table to nother.=20 > For example, i have this tableA, at the end of the day i want to move = its=20 > contents to table B.=20 > Is there anyway that this could be done.? Deleting and inserting takes = longer=20 > especially if they both have lrge record counts,,,,, we are only given = an a=20 > very small time buffer to move the contents of the tables...=20 >=20 > Thanks.=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20

PLEASE! Give us the full requirement and maybe someone will have an idea for you. - Do you need to move ALL of the rows out of one table and into new hitherto empty table ending with the first table empty? - Do you need to move ALL of the rows out of one table and intoan existing table with other data in it already ending with the first table empty? - Do you need to do this for some of the rows in the table? - What? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Fri, Feb 3, 2012 at 8:47 AM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > Hi, > > Is there a very fast way other than delete and insert by which I could > move a > table from one table to nother. > For example, i have this tableA, at the end of the day i want to move its > contents to table B. > Is there anyway that this could be done.? Deleting and inserting takes > longer > especially if they both have lrge record counts,,,,, we are only given an a > very small time buffer to move the contents of the tables... > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --90e6ba6e8bac7c3e4604b80fc0b3

PLEASE! Give us the full requirement and maybe someone will have an idea for you. hi really sorry bout that. - Do you need to move ALL of the rows out of one table and into new hitherto empty table ending with the first table empty? no, the other table is not empty. the other table is an existing table with the previous records in place. it's like a history table for the first table. what we are doing right now is renaming the table and then doing a union against that table. we don't want to incur too many unions. - Do you need to move ALL of the rows out of one table and intoan existing table with other data in it already ending with the first table empty? yes, we need to move all of the rows as much as possible into a table that has the data that was previously moved. - Do you need to do this for some of the rows in the table? nope, there's no particular selection needed. - What? sorry bout that lack of information.

You can use the table fragmentation by expression for this porpuse. Create your table with "fragment by expression" using date, and at the end of day you can detach the fragment. This is the most quick way to do it. On Fri, Feb 3, 2012 at 8:47 AM, NATYURAL HORACIO <horacio.natyural@gmail.com > wrote: > Hi, > > Is there a very fast way other than delete and insert by which I could > move a > table from one table to nother. > For example, i have this tableA, at the end of the day i want to move its > contents to table B. > Is there anyway that this could be done.? Deleting and inserting takes > longer > especially if they both have lrge record counts,,,,, we are only given an a > very small time buffer to move the contents of the tables... > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Saludos, Víctor Huaquisto --20cf303b396d97450104b8108651

just to expound, we have this particular table, tbl_sample. before, our script would delete and then insert it into another table. our problem is this is taking too much time and it is reaching the time that the system needs to open. so what we did was to rename tbl_sample into tbl_sample_a and then did a union in our queries. our problem is, if there are too many unions, the performance also might suffer. so we were thinking, what is the best solution to move the tbl_sample into tbl_sample_history for example. if we can move all rows, it would be better.

can i attach the fragment to another table? afterwards? Thanks

Yes. It is easy and very fast. On Fri, Feb 3, 2012 at 10:00 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > can i attach the fragment to another table? > afterwards? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Saludos, Víctor Huaquisto --20cf3040ee1c34f26604b810993b

oh this is nice. maybe what we need. is it equivalent to oracle's partition? or is it a different concept

Yes. The idea is the same... Regards. On Fri, Feb 3, 2012 at 10:03 AM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > oh this is nice. > maybe what we need. > > is it equivalent to oracle's partition? > or is it a different concept > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Saludos, Víctor Huaquisto --20cf3040ee1c1deb2c04b810a7c0

do i need to reindex if I do this? or will the indexes be applied. thanks

btw, there is no interval fragmentation in 11.1. so if i were to specify fragments per date, i'd have to specify all dates.. in informix 11, does it allow fragmentation within the same tablespace? thanks

No you can within the the same dbspace > btw, > > there is no interval fragmentation in 11.1. > so if i were to specify fragments per date, i'd have to specify all > dates.. > in informix 11, does it allow fragmentation within the same tablespace? > > thanks > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.

Then just attach the current table to the history table as a new fragment then recreate the current records table empty. Art On Feb 3, 2012 9:46 AM, "NATYURAL HORACIO" <horacio.natyural@gmail.com> wrote: > PLEASE! Give us the full requirement and maybe someone will have an idea > for you. > > hi really sorry bout that. > > - Do you need to move ALL of the rows out of one table and into new > > hitherto empty table ending with the first table empty? > > no, the other table is not empty. the other table is an existing table with > the previous records in place. > it's like a history table for the first table. what we are doing right now > is > renaming the table and then doing a union against that table. we don't > want to > incur too many unions. > > - Do you need to move ALL of the rows out of one table and intoan > > existing table with other data in it already ending with the first table > > empty? > > yes, we need to move all of the rows as much as possible into a table that > has > the data that was previously moved. > > - Do you need to do this for some of the rows in the table? > nope, there's no particular selection needed. > > - What? > sorry bout that lack of information. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f22c681a89a5d04b8125515

Yes. Art On Feb 3, 2012 10:00 AM, "NATYURAL HORACIO" <horacio.natyural@gmail.com> wrote: > can i attach the fragment to another table? > afterwards? > > Thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9399c1fc04d5c04b81258e5

Same idea, bad name. Art On Feb 3, 2012 10:03 AM, "NATYURAL HORACIO" <horacio.natyural@gmail.com> wrote: > oh this is nice. > maybe what we need. > > is it equivalent to oracle's partition? > or is it a different concept > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340f9d30fdd504b8125ac1

Indexes get carried along if their fragmentation expression is compatible. This works best if you can partition the table on a column that indicates the period covered, like transaction date. Art On Feb 3, 2012 10:07 AM, "NATYURAL HORACIO" <horacio.natyural@gmail.com> wrote: > do i need to reindex if I do this? > or will the indexes be applied. > > thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340f9d6812d504b8126128

No. Your drag expression can contain a range of dates: Art On Feb 3, 2012 11:01 AM, "NATYURAL HORACIO" <horacio.natyural@gmail.com> wrote: > btw, > > there is no interval fragmentation in 11.1. > so if i were to specify fragments per date, i'd have to specify all dates.. > in informix 11, does it allow fragmentation within the same tablespace? > > thanks > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f234cd3316c0404b81269f5

This is really nice. Exactly what we would be needing. This would also prevent the index from ballooning up. Can i add fragment to an existing table with no fragments? Really thanks a lot for all the help.

Yes. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ 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 Fri, Feb 3, 2012 at 12:54 PM, NATYURAL HORACIO < horacio.natyural@gmail.com> wrote: > This is really nice. Exactly what we would be needing. This would also > prevent > the index from ballooning up. > Can i add fragment to an existing table with no fragments? > > Really thanks a lot for all the help. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f234cd3d26a3404b814c2a2