Oracle-L: RE: partition tables

From: <Cherie_Machler_at_gelco.com>
Date: Fri, 31 May 2002 08:23:39 -0800
Message-ID: <F001.00470FC7.20020531082339@fatcity.com>

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_at_y       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>     
                    ahoo.com>            cc:                                                                     
                    Sent by:             Subject:     RE: partition tables                                       
                    root_at_fatcity.c                                                                               
                    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:

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.
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.
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.
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.
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.
Don't create the indexes before you load - this will fragment them and slow down your insert.
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.
You can analyze all your partitions separate from each other, and in tandem if you wish.
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.
Create and rebuild your indexes in parallel.
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.
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.
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.
*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: jack_silvey_at_yahoo.com

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: ListGuru_at_fatcity.com (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: Cherie_Machler_at_gelco.com

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: ListGuru_at_fatcity.com (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).

Received on Fri May 31 2002 - 11:23:39 CDT