Professional Web Applications Themes

modifying and adding columns to a big table - Oracle Server

I have to enlarge the precision of a numeric column and add two new columns on a partitioned table that counts 1,300,000 records. This on Oracle 9.0.1.3 on SuSE SLES 7. Is this a resource-consuming operation or a "light" one? Does this operation will invalidate the packages based on this table? Thanks in advance - Kind regards, -- Cris Carampa (spamto:com) "Poveri fanatici comunisti, noglobal e affetti dalla sindrome anti-microsoft" (gli utenti Linux secondo un poster di ICOD)...

  1. #1

    Default modifying and adding columns to a big table

    I have to enlarge the precision of a numeric column and add two new
    columns on a partitioned table that counts 1,300,000 records. This on
    Oracle 9.0.1.3 on SuSE SLES 7. Is this a resource-consuming operation or
    a "light" one? Does this operation will invalidate the packages based on
    this table?

    Thanks in advance - Kind regards,

    --
    Cris Carampa (spamto:com)

    "Poveri fanatici comunisti, noglobal e affetti dalla sindrome
    anti-microsoft" (gli utenti Linux secondo un poster di ICOD)

    Cris Guest

  2. #2

    Default Re: modifying and adding columns to a big table



    Cris Carampa wrote: 

    Adding and modifying columns is "light". It's dropping them that hurts.

    It will invalidate your packages.
    Rob Guest

  3. #3

    Default Re: modifying and adding columns to a big table

    Cris Carampa wrote:
     
    Should be near instantaneous and yes it will invalidate the packages
    requiring a recompile.
    Look in $ORACLE_HOME/rdbms/admin for the utlrp.sql script. Read the
    header before
    running it to make sure it will do what you want.

    Two comments though:

    1. You version of Oracle barely qualifies as better than Beta. Why not
    upgrade to 9.2?
    2. 1.3M rows is so small it is hardly worth partitioning. This is a very
    small table. I hope
    you didn't expend the money for EE just for this.

    --
    Daniel Morgan
    http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
    http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
    damorganx.washington.edu
    (replace 'x' with a 'u' to reply)

    Daniel Guest

  4. #4

    Default Re: modifying and adding columns to a big table

    On Mon, 10 Nov 2003 15:12:51 +0000 (UTC), Rob Cowell <com>
    wrote:
     
    >
    >Adding and modifying columns is "light".[/ref]

    Although it can get heavier if you add a column with a DEFAULT clause as
    Oracle has to populate the column in all existing rows.
     

    But remember you can defer this cost by using 'set unused', at the cost of
    having the space still allocated. At a later time you can either 'alter table x
    drop unused columns' (which does the 'heavy' work of actually removing the
    data).

    Or if it's a table where rows get deleted out after a certain amount of time,
    you can choose to leave it; new rows appear to have a 1-byte overhead for the
    inaccessible column from looking at block dumps, and the old ones with the
    larger overhead (the value of the column in that row) will eventually
    disappear. Depends what your priorities are and when/whether you can afford to
    rewrite the table with 'drop unused columns'.

    --
    Andy Hassall (co.uk) icq(5747695) (http://www.andyh.co.uk)
    Space: disk usage ysis tool (http://www.andyhsoftware.co.uk/space)
    Andy Guest

  5. #5

    Default Re: modifying and adding columns to a big table



    Daniel Morgan wrote: 

    That's a bit of a generalistaion. Partitioning can be for performance,
    not just administration.

    I work with some similar sized (assuming his rows aren't 4Mb each, why
    do people quote table sizes in numbers of rows?) tables that are hit
    hundreds of times a day to read about a 10th of all the rows for report
    generation, depending on the value of a certain key. Partitioning on
    that key drastically reduces scan times, because we don't need to scan
    the whole table, but an index wouldn't be selective enough to be worth
    using.
    Rob Guest

  6. #6

    Default Re: modifying and adding columns to a big table

    Rob Cowell wrote:
     
    >
    >That's a bit of a generalistaion. Partitioning can be for performance,
    >not just administration.
    >
    >I work with some similar sized (assuming his rows aren't 4Mb each, why
    >do people quote table sizes in numbers of rows?) tables that are hit
    >hundreds of times a day to read about a 10th of all the rows for report
    >generation, depending on the value of a certain key. Partitioning on
    >that key drastically reduces scan times, because we don't need to scan
    >the whole table, but an index wouldn't be selective enough to be worth
    >using.
    >
    >[/ref]
    Tables that are ht hundreds of times per day to read about a 10th of the
    rows probably
    have all of the information cached in memory unless they are very large
    tables which is why
    my comment.

    --
    Daniel Morgan
    http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
    http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
    damorganx.washington.edu
    (replace 'x' with a 'u' to reply)


    Daniel Guest

  7. #7

    Default Re: modifying and adding columns to a big table


     
    > > That's a bit of a generalistaion. Partitioning can be for
    > > performance,
    > > not just administration.
    > >
    > > I work with some similar sized (assuming his rows aren't 4Mb each,
    > > why
    > > do people quote table sizes in numbers of rows?) tables that are hit
    > > hundreds of times a day to read about a 10th of all the rows for
    > > report
    > > generation, depending on the value of a certain key. Partitioning on
    > > that key drastically reduces scan times, because we don't need to
    > > scan
    > > the whole table, but an index wouldn't be selective enough to be
    > > worth
    > > using.
    > >
    > >[/ref]
    > Tables that are ht hundreds of times per day to read about a 10th of
    > the rows probably
    > have all of the information cached in memory unless they are very
    > large tables which is why
    > my comment.
    >
    > --
    > Daniel Morgan
    > http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
    > http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
    > damorganx.washington.edu
    > (replace 'x' with a 'u' to reply)[/ref]

    Except that buffer reads are reduced by 90% for every query execution.
    Whether that's much of an issue would depend on the precise nature of
    the application and hardware, which I confess I'm not familiar with on
    Cris's system.
    Rob Guest

Similar Threads

  1. adding columns to an existing recordset
    By rorajoey in forum Macromedia ColdFusion
    Replies: 2
    Last Post: April 22nd, 05:29 PM
  2. Modifying FlexStore (w/ ver 1.5): Adding Categories
    By fernandesfran in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: April 5th, 02:46 PM
  3. Adding columns dynamically
    By Peter Kamitz in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: March 2nd, 06:11 PM
  4. Modifying Columns in Datagrid
    By Gary in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: April 1st, 04:25 AM
  5. modifying table values behind the scenes
    By Dirk Goldgar in forum Microsoft Access
    Replies: 1
    Last Post: July 9th, 09:51 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