Search BC Sites

Free Oracle Tips   HTML Text

Free Oracle App Server Tips   HTML Text

Privacy Policy

Redneck

Dress Code

Oracle tuning

Oracle training

Oracle support

Remote Oracle

Oracle Tips by Burleson

List Partitioning

            CREATE TABLE sales
(salesid NUMBER(9999), transdate DATE, state VARCHAR2(30)…..)
PARTITION BY LIST (state)
(PARTITION region_north VALUES (‘New York’, ‘Maine’)
             PARTITION region_south VALUES (‘Florida’, ‘Georgia’)
 ……….
 PARTITION region_ovflw VALUES (DEFAULT));

Range/List Composite Partitioning

Oracle’s latest release offers a hybrid solution for list partitioning that is a combination of the range and list partitioning techniques. The table is first partitioned by range and then subpartitioned using the list partitioning technique.  Unlike composite range-hash partitioning, each subpartition contains a logical division of the data that is specified by the DBA (as opposed to the range-hash technique where the subpartitions are selected by the hashing algorithm itself).

For example, the primary partition could be based on a date range to allow rolling window operations to occur and the second level could be based on a logical grouping (a list of states (i.e. Pennsylvania, New York, Ohio, etc.) is an good example of a logical grouping).   The data would be divided according to the date range and divided again according to the state’s name.  The example below shows a table using the range-list composite partitioning technique:
 

For more details and scripts, see my new book " Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot.