RE: Modify table
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 31 Aug 2004 11:32:34 -0400
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.
1) 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.
2) 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.
3) 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.
4) 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Goulet, Dick
Sent: Tuesday, August 31, 2004 10:37 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Modify table
Alter table move.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
From: Jaehne, Richard S [mailto:JaehneRS@xxxxxxxxx]
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
