Jack,
Thanks for these great hints. I have not seen lots of these before and
they have given me lots of ideas.
Here's a question that you've induced: How do you determine what a good
value is for INITRANS?
What are the downsides of setting it too high?
Thanks,
Cherie Machler
Oracle DBA
Gelco Information Network
Jack Silvey
<jack_silvey@y To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
ahoo.com> cc:
Sent by: Subject: RE: partition tables
[EMAIL PROTECTED]
om
05/31/02 10:08
AM
Please respond
to ORACLE-L
Big (or should we just call you P?),
I have become somewhat experienced at manipulating
large partition tables since I have had to do it so
often (can you say poor initial design?). Export /
import is not the fastest way to go.
Here are some tips from the trenches:
1) You mention that each partition should have only
100,000 rows, but it might be beneficial for you to
focus more on the partition key. If you choose the
right partition key, queries will be able to do
partition pruning, where they can look at the data
dictionary and see that they only want to look at
certain partitions and not others. This is the big win
for partitioned tables - remove as much data from the
initial lookup as possible by skipping partitions.
2) Investigate "create table as select" with the
nologging option, in combination with the partition
exchange option. Let's assume that you want a
partition table with 10 partitions. You can create an
empty partition table with 10 partitions, CTAS 10 new
tables from your original table, and exchange the
partitions. After this, you will have a partitition
table full of data. Ain't it cool.
3) If you are sure of your data integrity, use the
"without validation" clause of the partition exchange.
Otherwise, Oracle will look at each and every row in
each and every partition when it is swapped in -
really slows things down.
4) Another way of creating a partition table from a
standalone is to create the empty partition table and
do a "insert /*+ nologging append parallel(a,12) */
into tablea a select /*+ parallel (b,12) /* from
tableb b;" and this will spawn off 12 parallel
processes for the select, 12 parallel processes for
the insert, use almost no rollback (appends the data)
and use almost no logging. This screams like a
banshee, very fast. Remember, *each* pq process will
write to its own extent, size your extents
accordingly.
5) Create bitmap partitioned indexes on your low
cardinality join columns (look at number of distinct
values / number of rows) - make sure and set your
sort_area_size wayyyyyy high (and set your
sort_area_retained_size to the same value - bug in
oracle with the two not being equal throwing a -600
error) but remember - *each* pq process gets its own
sort_area_size - don't run the box out of ram.
6) Don't create the indexes before you load - this
will fragment them and slow down your insert.
7) Remember to set your parallelism on your table back
to a reasonable level if you CTAS with pq - otherwise,
a high parallelism level on the table will tend to
make Oracle favor full table scans and hash or sort
joins over nested loops and index lookups. Same goes
with indexes - more PQ favors full index scans.
8) You can analyze all your partitions separate from
each other, and in tandem if you wish.
9) alter your index partitions "unusable" before you
load and then rebuild those partitions with the
"compute statistics" clause - this is faster and
optimizes your indexes. Bitmap indexes do not like to
be up while loading. Be advised, if someone tries to
query this table and they don't have
"skip_unusable_indexes=true" set in their session,
they will get an error. One way to set this parameter
in every session is to include it in a logon trigger
using "execute immediate 'alter session set
skip_unusable_indexes=true'" - HOWEVER, this will
change their execution plan to favor FTS since the
index is *not available*. Use caution.
10) Create and rebuild your indexes in parallel.
11) Use a MAXVALUE partition - this will allow you to
load all data and catch that data that falls outside
the other partition ranges. If you don't have a
maxvalue partition, and you try to insert a row that
does not match the other partitions, you will get
"inserted value beyond highest legal partition key"
and your insert will fail and might stop your load.
You can always split the MAXVALUE partition later.
12) Put all your table partitions in one tablespace
and all your index partitions in another single
tablespace (each suitably striped, of course, and
respecting recovery plans.) This will allow you to
automate partition management (addition of new
partitions and dropping of old) if you need to and
manage your tablespace space more effectively.
13) Use the "monitoring" option on your partitions -
not all partitions change enough to be analyzed each
time necessarily, and this will tell you which ones
need it.
14) *do not* create your table with pctfree = 0 and
*do* create with healthy initrans value (we use 8). If
you have initrans of 2 and pctfree of 0, and you try
to insert/update the table with 3 or more parallel
processes, the ITL table (the thing that the processes
register with when they use the table) cannot grow
(no space with pctfree 0) and the extra processes will
either wait or fail with a deadlock error. To change
pctfree you will have to rebuild the table.
hth,
jack silvey
> -----Original Message-----
> Sent: Thursday, May 30, 2002 8:59 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi All ,
> We are thinking of converting one of huge table in
> to partition table .
> What is best way to achieve this ? Is there any
> alter table clause that can
> do this or I will have to export ..recreate table
> with partition option and
> then import . Also how can I mentiod that partition
> should have only 100000
> rows . For example after each 100000 rows add
> another partition ?
> If I have 10000000 rows in the table , should I
> expect some performance gain
> out of this
>
> Thanks ,
> BigP
>
__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
