Professional Web Applications Themes

Releasing extents from a table. - Informix

I am in need of some general information about releasing extents from a table that I will be trimming and releasing those extents back to the database. The information I have at hand is not clear about this process. Thanx, Jim Swallow GBAPS sending to informix-list...

  1. #1

    Default Releasing extents from a table.


    I am in need of some general information about releasing extents from a table that I will be trimming and releasing those extents back to the database. The information I have at hand is not clear about this process.

    Thanx, Jim Swallow GBAPS



    sending to informix-list
    JAMES Guest

  2. #2

    Default Re: Releasing extents from a table.

    On Mon, 26 Jan 2004 10:30:40 -0500, JAMES SWALLOW wrote:

    To release unused space from a table back to the common pool of free extents,
    you have to reorganize the table. There are several methods:

    - Unload the data, drop the table(s), recreate the table(s) with appropriate
    extent and next sizes, reload the table(s), recreate indexes and constraints.
    This is and the dup-table copy (next) are the only viable options if the
    initial extent size is so large that space will not be freed.

    - Create a new table with appropriate extent and next sizes, copy the data
    from the original table to the new one, drop the original table, rename the
    new one, recreate indexes and constraints.

    - Cluster an existing index, or create a new CLUSTERed index, after adjusting
    the NEXT SIZE for the table. PDQ & PSORT_ are important to the performance of
    this option. You can mark the index UNCLUSTERed after the reorg is complete
    if you like.

    - ALTER FRAGMENT FOR TABLE tablename INIT IN <single dbspace or fragmentation
    expression>; after adjusting the NEXT SIZE. This last works for all tables
    whether fragmented or not EVEN if you will be reorging the table into the same
    dbspace it already lives in. This option tends to be fastest and is the only
    one that will not change the table's tabid.

    As noted above, if the table has an initial EXTENT SIZE that is much larger
    than the current contents then that space will remain unused using the
    CLUSTER and ALTER FRAGMENT...INIT methods. You can query sysmaster:sysptnhdr
    to see the number of used pages or run oncheck -pT to for the same to
    estimate new EXTENT SIZE and NEXT SIZE values. Also my dbschema replacement
    utility, myschema, will automatically determine these minimal sizes for you
    when run with -a & -m and will generate ALTER FRAGMENT...INIT IN statements
    and ALTER TABLE...NEXT SIZE statements for you if you add the -r option.

    Myschema is included in the package utils2_ak available for download from the
    IIUG Software Repository. Also my package utils4_ak contains awk scripts to
    post-process a dbschema or myschema output file to create various scripts
    including reorg scripts.

    Art S. Kagel
     
    Art Guest

  3. #3

    Default Re: Releasing extents from a table.


    Unload, drop, recreate and reload. See the Informix Load FAQ.
    www.artentech.com/downloads.htm

    If you are using XPS you could just create a copy of the table, but with 7x
    or 9x you'd want the performance of the HPL to move the data around.

    cheers
    j.
    ----- Original Message -----
    From: "JAMES SWALLOW" <k12.wi.us>
    To: <org>
    Sent: Monday, January 26, 2004 11:30 AM
    Subject: Releasing extents from a table.

     
    table that I will be trimming and releasing those extents back to the
    database. The information I have at hand is not clear about this process. 


    sending to informix-list
    Jack Guest

  4. #4

    Default Re: Releasing extents from a table.

    And for unload / reload use HPL. You have to be patient when configuring jobs
    but after that ...

    nebojsa

    On Mon, 26 Jan 2004 18:20:43 -0400, "Jack Parker" <net> wrote:
     
    >table that I will be trimming and releasing those extents back to the
    >database. The information I have at hand is not clear about this process. 
    >
    >
    >sending to informix-list[/ref]

    ------------------------------------
    Remove spam block (DELETE_) to reply
    Nebojsa Guest

  5. #5

    Default Re: Releasing extents from a table.

    Not necessarily. If you create the new table as RAW then inserting
    into it is EXTREMELY quick. However you have to ALTER it back to
    STANDARD before you can put any primary key or indexes on it. Unless
    the table has loads of indexes (including rowids) on it this is likely
    to be a lot faster than two operations with HPL.

    Andy


    "Jack Parker" <net> wrote in message news:<bv48ji$npj$xmission.com>... 
    > table that I will be trimming and releasing those extents back to the
    > database. The information I have at hand is not clear about this process. 
    >
    >
    > sending to informix-list[/ref]
    Andy Guest

  6. #6

    Default Re: Releasing extents from a table.

    As Andy has mentioned Raw is exeptionally quick 1M+ rows per second
    but the absolute killer is the re-indexing.

    Andy Kent wrote: 
    > > table that I will be trimming and releasing those extents back to the
    > > database. The information I have at hand is not clear about this process. 
    > >
    > >
    > > sending to informix-list[/ref][/ref]

    --
    Paul Watson #
    Oninit Ltd # Growing old is mandatory
    Tel: +44 1436 672201 # Growing up is optional
    Fax: +44 1436 678693 #
    Mob: +44 7818 003457 #
    www.oninit.com #
    Paul Guest

  7. #7

    Default Re: Releasing extents from a table.


    I had forgotten that 9.x had a raw table type - thanks for pointing that
    out. You will get light appends with this - which is the whole point of
    doing the HPL song-and-dance. Looks like the FAQ may need updating.
    Speaking of which - since it is STILL languishing waiting for inclusion in
    the real FAQ (what's it been, 2 years?) - what is the status of that body of
    work? Has David gone to take the dirt nap or something?

    cheers
    j.

    ----- Original Message -----
    From: "Paul Watson" <com>
    To: <org>
    Sent: Tuesday, January 27, 2004 2:59 PM
    Subject: Re: Releasing extents from a table.

     [/ref]
    news:<bv48ji$npj$xmission.com>... [/ref][/ref]
    with 7x [/ref][/ref]
    from a [/ref][/ref]
    process. [/ref]
    >
    > --
    > Paul Watson #
    > Oninit Ltd # Growing old is mandatory
    > Tel: +44 1436 672201 # Growing up is optional
    > Fax: +44 1436 678693 #
    > Mob: +44 7818 003457 #
    > www.oninit.com #
    >[/ref]


    sending to informix-list
    Jack Guest

  8. #8

    Default Re: Releasing extents from a table.


    I had forgotten that 9.x had a raw table type - thanks for pointing that
    out. You will get light appends with this - which is the whole point of
    doing the HPL song-and-dance. Looks like the FAQ may need updating.
    Speaking of which - since it is STILL languishing waiting for inclusion in
    the real FAQ (what's it been, 2 years?) - what is the status of that body of
    work? Has David gone to take the dirt nap or something?

    cheers
    j.

    ----- Original Message -----
    From: "Paul Watson" <com>
    To: <org>
    Sent: Tuesday, January 27, 2004 2:59 PM
    Subject: Re: Releasing extents from a table.

     [/ref]
    news:<bv48ji$npj$xmission.com>... [/ref][/ref]
    with 7x [/ref][/ref]
    from a [/ref][/ref]
    process. [/ref]
    >
    > --
    > Paul Watson #
    > Oninit Ltd # Growing old is mandatory
    > Tel: +44 1436 672201 # Growing up is optional
    > Fax: +44 1436 678693 #
    > Mob: +44 7818 003457 #
    > www.oninit.com #
    >[/ref]


    sending to informix-list
    sending to informix-list
    Jack Guest

  9. #9

    Default Re: Releasing extents from a table.

    Doesn't HPL do something to speed up inserting into the indexes that
    you can't do with TYPE RAW? Are there SQL equivalents for all of HPL's
    jiggery-pokery or not?
     

    I don't know what this means but Roger's Profanisaurus comes to mind
    ....

    Andy


    "Jack Parker" <net> wrote in message news:<bv70fn$2pk$xmission.com>... [/ref]
    > news:<bv48ji$npj$xmission.com>... [/ref]
    > with 7x [/ref]
    > from a [/ref]
    > process. 
    > >
    > > --
    > > Paul Watson #
    > > Oninit Ltd # Growing old is mandatory
    > > Tel: +44 1436 672201 # Growing up is optional
    > > Fax: +44 1436 678693 #
    > > Mob: +44 7818 003457 #
    > > www.oninit.com #
    > >[/ref]
    >
    >
    > sending to informix-list[/ref]
    Andy Guest

  10. #10

    Default Re: Releasing extents from a table.

    Andy Kent wrote:
     
    >
    > I don't know what this means but Roger's Profanisaurus comes to mind[/ref]

    Been buried?

    --
    "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
    - Coluche
    Obnoxio Guest

Similar Threads

  1. Getting table extents from sysmaster
    By Andy in forum Informix
    Replies: 4
    Last Post: December 4th, 01:38 PM
  2. having problem with extents
    By Steven Kurlander in forum Informix
    Replies: 3
    Last Post: September 29th, 06:09 PM
  3. table extents
    By Rüdiger Mähl in forum Informix
    Replies: 5
    Last Post: September 17th, 03:06 PM
  4. Querying free extents through sysmaster
    By Andy Kent in forum Informix
    Replies: 2
    Last Post: July 28th, 08:33 PM
  5. counting remaining extents on tablespaces
    By amani in forum Oracle Server
    Replies: 2
    Last Post: July 7th, 05:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139