RE: Modify table

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 31 Aug 2004 11:32:34 -0400
Message-ID: <KNEIIDHFLNJDHOOCFCDKKELIFEAA.mwf@rsiz.com>

Are you concerned about the actual extents, or just parameters on the table?

If it is just the parameters, most of them can be altered (see alter table).

If it is the actual extents, please stop and think.

Unless each extent is smaller than your effective multiblock read it is nearly impossible to speed up anything other than your next drop table (which you probably don't need to do, but if you're still using dictionary managed extents....) I once did something really clever in a GCOS program and I was really proud of it until Chip Elliot casually observed that it was a nice savings but the total time of executions of that routine between now and the obsolescene of this entire machine with the old routine doesn't add up to the time you spent figuring it out. Now I actually learned something in the process in addition to Chip's lesson on relevance, so the work wasn't total waste. Chip was right (DTSS's last incarnation went off line quite a while ago.) Why did I mention this now? Oh - if you have some really small tables with lots of very tiny extents, it might be possible that the tiny cost to rebuild them is worth it if in the lifetime of the tables you'll do enough extra uncached PIOs on them because the extent boundary interrupts multiblock read. Then again, I wouldn't want to try to justify the cost of proving that. If it will save you time to explain to humans why you have 4 extents for a 32K table, that might be justification, too.
If the table has a large empty front and it is frequently scanned, especially by programs that scan where rownum = 1 or < 2 to discover whether the table is empty or not, you probably have a case where the time to rebuild is justified by the recurrent savings.
If you have a dominant order of access, especially if used by match-merge moving windows in programs, such that having the data in rows in that order physically in the table is of demonstrable value, you might want to rebuild it to put it in order.
If you're moving to uniform local extent management, you might want to rebuild it.

I'm trying to remember if I have any other reasons when rebuilds are justified in cost and time. Well, whole rebuilds are sometimes justified to accomodate new functional requirements such as supporting multiple languages justifies rebuilding for the new character set unless the non-rebuild change character set rules apply to your situation. Oh, here is a good one - If you have scheduled outage windows and the time to put automated rebuilds into a recurrent scheduling engine (like cron) is smaller than the time required to fix some report on the database that has format windows that now overflow.

Anyway, this was a long and possibly silly way to say, you may want to think about just altering the table. Not sure why you want max_extents small, though. I used to lose this argument all the time at DTSS: We wasted more cycles by terminating jobs that exceeded resource limits and then re-running the jobs from the start with higher limits than it would have cost to snipe runaways once a day that users would have agreed should not be re-run than all the time saved by resources limits ever. But I digress. When Mogens starts going on and on about VMS, I start thinking about where most of it was cribbed from. Then there is DaTaSyS, the first commercially available relational database management system. Of course it was only available on DTSS which had a total license distribution of about 15, so I can't really quibble with Oracle's claim. Even DTSS itself chose Oracle when it started making portable software products.

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Goulet, Dick Sent: Tuesday, August 31, 2004 10:37 AM
To: oracle-l_at_freelists.org
Subject: RE: Modify table

Alter table move.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----

From: Jaehne, Richard S [mailto:JaehneRS_at_state.gov] Sent: Tuesday, August 31, 2004 10:22 AM
To: 'Oracle-L (E-mail)
Subject: Modify table

All,

I'm running Oracle 8i on Solaris 8. I've got a table with some =3D parameters that are really out of bounds (max_extents are HUGE). I know = =3D
it's possible to reallocate on the fly w/ 9i, is it possible to =3D reallocate in 8i as well? I'd like to redo this table w/out having to = =3D
drop and recreate.

Thanks,=3D20

R. Jaehne

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------

----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com


----------------------------------------------------------------

To unsubscribe send email to:  oracle-l-request_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Tue Aug 31 2004 - 10:29:59 CDT