That's okay. I think the last solution is the only option available. Thanks for your support. You are my best resource.

Thanks
Amer

This was what I've been looking for... I think? This looks like multicolumn partition key, but you said composite partition? If it's a mulicolumn, wouldn't any insert for the month of OCT go into the first partition, since it's less the the bound?

If it's a composite, how do you get each country code to hash to the proper location?

Thanks

Thanks Tom!  We've been trying to implement a multicolumn key partition and been having some trouble.

Right now we have partitions based on Month, so each months transactions would go into a separate partition. The partitions use the Date as the key. With one client type this hasn't been problem.

Now we are introducing another client type, so we want to put another key along with the date... client_id.

Our example...
Create Table P_main_Test
 (
   Trans_ID     number       Not Null,
   Client_ID    number(4)    Not Null,
   P_Date       date         Not Null,
   f1           varchar2(10),
   f2           varchar2(10),
   f3           varchar2(10)
)
storage(initial 1m next 1m) tablespace users
partition by range(P_Date,client_ID)
(
    partition G_may99 values less than (to_date('01-JUN-1999','DD-MON-YYYY'),4999)
      TABLESPACE users,
    partition C_may99 values less than (to_date('01-JUN-1999','DD-MON-YYYY'),9999)
     TABLESPACE users,
    partition G_jun99 values less than (to_date('01-JUL-1999','DD-MON-YYYY'),4999)
      TABLESPACE users,
    partition C_jun99 values less than (to_date('01-JUL-1999','DD-MON-YYYY'),9999)
      TABLESPACE users,
    partition G_jul99 values less than (to_date('01-AUG-1999','DD-MON-YYYY'),4999)
      TABLESPACE users,
    partition C_jul99 values less than (to_date('01-AUG-1999','DD-MON-YYYY'),9999)
      TABLESPACE users
);

Sample Insert....

insert into P_Main_Test
  (Trans_id, P_date, client_id,f1,f2,f3)
values
  (1, '02-MAY-1999',4000,'aaa','bbb','ccc');
REM
insert into P_Main_Test
  (Trans_id, P_date, client_id,f1,f2,f3)
values
  (1, '02-MAY-1999',6000,'aaa','bbb','ccc');
insert into P_Main_Test
  (Trans_id, P_date, client_id,f1,f2,f3)
values
  (1, '01-JUN-1999',4000,'aaa','bbb','ccc');
REM
insert into P_Main_Test
  (Trans_id, P_date, client_id,f1,f2,f3)
values
  (1, '01-JUN-1999',6000,'aaa','bbb','ccc');


sample selects...


 select * from P_Main_Test partition(G_MAY99)
  2  /

  TRANS_ID  CLIENT_ID P_DATE    F1         F2         F3
---------- ---------- --------- ---------- ---------- ----------
         1       4000 02-MAY-99 aaa        bbb        ccc
         1       6000 02-MAY-99 aaa        bbb        ccc
         1       4000 01-JUN-99 aaa        bbb        ccc

SQL> Prompt C - May 99
C - May 99
SQL> select * from P_Main_Test partition(C_MAY99)
  2  /

  TRANS_ID  CLIENT_ID P_DATE    F1         F2         F3
---------- ---------- --------- ---------- ---------- ----------
         1       6000 01-JUN-99 aaa        bbb        ccc

I understand why, based on the Vector discussions, the both client id's are in G_MAY99 and why 1 June is in G_MAY99...  I'm just trying to figure out a way to make this work...  

Thanks again
 

Tom:

I have a similiar issue to the second follow-up in this thread.

I have a table that is partitioned by week (52 partitions). Many of the queries against this table have a where clause whereby they select a group of stores but don't have a reference to week in the where clause. Each query then has to scan every partition to get the results. I was thinking of subpartitioning the table on the store column (several thousand unique values in the store column) with 8 or 16 subpartitions. My hope is that for a query with a range of store values in the where clause it will only have to scan a limited number of subpartitions for each week partition, thereby cutting down on the IO. For other reasons I don't want to have an index on the store column.

What is the best way to determine if I am getting partition elimination with subpartitions. I have looked at @$ORACLE_HOME/rdbms/admin/utlxpls.sql, but I am not clear on the meaning. Here is an example:

Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 31 | 3K| 773308 | | |
| PARTITION RANGE INLIST | | | | |KEY(I) |KEY(I) |
| PARTITION HASH ALL | | | | | 1 | 8 |
| TABLE ACCESS FULL |TDS_QCF | 31 | 3K| 773308 |KEY(I) |KEY(I) |
--------------------------------------------------------------------------------

Does partition range inlist KEY(I) KEY(I) mean that it will read a limited number of partitions, and then within those partitions, the partition hash all Pstart and Pstop indicate the subpartitions for each partition that will be accessed since the same value of store will hash to the same subpartition within each partition, for example store_id 404 would hash to subpartition 3 within each partition.

Rob

Tom:

I have been doing some more thinking about this composite partitioned table. It seems like the docs recommend hash partitioning on something close to a unique key. While I have about 3000 unique values for the store_id column, the table contains 1.2 billion rows.

I am trying to decide how many subpartitions to specify. My thinking is that the more subpartitions there are the less IO there will be in query that has a where clause like
where store_id in (23,56,980) since it may be that all 3 of these values hash to a different subpartition, each subpartition will be smaller if I choose to create many subpartitions. On the other hand, is there a lot of overhead to having several thousand subpartitions. I now find that there may be 120 weeks of data therefore 120 partitions and if I have 32 subpartitions that would be a total of 3840 subpartitions.

What do you think of the package dbms_pclxutil for rebuilding partitioned indexes in parallel. Have you used it?

Hi Tom

When I execute package dbms_pclxutil I get an Oracle-error:

PROCEDURE dbms_pclxutil.build_part_index
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOBS_PER_BATCH NUMBER IN DEFAULT
PROCS_PER_JOB NUMBER IN DEFAULT
TAB_NAME VARCHAR2 IN DEFAULT
IDX_NAME VARCHAR2 IN DEFAULT
FORCE_OPT BOOLEAN IN DEFAULT

exec dbms_pclxutil.build_part_index(1,4,'PART_TABLE','PART_INDEX')
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYS.DBMS_PCLXUTIL", line 50
ORA-06512: at line 1

Because package body is wrapped and no help on Metalink is present I cannot see the problem on line 50. Please advice. Thanks.

Thanks for extreme fast feedback. We have Oracle Version 8.1.7.4.0 - 64bit Production on Sun Solaris 2.8 with 4 CPU's and 16 GB RAM. The table part_table has 4 partitions and two indexes. I also have tried DBMS_PCLXUTIL for other partitioned tables with the same problem.
Kind Regards.

Here is my test script:

CREATE TABLE PART_TABLE
(DAT_PER DATE NOT NULL
,CIFNR VARCHAR2(12) NOT NULL
,WERTEART VARCHAR2(5) NOT NULL
,WERT NUMBER(15,2) NOT NULL
)
TABLESPACE USERS
PARTITION BY RANGE (DAT_PER)
(PARTITION PART_TABLE_2002_M01 VALUES LESS THAN (TO_DATE('01.02.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M02 VALUES LESS THAN (TO_DATE('01.03.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M03 VALUES LESS THAN (TO_DATE('01.04.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M04 VALUES LESS THAN (TO_DATE('01.05.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M05 VALUES LESS THAN (TO_DATE('01.06.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M06 VALUES LESS THAN (TO_DATE('01.07.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M07 VALUES LESS THAN (TO_DATE('01.08.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M08 VALUES LESS THAN (TO_DATE('01.09.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M09 VALUES LESS THAN (TO_DATE('01.10.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M10 VALUES LESS THAN (TO_DATE('01.11.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M11 VALUES LESS THAN (TO_DATE('01.12.2002','DD.MM.YYYY'))
,PARTITION PART_TABLE_2002_M12 VALUES LESS THAN (TO_DATE('01.01.2003','DD.MM.YYYY'))
);

CREATE INDEX PART_INDEX ON PART_TABLE
(WERTEART)
TABLESPACE USERS
LOCAL
UNUSABLE;

execute dbms_pclxutil.build_part_index(1,4,'PART_TABLE','PART_INDEX')

Thanks for your investigations.

SQL> select * from NLS_SESSION_PARAMETERS order by 1;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CALENDAR                   GREGORIAN
NLS_COMP                       BINARY
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-YYYY
NLS_DATE_LANGUAGE              AMERICAN
NLS_DUAL_CURRENCY              $
NLS_ISO_CURRENCY               AMERICA
NLS_LANGUAGE                   AMERICAN
NLS_NUMERIC_CHARACTERS         .,
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM

SQL> select * from NLS_DATABASE_PARAMETERS order by 1;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_CALENDAR                   GREGORIAN
NLS_CHARACTERSET               WE8ISO8859P1
NLS_COMP                       BINARY
NLS_CURRENCY                   $
NLS_DATE_FORMAT                DD-MON-YYYY
NLS_DATE_LANGUAGE              AMERICAN
NLS_DUAL_CURRENCY              $
NLS_ISO_CURRENCY               AMERICA
NLS_LANGUAGE                   AMERICAN
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1
NLS_NUMERIC_CHARACTERS         .,
NLS_RDBMS_VERSION              8.1.7.4.0
NLS_SORT                       BINARY
NLS_TERRITORY                  AMERICA
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZH:TZM
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM 

Thank you for feedback. I'll contact Oracle Support.

why is my insert inserting 3 rows here please explain

1 CREATE TABLE MYEMP
2 (EMPNO NUMBER,JOB VARCHAR2(20), DEPTNO NUMBER)
3 PARTITION BY RANGE (DEPTNO)
4 SUBPARTITION BY HASH (JOB) SUBPARTITIONS 4
5 ( PARTITION P1 VALUES LESS THAN (11),
6 PARTITION P2 VALUES LESS THAN (21),
7 PARTITION P3 VALUES LESS THAN (31),
8* PARTITION P4 VALUES LESS THAN (MAXVALUE))
scott> /

Table created.
scott> INSERT INTO MYEMP
2 SELECT &EMPNO,'&ENAME',&DEPTNO FROM DUAL;
Enter value for empno: 1
Enter value for ename: MANAGER
Enter value for deptno: 10
old 2: SELECT &EMPNO,'&ENAME',&DEPTNO FROM DUAL
new 2: SELECT 1,'MANAGER',10 FROM DUAL

3 rows created.

scott> /
Enter value for empno: 2
Enter value for ename: CLARK
Enter value for deptno: 20
old 2: SELECT &EMPNO,'&ENAME',&DEPTNO FROM DUAL
new 2: SELECT 2,'CLARK',20 FROM DUAL

3 rows created.
scott> SELECT * FROM MYEMP;

EMPNO JOB DEPTNO
---------- -------------------- ----------
1 MANAGER 10
1 MANAGER 10
1 MANAGER 10
2 CLARK 20
2 CLARK 20
2 CLARK 20

6 rows selected.

scott>
scott> select trigger_name from user_triggers
2 where table_name = upper('myemp');

no rows selected

you have 3 rows in dual.  dual is magical, dual is a SYS owned object.  You have allowed someone to manually modify a SYS owned table which is not allowable.

ops$tkyte@ORA817DEV> create table t ( x int );

Table created.

ops$tkyte@ORA817DEV> insert into dual values ( 'x' );

1 row created.

ops$tkyte@ORA817DEV> insert into dual values ( 'x' );

1 row created.

ops$tkyte@ORA817DEV> select * from dual;

D
-
x

ops$tkyte@ORA817DEV> select count(*) from dual;

  COUNT(*)
----------
         3

ops$tkyte@ORA817DEV> insert into t select rownum from dual;

3 rows created.

ops$tkyte@ORA817DEV> select * from t;

         X
----------
         1
         2
         3

ops$tkyte@ORA817DEV> rollback;

Rollback complete.


correct your DUAL table by:

delete from dual where rownum <= 2;
commit;

 

G'day Tom.

You say has subpartitions work best with a LARGE amount of subpartitions.
Please define large.
A few Metalink notes I've read discussing composite partitioning (eg: Note 125314.1) use 8 subpartitions, and recommend using a number to the power of 2. (2,4,8,16 etc).

We are proposing partitioning a sales table by financial year, and subpartitioning by state (9 possible values in Australia), however your comments suggest that these subpartitions may not be used.

Your comments would be appreciated.

Thanks.

Based on above discussion, we have data with a great profile for V9.2 range partitioning by date + hash subpartitioning on a unique string identifier. We have hundreds of unique string ids so it would seem the range+hash partitioning would distribute the data nicely into, say, 200 or so subpartitions for each month.

Note: our string identifiers are unique but all have a common suffix. Example:

SPQID
----------
usabakerj
usamastersj
canporterp
canllensl

Please don't scold me for IDs with embedded country code. Rather, I'm interested to know if you think the hash distribution of strings like this will distribute somewhat evenly of if the hash algorithm will steer all the values into just a few sub-partitions (e.g. usa% and can%)?

I had a crazy thought to reverse the key:

partition by range ( spq_date )
subpartition by hash( reverse( spqid ) )

but adding the reverse() function leads to parse errors during table creation.

Thanks for any comments you may have,

-Mike

ops$tkyte@ORA10G> CREATE TABLE t
  2  (
  3    data       varchar2(80)
  4  )
  5  PARTITION BY hash (data) partitions 4
  6  /
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> insert into t
  2  select 'usa' || object_name from all_objects;
 
47079 rows created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2      l_cnt number;
  3  begin
  4      for x in ( select partition_name
  5                   from user_tab_partitions
  6                  where table_name = 'T' )
  7      loop
  8          execute immediate
  9          'select count(*)
 10             from t partition(' || x.partition_name || ')'
 11             into l_cnt;
 12          dbms_output.put_line
 13          ( 'partition ' || x.partition_name || ' ' || l_cnt );
 14      end loop;
 15  end;
 16  /
partition SYS_P185 11856
partition SYS_P186 12062
partition SYS_P187 11664
partition SYS_P188 11497
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G>


it'll spread it out 

Great example and test. Thank you very much and I will charge ahead with partitioning our data.

I have created a range partition table based on a composite key. Earlier the same table was range partitioned on one attribute.
After recreating the table with a composite key, the number of partitions went up to 9000. Earlier it was 900.

I am seeing the normal DML commands like Insert into the partitioned table is taking almost 2 minutes to insert 20K records and select from the table for the same 20k records is taking almost 1 minute.

On a non-partitioned table for the same 20k records, the insert and select are under a second.

Why such a drastic change in the DML statements on a composite key range partitioned table?

Thanks,

Tom:
I have a table which will store monthly usage data. We have to keep this data for several years. This data will be used for trending reports. I am thinking of creating the table with 12 partition - each partion per month. But when I have data for couple of years I don't want all Jan data in one partition, Feb data in 2nd partition etc etc. I would like to have month and year as the partition key. How do I create a table with month and year as the composite partition key.

Tom,

Regarding your statement:-

>> Even in 9i when LIST partitions are added -- they will not help you achieve your specific goal in this case as they cannot be used for subpartitions...

It might help future readers of this thread to know that this is certainly possible in 9.2 ( I guess you must have been talking about 9.0 given the age of the thread ).

920> CREATE TABLE sub_by_list
2 ( x INT
3 , y DATE
4 , z VARCHAR2(1)
5 )
6 PARTITION BY RANGE ( y )
7 SUBPARTITION BY LIST ( z )
8 SUBPARTITION TEMPLATE
9 (
10 SUBPARTITION sp1 VALUES ( 'A', 'B', 'C' )
11 , SUBPARTITION sp2 VALUES ( 'D', 'E', 'F' )
12 , SUBPARTITION df VALUES ( DEFAULT )
13 )
14 (
15 PARTITION p1 VALUES LESS THAN ( DATE '2003-01-01' )
16 , PARTITION p2 VALUES LESS THAN ( DATE '2004-01-01' )
17 , PARTITION mv VALUES LESS THAN ( MAXVALUE )
18 );

Table created.

920>
920> INSERT INTO sub_by_list
2 SELECT object_id
3 , created
4 , SUBSTR( object_name, 1, 1 )
5 FROM user_objects;

706 rows created.

920>
920> ANALYZE TABLE sub_by_list COMPUTE STATISTICS;

Table analyzed.

920>
920> SELECT table_name
2 , partition_name
3 , subpartition_name
4 , num_rows
5 FROM user_tab_subpartitions;

TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------ --------------- -------------------- ----------
SUB_BY_LIST MV MV_SP1 94
SUB_BY_LIST MV MV_SP2 17
SUB_BY_LIST MV MV_DF 169
SUB_BY_LIST P1 P1_SP1 0
SUB_BY_LIST P1 P1_SP2 0
SUB_BY_LIST P1 P1_DF 0
SUB_BY_LIST P2 P2_SP1 156
SUB_BY_LIST P2 P2_SP2 73
SUB_BY_LIST P2 P2_DF 197

9 rows selected.

Regards
Adrian

I have generated the tkprof for both the insert statements and pasting it below.

The first insert statement inserts into a range partitioned table. The number of partitions is 8000.

the second insert statement is a simple statement on a non-partitioned table.

********************************************************************************
insert into rpt_fact_retail_blend_0617(bipad,issue_code,yr_term_code)
select bipad,issue_code,yr_term_code from rpt_bipad_issue_0617

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.10 0 0 0 0
Execute 1 5.08 225.26 13228 17635 54762 20229
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.11 225.36 13228 17635 54762 20229

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29

Rows Row Source Operation
------- ---------------------------------------------------
20229 TABLE ACCESS FULL RPT_BIPAD_ISSUE_0617 (cr=120 r=159 w=0 time=843776 us)

************************************************************


insert into a select * from rpt_bipad_issue_0617


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 3 0 0
Execute 1 0.15 0.93 124 270 745 20229
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.15 0.94 124 273 745 20229

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29

Rows Row Source Operation
------- ---------------------------------------------------
20229 TABLE ACCESS FULL RPT_BIPAD_ISSUE_0617 (cr=120 r=117 w=0 time=589824 us)

************************************************************

Thanks,

Alright, the tkprof report for a insert into a partitioned table. This partitoned table has 900 partitions.

********************************************************************************

insert into rpt_fact_retail_blend3(bipad,issue_code,yr_term_code)
select bipad,issue_code,yr_term_code from rpt_bipad_issue_0617

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 1.33 20.58 4402 8309 12839 20229
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.35 20.60 4402 8309 12839 20229

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29
********************************************************************************


If I see the CPU time, elapsed time, it is lowest on a non-partioned table, better in a partitoned table that has 900 partitions and probably worse in a partitioned table that has 8000 partitions.

I am not sure how to explain this to my manager.

Help me out.

Thanks,

I have generated tkprof by setting events 10046 level 12.

The first insert statement is on a non-partitioned table.
the second one is on a range partitioned table that has 8000 partitions.
The third insert statement is on a range partitioned table that has 900 partitioned tables

********************************************************************************

insert into a select * from rpt_bipad_issue_0617


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.15 0.54 123 386 749 20229
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.16 0.56 123 386 749 20229

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29

Rows Row Source Operation
------- ---------------------------------------------------
20229 TABLE ACCESS FULL RPT_BIPAD_ISSUE_0617 (cr=120 r=118 w=0 time=370688 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 6 0.01 0.07
db file scattered read 8 0.05 0.32
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 2.56 2.56
********************************************************************************
insert into rpt_fact_retail_blend_0617(bipad,issue_code,yr_term_code)
select bipad,issue_code,yr_term_code from rpt_bipad_issue_0617

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.06 0.06 0 0 0 0
Execute 1 8.11 205.77 14130 19579 55737 20229
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 8.18 205.84 14130 19579 55737 20229

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29

Rows Row Source Operation
------- ---------------------------------------------------
20229 TABLE ACCESS FULL RPT_BIPAD_ISSUE_0617 (cr=120 r=223 w=0 time=772096 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 13911 0.59 197.41
db file scattered read 19 0.12 0.63
latch free 4 0.01 0.01
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 20.62 20.62
********************************************************************************
insert into rpt_fact_retail_blend3(bipad,issue_code,yr_term_code)
select bipad,issue_code,yr_term_code from rpt_bipad_issue_0617

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.65 15.05 1583 8314 12848 20229
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.65 15.06 1583 8314 12848 20229

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 8 0.07 0.44
db file sequential read 1466 0.15 12.95
SQL*Net message to client 1 0.00 0.00



********************************************************************************

Thanks

Thanks for the great example. I tried to insert into a table that has 8000 partitions ordering by the range partitioned column. It seems even worse.
I have the tkprof report for that.

insert into rpt_fact_retail_blend_0617(bipad,issue_code,yr_term_code)
select bipad,issue_code,yr_term_code from rpt_bipad_issue_0617
order by yr_term_code

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 5.70 346.11 15813 7967 7909 20229
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 5.71 346.13 15813 7967 7909 20229

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 15696 0.59 340.07
db file scattered read 8 0.19 1.07
SQL*Net message to client 1 0.00 0.00
***********************************************************************

Thanks,

Thanks for a lot of info.
I have one more question......

After inserting the data into the table that has 8000 partitions, I expect the query to return fast compared to accessing the 900 partition table. It seems it is not.

The old table is partitioned by magazine ID (900 partitions) and the new table is partitioned by magazine ID and monthly code(8000 partitions). So roughly the same magazine ID is distributed to 10 different partitions(on an average) in the new table.

Do you think having the new table is a good idea for query purpose? Our users will always query by magazine and they restrict by time period by saying give me the sales of a magazine for the time period between jan and mar of 2004. Now the new table has data for three months in three different partitions.

Any suggestion in this matter?

Thanks,

Hi Tom,

In 10g certification outline there is an item:

"Specify storage characteristics for index partitions in the table partition DML commands"

Could you help me give an example? I really can't figure out what it is.

Thanks,

</code> http://www.oracle.com/education/certification/objectives/index.html?40a.html <code>

It's the outline for the new 10g upgrade exam.

BTW, are you certified as an OCP? I doubt you are not. *_*

Tom:
1)at conditions determine whether to use a Global Index or a Partitioned Index?
2)I have a table that i want to partition by day.Every day i drop the oldest partition and create a new one.Effectively, the table has to have seven partitions(holds a week worth of data).What kind of partitioning method do you recommend and what kind of index
Thanks

Suppose I have range partitioning in my database as per the sizing requirements at the time. The thought was that the ranges would contain similar data volumes (monthly partitions, for example).

But it is later seen that some months have order of magnitude more data than others.

Is it possible to subpartition just these big partitions by using hash partitioning?

Thanks

"however, each partition may have a different number of subpartitions!"

How is this possible, how can I set this up?

Thanks

Hi, Tom. There's a great deal of good info in this thread! One variation on the partitioning theme - can you partition in 9iR2 by list and then subpartition by hash? Thank you.

Regards.

Hi Tom, thank you for you answers.
I have a table partitioned by month and subpartitioned by list(user_id)

I'm trying to figure out the syntax for creating local index on that table where I would assign subparitionin segments to diff tablespaces.

here is the table:

(sorry for tablespace syntax, it's relevant to the example)

CREATE TABLE sum_table
(
USER_ID NUMBER NOT NULL
,DATE_OCCURRED DATE
,QTY_OCURRED NUMBER
) PARTITION BY RANGE (DATE_OCCURRED)
SUBPARTITION BY LIST (USER_ID)
SUBPARTITION TEMPLATE
(
SUBPARTITION uid_1165 VALUES (116,117) TABLESPACE tbl_3,
SUBPARTITION uid_1166 VALUES (284) TABLESPACE tbl_4,
SUBPARTITION uid_1167 VALUES (307) TABLESPACE tbl_3,
SUBPARTITION uid_1168 VALUES (3515) TABLESPACE tbl_4,
SUBPARTITION uid_others VALUES (DEFAULT ) TABLESPACE tbl_3
)
(PARTITION PSE_SEP_04 VALUES LESS THAN ('2004-09-01') TABLESPACE tbl_3,
PARTITION PSE_OCT_04 VALUES LESS THAN ('2004-10-01') TABLESPACE tbl_4,
PARTITION PSE_NOV_04 VALUES LESS THAN ('2004-11-01') TABLESPACE tbl_3,
PARTITION PSE_max VALUES LESS THAN (MAXVALUE) TABLESPACE tbl_4)
NOLOGGING NOCACHE PARALLEL;

I can create index only assigning tablespace to partitions like that:

CREATE INDEX sum_PUB_SID_E_po_IDX2 ON sum_PUB_SID_E (USER_ID,DATE_OCCURRED,OFFER_ID) PARALLEL LOCAL
(
PARTITION PSE_SEP_04 TABLESPACE tbl_i3,
PARTITION PSE_OCT_04 TABLESPACE tbl_i4,
PARTITION PSE_NOV_04 TABLESPACE tbl_i3,
PARTITION PSE_max TABLESPACE tbl_i4);

but it won't provide the benefit of splitting the IO for concurrent queries, cause most of the quries run against the latest month. So I want to distribute the users across diff tablespaces by something like this:

CREATE INDEX sum_PUB_SID_E_po_IDX2 ON sum_PUB_SID_E (USER_ID,DATE_OCCURRED,OFFER_ID) PARALLEL LOCAL
(
PARTITION PSE_SEP_04 TABLESPACE tbl_i3,
PARTITION PSE_OCT_04 TABLESPACE tbl_i4,
PARTITION PSE_NOV_04 TABLESPACE tbl_i3,
PARTITION PSE_max TABLESPACE tbl_i4);
SUBPARTITION TEMPLATE
(SUBPARTITION uid_1165 TABLESPACE tbl_i3,
SUBPARTITION uid_1166 TABLESPACE tbl_i4,
SUBPARTITION uid_1167 TABLESPACE tbl_i3,
SUBPARTITION uid_1168 TABLESPACE tbl_i4,
SUBPARTITION uid_others TABLESPACE tbl_i3
);

unfortunately oracle doesn't seem to understand the "SUBPARTITION TEMPLATE" clause in that context.

Is there any valid syntax I can use?

the # of part per #of subpart >800 so I woudn't want to explicitly list every subpartition.

Thank you for your time.
David

well, it's getting big but we have pretty cheap setup:
2 JBODs with 4 raid volumes.
Anyway it's not something I can change right now.
The bottom line is, I have to balance the IO using tablespaces.

Just trying to figure out if some syntax exists at all to assign tablespace name to subpartitions of a local index (using subpartition template).

Thank you,
David

OK, I guess I got more than I asked for :)
Thanks a lot Tom,
Your suggestion really makes tablespace-based IO distribution completely unnecessary.
I got it now and will definitely use it not only for that specific issue I’m solving, but also to solve a general problem of IO balancing in my DB.

I only wonder if local tablespaces with auto segment management have any overhead from using large number of small extents? I guess it’s knee-jerk reaction from the days of the dictionary-managed tablespaces but I wonder if 5G segment consisting out of 5000 1M extents will have any noticeable performance overhead for reads, writes and allocation?

Also where can I read to understand how local tablespaces and auto segment management works internally?

Thank you again,
I really appreciate your approach
(getting real practical answer instead of technically correct nonsense :)

David

Yes, I use ASSM with uniform extent size on 9i r2.

Just read the section in "Effective Oracle by Design" -
found the book almost as useful as your site :D,
I only need to read it more often ;)

Here is my partitioned table copied to the new "RoundRobin" LMT with ASSM:

TABLESPACE_NAME FILE_NAME SIZE_MB
----------------- --------------------------------------- --------
RR_1M_SUM /u01/oradata/prod1/rr_1m_sum_u01_01.dbf 1152
RR_1M_SUM /u02/oradata/prod1/rr_1m_sum_u02_01.dbf 1840
RR_1M_SUM /u03/oradata/prod1/rr_1m_sum_u03_01.dbf 1312
RR_1M_SUM /u04/oradata/prod1/rr_1m_sum_u04_01.dbf 1232

Same if we look at the extents:

select file_id, count(*)
from dba_extents
where segment_name='SUM_PUB_SID'
group by rollup(file_id);

FILE_ID COUNT(*)
---------- ----------
43 1151
44 1839
45 1311
46 1231
Total 5532

The individual partition distribution however look more even:

select file_id,partition_name, count(*)
from dba_extents
where segment_name='SUM_PUB_SID'
group by file_id, partition_name
order by partition_name, file_Id;

FILE_ID PARTITION_NAME COUNT(*)
------- --------------- --------
43 PS_AUG_04_PID_1167 20
44 PS_AUG_04_PID_1167 23
45 PS_AUG_04_PID_1167 22
46 PS_AUG_04_PID_1167 20
43 PS_AUG_04_PID_1168 39
44 PS_AUG_04_PID_1168 38
45 PS_AUG_04_PID_1168 39
46 PS_AUG_04_PID_1168 36
...........................
43 PS_NOV_04_PID_1168 119
44 PS_NOV_04_PID_1168 111
45 PS_NOV_04_PID_1168 114
46 PS_NOV_04_PID_1168 113

1341 rows selected.

The bottom line is, I have 5532 extents this way.

Just wonder if there there any noticeable issue with extents bitmap in file headers
growing too big? Is 5532 too large?

Thank you,
David

tom,

I got a big fact table which consists of 4 "sub" - tables, coming from the source which means
you could query star-schema-like like:
select DIM_A.level_1, DIM_B.level_2, sum(revenue)
from (
select DIM_A_SID, DIM_B_SID, revenue from fact_A
UNION ALL
select DIM_A_SID, DIM_B_SID, revenue from fact_B
UNION ALL
select DIM_A_SID, DIM_B_SID, revenue from fact_C
...
), DIM_A, DIM_B
where ...
group by DIM_A.level_1, DIM_B.level_2

source table A : 60 Mio rows ( on average. grows and shrinks --> records move into B or C )
source table B : 2 Mio rows per day (probably only 1 year stored)
source table C : 200 K rows
source table D : 50 Mio rows per year

I'd like to do composite : 1st list ('A', 'B', 'C', 'D')
and then range by date , because I have to exchange or extend "sub"- table (partition) A , add dayly subpartitions to B and so on ...
But that partition design isnt allowed ...

The issue is, that the records are moving between these 4 big tables and the finance-guys like to see a complete
view (union) of all tables at a certain time.

1st question is:
Can I garantuee similar performance on a
statement like the shown Union all compared with
a select on one fact table having "materialized" that union ?

2nd question:
What alternatives are there left ?
I got a merge statement (does the union inline) working, but now I got difficult delta criteria on the source tables to apply , otherwise the update cost of the merge grows heavily. But for source A for example the best way of population to a / the fact table would be a full refresh, especially because we got problems to define a delta ( timestamp > last_load_DateTime) criteria !

hope u can help,
kind regards
marc

1) no, not in general.  4 tables, 4 different sets of stats, optimizer optimizing a 4 table union all to be joined -- you'll get radically different plans potentially.  they won't be comparable.

2) can't you use a vector here with a range?

ops$tkyte@ORA10GR1> Create Table t
  2  (
  3     code   varchar2(1),
  4     dt     date
  5  )
  6  partition by range(code,dt)
  7  (
  8      partition p1a values less than ( 'A', to_date('01-jan-2004') ),
  9      partition p2a values less than ( 'A', to_date('02-jan-2004') ),
 10      partition p3a values less than ( 'A', to_date('03-jan-2004') ),
 11      partition p1b values less than ( 'B', to_date('01-jan-2004') ),
 12      partition p2b values less than ( 'B', to_date('02-jan-2004') ),
 13      partition p3b values less than ( 'B', to_date('03-jan-2004') ),
 14      partition p1c values less than ( 'C', to_date('01-jan-2004') ),
 15      partition p2c values less than ( 'C', to_date('02-jan-2004') ),
 16      partition p3c values less than ( 'C', to_date('03-jan-2004') ),
 17      partition p1d values less than ( 'D', to_date('01-jan-2004') ),
 18      partition p2d values less than ( 'D', to_date('02-jan-2004') ),
 19      partition p3d values less than ( 'D', to_date('03-jan-2004') )
 20  )
 21  /
 
Table created.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> declare
  2     type vcarray is table of varchar2(1);
  3     l_codes vcarray := vcarray('A','B','C','D');
  4  begin
  5     for i in 1..l_codes.count
  6     loop
  7        for j in 0..2
  8        loop
  9           insert into t values ( l_codes(i), to_date('31-dec-2003')+j );
 10        end loop;
 11     end loop;
 12  end;
 13  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select 'p1a', x.* from t partition (p1a) x union all
  2  select 'p2a', x.* from t partition (p2a) x union all
  3  select 'p3a', x.* from t partition (p3a) x union all
  4  select 'p1b', x.* from t partition (p1b) x union all
  5  select 'p2b', x.* from t partition (p2b) x union all
  6  select 'p3b', x.* from t partition (p3b) x union all
  7  select 'p1c', x.* from t partition (p1c) x union all
  8  select 'p2c', x.* from t partition (p2c) x union all
  9  select 'p3c', x.* from t partition (p3c) x union all
 10  select 'p1d', x.* from t partition (p1d) x union all
 11  select 'p2d', x.* from t partition (p2d) x union all
 12  select 'p3d', x.* from t partition (p3d) x;
 
'P1 C DT
--- - ---------
p1a A 31-DEC-03
p2a A 01-JAN-04
p3a A 02-JAN-04
p1b B 31-DEC-03
p2b B 01-JAN-04
p3b B 02-JAN-04
p1c C 31-DEC-03
p2c C 01-JAN-04
p3c C 02-JAN-04
p1d D 31-DEC-03
p2d D 01-JAN-04
p3d D 02-JAN-04
 
12 rows selected.
 

That is just like a LIST with a RANGE underneath it. 

Thanks Tom. Good idea.
Another question:
can I see if a partition got updated after a load ?
like :
table partition last_update
A A_1 null / 2004-01-01
A A_2 'Y' / 2004-02-02
...

Tom,
considering your vector-range partition example above ...
alternitively I still could do composite partitioning:
by range (date) and
then subpartition by list (source)
one subpartition for each A,B,C,D

the major load I need to tune is the "exchange" (truncate & re-insert) of all records coming from source B ( approx. 60 Mio ) , so using the vecor I had to truncate all partitions of
"... less than values ('B', 'date1')" (B must be C to get B values categorized in there because of the less than ...)
"... ... ('B', 'date2')"
...
using subpartitions I had to drop the subpartition for 'B'
in every partition.

Will there be a considerable difference in load-time
between these 2 ways ?
I'd like to go for the composite-way - as I can use subpartition template and in general the splitting is more natural.

Could I still get performance problems using too many (> 1000) partitions as J. Lewis found out ( was in 8i ) ? -
on "drop partition" and querying using bind-variables !

thanks a lot,
Kind regards
Marc

Tom,
continuing the IO distribution question above,
I'm trying to switch to Round Robin tablespaces striping over all 4 physical raids volumes, however while it should be good once everything is moved, right now RR tablespaces block the most (cause they block on every bottleneck on every volume)

Since the DB is big and very active, I need lots of time for rebuild.

I'm looking for a way to identify the segments that are most active IO-wise and migrate them first to RR.
Are there any apparent way to get that data from the oracle dictionary?
(Statspack only gives the breakdown by file and tablespace)
If not, any other ways?

Thank you very much,
David

fortunately 9i r2 :)

found for statspack:

Level 7 includes the following segment statistics:

Logical reads
Db block changes
Physical reads
Physical writes
Physical reads direct
Physical writes direct
Global cache consistent read blocks served (RAC specific)
Global cache current blocks served (RAC specific)
Buffer busy waits
ITL waits
Row lock waits
Although Statspack captures all segment statistics, it reports only the following statistics that exceed one of the predefined threshold parameters:

Number of logical reads on the segment. The default is 10,000.
Number of physical reads on the segment. The default is 1,000.
Number of buffer busy waits on the segment. The default is 100.
Number of row lock waits on the segment. The default is 100.
Number of ITL waits on the segment. The default is 100.
Number of global cache consistent read blocks served (RAC only). The default is 1,000.
Number of global cache current blocks served (RAC only). The default is 1,000.

thank you!

Tom, i am trying to partition a giant table say 100M rows using list partitioning by 3 columns; first column (call it A has 50 values, second (B) has 10 and third (C) 20).
to generate sql i need to list all possible combinations which is 50x10x20 = 10,000 combinations;
the resulting sql exceeds the size of varchar2 type (32k). and execute immmediate does not allow to run it due to type error.
how can one execute such a giant sql ?

Stanislav

ok, i discovered that oracle 9.2 has an ability
to parse big sql if you split sql into 32k chunks and load them into array of strings each 32k max; that in package documentation for dbms_sql.parse routine

as far a business logic, building a worldwide data warehouse and part of it fact table to store summarized transactions; those are continuouly changing even for prior time periods; thus vector A represents country, B represents snapshot time period and C time period. we store both history and current data views; to enable parallel work done on each country data i am trying to partition it naturally to match existing workstreams. Currently we store the data in one giant flat table and amount of contention generated by parallel workstreams it forcing us to seek partioning schemes

Hi,

i wish to create partion by week,and wish to reuse that partition.

Means for a month i want to create only 4 partitions,last partition will have more days.

Thenfor next month,it should use same partitions we created.

So in short i don't want to create 52 partitions for a year,but just 4 partitions.

I am doing partition on timesatamp column whose dataye is date.

+++++++++++++++++++++
CREATE TABLE TEST ("ID" VARCHAR2(50) NOT
NULL ENABLE, "TRANSACTIONID" VARCHAR2(255) NOT NULL ENABLE,
"SOURCEID" VARCHAR2(255) NOT NULL ENABLE, "DOCUMENTID" VARCHAR2(255)
NOT NULL ENABLE, "TIMESTAMP" DATE NOT NULL ENABLE, "TIMESPENT"
NUMBER, "SERVICEIN" CLOB, "SERVICEOUT" CLOB, "APPNAME" VARCHAR2(50)
NOT NULL ENABLE, "SERVICENAME" VARCHAR2(255) NOT NULL ENABLE,
"MACHINENAME" VARCHAR2(255) NOT NULL ENABLE, "OBJECTTYPE"
VARCHAR2(255), "STATUS" VARCHAR2(10) NOT NULL ENABLE, "VERSION_NUM"
VARCHAR2(10), "MAX_VERSION" VARCHAR2(1), "COMMENTS" VARCHAR2(255))
PCTFREE 25 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 131072
FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "VRTSD" LOGGING
NOCOMPRESS LOB ("SERVICEIN") STORE AS (TABLESPACE "VRTSD" ENABLE
STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE STORAGE(INITIAL
524288 FREELISTS 1 FREELIST GROUPS 1)) LOB ("SERVICEOUT") STORE AS
(TABLESPACE "TESTD" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION
10 NOCACHE STORAGE(INITIAL 524288 FREELISTS 1 FREELIST GROUPS 1))
PARTITION BY RANGE ("TIMESTAMP" ) (PARTITION
"CUF_TXN_JUN05_1" VALUES LESS THAN (TO_DATE('JUN-08-2005','Mon-DD-YYYY')) PCTFREE 10 PCTUSED 85
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 25m NEXT 25m MINEXTENTS 1
MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "CUF_TXN_JUN05" LOGGING NOCOMPRESS,
PARTITION "CUF_TXN_JUN05_2" VALUES LESS THAN (TO_DATE('JUN-16-2005','Mon-DD-YYYY')) PCTFREE 10 PCTUSED 85
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 25m NEXT 25m MINEXTENTS 1
MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "CUF_TXN_JUN05" LOGGING NOCOMPRESS,
PARTITION "CUF_TXN_JUN05_3" VALUES LESS THAN (TO_DATE('JUN-23-2005','Mon-DD-YYYY')) PCTFREE 10 PCTUSED 85
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 25m NEXT 25m MINEXTENTS 1
MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "CUF_TXN_JUN05" LOGGING NOCOMPRESS,
PARTITION "CUF_TXN_JUN05_4" VALUES LESS THAN (TO_DATE('JUL-01-2005','Mon-DD-YYYY')) PCTFREE 10 PCTUSED 85
INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 25m NEXT 25m MINEXTENTS 1
MAXEXTENTS 505 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "CUF_TXN_JUN05" LOGGING NOCOMPRESS);

++++++++++++++++++++++++++++


Could you help?

Thanks,
Atul

Hi,

say if i want to have 4 partitopns in one month
ie 1-7,8-15,16-23,24-31

Will following logic help

PARTITION BY RANGE ("TIMESTAMP" ) (PARTITION
"CUF_TXN_JUN05_1" VALUES LESS THAN to_number((TO_CHAR(Timestamp,'DD'))
PCTFREE 10 PCTUSED 85

so that i get DD which will helpful for me to keep only 4 partitions only,as its not dependant on month

Thanks

In this thread there is an example (june 18,2004)
partition by range and
sub-partition by list

Is it possible to do in 9.2
partition by list and
sub-partition by range
as well

"
range is at the top of the composite scheme
"

Does 10g has any enhancement to let composite partitioning
have either range or list as primary partitioning

Hi Tom,
We have a table with 38 million rows.
Our core business processes select and update the table by disc_number column which is unique in the table.
So we are considering to hash partition the table by this column to facilitate these selects and updates.
We built two tables to run tests:
1) Non-partitioned table – a copy of original one.
2) Hash partitioned table (64 partitions) with the same set of non-partitioned indexes and with a local unique index on disc_number column.
Somehow update performance on portioned table almost the same (in fact worse) as the one on non-partitioned table even though it uses single partition index unique scan..
So there are a couple of questions:
1) What could be the reason of the behavior above?
2) Would you recommend to go with non-partitioned or partitioned table? Our concerns regarding the second approach are:
a. Higher CPU consumption;
b. Slower data access by non-partitioned index vs. a regular index on non-partitioned table for other DMLs.

Thanks a lot for your help and best regards,
Andrey.

Update test:
Run1 – 5000 updates on non-partitioned table.
Run1 – 5000 updates on partitioned table by disc_number.

Run1 ran in 886 hsecs
Run2 ran in 1177 hsecs
run 1 ran in 75.28% of the time

Name Run1 Run2 Diff
LATCH.post/wait queue 4 3 -1
LATCH.session timer 3 4 1
STAT...calls to get snapshot s 5,004 5,005 1
STAT...cursor authentications 1 0 -1
STAT...deferred (CURRENT) bloc 0 1 1
STAT...cleanout - number of kt 4 5 1
STAT...calls to kcmgcs 3 4 1
STAT...immediate (CURRENT) blo 1 2 1
STAT...redo entries 50,508 50,509 1
LATCH.dummy allocation 2 0 -2
LATCH.job_queue_processes para 2 0 -2
LATCH.transaction allocation 2 0 -2
LATCH.kwqit: protect wakeup ti 0 2 2
LATCH.mostly latch-free SCN 40 42 2
LATCH.list of block allocation 150 148 -2
LATCH.user lock 2 0 -2
STAT...redo synch time 2 0 -2
STAT...consistent gets - exami 15,254 15,256 2
STAT...consistent gets 15,255 15,257 2
STAT...commit txn count during 2 4 2
LATCH.cache buffer handles 5 9 4
STAT...calls to kcmgas 13 8 -5
STAT...write clones created in 11 6 -5
LATCH.channel operations paren 21 27 6
LATCH.lgwr LWN SCN 35 41 6
LATCH.Consistent RBA 34 41 7
STAT...change write time 30 38 8
LATCH.loader state object free 12 24 12
LATCH.FOB s.o list latch 24 11 -13
STAT...consistent changes 511 528 17
LATCH.enqueue hash chains 1,945 1,965 20
STAT...db block changes 101,362 101,392 30
STAT...messages sent 37 85 48
STAT...enqueue releases 5 72 67
STAT...enqueue requests 4 72 68
STAT...recursive cpu usage 268 337 69
LATCH.child cursor hash table 4 118 114
LATCH.session allocation 56 190 134
LATCH.global tx hash mapping 908 757 -151
LATCH.transaction branch alloc 908 757 -151
LATCH.active checkpoint queue 73 242 169
LATCH.redo writing 181 371 190
LATCH.undo global data 299 501 202
STAT...hot buffers moved to he 454 237 -217
LATCH.redo allocation 50,650 50,901 251
LATCH.enqueues 1,890 1,602 -288
LATCH.dml lock allocation 33 323 290
LATCH.library cache pin alloca 1,048 1,340 292
STAT...Elapsed Time 890 1,182 292
LATCH.session idle bit 1,839 2,300 461
LATCH.library cache pin 11,935 12,411 476
LATCH.messages 393 991 598
LATCH.shared pool 6,647 7,320 673
STAT...shared hash latch upgra 3,671 4,378 707
LATCH.library cache 13,627 14,538 911
LATCH.row cache enqueue latch 56,000 55,072 -928
LATCH.row cache objects 56,401 55,364 -1,037
STAT...dirty buffers inspected 505 1,560 1,055
STAT...free buffer inspected 506 1,561 1,055
STAT...physical reads 14,270 17,509 3,239
STAT...free buffer requested 14,623 17,874 3,251
STAT...session uga memory 0 4,264 4,264
LATCH.multiblock read objects 5,313 10,535 5,222
STAT...db block gets 126,535 136,584 10,049
STAT...session logical reads 141,790 151,841 10,051
LATCH.cache buffers lru chain 14,044 24,565 10,521
LATCH.checkpoint queue latch 21,098 47,640 26,542
STAT...session pga memory 60,200 0 -60,200
STAT...redo size 10,106,836 10,241,312 134,476
LATCH.cache buffers chains 572,776 981,340 408,564

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
818,412 1,271,503 453,091 64.37%


Non-partitioned index on partitioned table vs. a regular index on non-partitioned table.
Run1: Select 5000 on partitioned table using Non-partitioned index
Run2: Select 5000 on non-partitioned table using the same index

Run1 ran in 435 hsecs
Run2 ran in 1307 hsecs
run 1 ran in 33.28% of the time

Name Run1 Run2 Diff
STAT...enqueue requests 1 2 1
STAT...shared hash latch upgra 4 5 1
LATCH.kwqit: protect wakeup ti 0 2 2
LATCH.session timer 2 4 2
STAT...active txn count during 4 2 -2
STAT...calls to get snapshot s 3 5 2
LATCH.child cursor hash table 4 8 4
STAT...calls to kcmgcs 4 8 4
STAT...deferred (CURRENT) bloc 0 4 4
STAT...db block gets 522 527 5
LATCH.cache buffer handles 0 6 6
STAT...consistent changes 511 517 6
STAT...hot buffers moved to he 0 7 7
STAT...messages sent 4 12 8
STAT...recursive cpu usage 51 60 9
LATCH.channel operations paren 10 29 19
LATCH.active checkpoint queue 6 32 26
LATCH.Consistent RBA 1 28 27
LATCH.lgwr LWN SCN 1 29 28
LATCH.mostly latch-free SCN 1 29 28
STAT...dirty buffers inspected 2 65 63
STAT...free buffer inspected 2 65 63
STAT...buffer is not pinned co 4,900 4,971 71
STAT...buffer is pinned count 5,100 5,029 -71
LATCH.row cache enqueue latch 12 86 74
LATCH.row cache objects 12 86 74
LATCH.redo writing 13 125 112
LATCH.enqueues 8 140 132
LATCH.dml lock allocation 2 206 204
LATCH.messages 28 288 260
LATCH.enqueue hash chains 10 321 311
STAT...no work - consistent re 6,643 6,959 316
LATCH.session allocation 4 326 322
STAT...session logical reads 7,944 7,497 -447
STAT...consistent gets 7,422 6,970 -452
LATCH.undo global data 768 244 -524
LATCH.redo allocation 1,269 736 -533
LATCH.library cache pin alloca 44 704 660
STAT...db block changes 1,780 1,028 -752
STAT...commit txn count during 759 4 -755
STAT...consistent gets - exami 766 9 -757
STAT...cleanout - number of kt 763 4 -759
STAT...immediate (CR) block cl 759 0 -759
STAT...redo entries 1,265 506 -759
LATCH.shared pool 111 902 791
STAT...Elapsed Time 439 1,312 873
LATCH.library cache pin 69 1,077 1,008
LATCH.cache buffers lru chain 17 1,466 1,449
LATCH.session idle bit 28 1,621 1,593
LATCH.library cache 128 1,844 1,716
STAT...free buffer requested 6,369 4,438 -1,931
STAT...physical reads 6,365 4,434 -1,931
LATCH.checkpoint queue latch 684 4,690 4,006
STAT...prefetched blocks 4,654 0 -4,654
STAT...session uga memory -4,768 0 4,768
LATCH.multiblock read objects 250 7,289 7,039
STAT...redo size 110,508 65,108 -45,400
LATCH.cache buffers chains 25,460 188,818 163,358

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
28,942 211,136 182,194 13.71%

The idea was that data access by local unique index should be faster. Another words single partition unique scan is faster then just unique scan on non-partitioned table.
Is it wrong?
Thanks and regards,
Andrey.

Hi Tom,
Is it possible to create two level of subpartition.
I have a requirement like this.

create tabel Inv_transaction(Inv_date DATE,
Dcp NUMBER)

This table would have more than 10 millions of data.
We have to delete records using date and dcp value.

we have decided to create the main partition using month and year(Range partition) and another level as date(List partition) and another level as DCP(List partition).
DCP column will have value from 1 to 19. If we can create parition like this, we can truncate the parition using date and dcp value instead of deleting.

Is it possible to create such level in oracle?

Tom,

Is it possible to build index for particular partition range. In our warehouse, table grows 60 million rows per day (we have created list partition per day). The retention period is 7 years. We do not have any global index.

There is a requirement for new report which will access last 4 months of data from above table which requires creation of new local index. We do not want to create new local index due to size and growth of the table. Is it possible to create/build index for 'n' partitions? Do you have any suggestions?

Thanks

he can use your "selective index" mecanism - ie, create a FBI index indexing only the records in the desired partitions, no ? This way, the index will be much much smaller AND the desired range of records can be accessed via an index, what appears to be the objective here...

Regards,

Chiappa

Tom,

Index is partitioned on "processing date". Query will filter last 4 months data based on processing date and then will look for particular column which is not indexed yet..

i.e. where processing_date between :p_start_date and :p_end_date and ORIGINATOR = :p_originator and........

Here the index is required on Originator. If we do a full table scan of 4 months (60m x 20 days x 4 months ) I dont think this is a good idea. The other alternative we thought is to create local index, run the report and then drop it. However, still thinking for some clever way of handling the situation

Thanks Tom... we will go with your suggestion.

Hi Tom,

I have a Syntax problem.
I couldnt found how change the number of hash partition
on a table.

For example:

CREATE TABLE t
(
data varchar2(80)
)
PARTITION BY hash (data) partitions 7

insert into t
select 'usa' || object_name from all_objects;

commit;

I want to change it to 8 hash partition.
Could you please show how to do that ?
Thanks.

I am required to create a table that will hold sales data of an Organization for a given country. The table will be holding multi-million records.

Now, in order to enhance Performance, we have decided to parttition a table , say SalesDat.

Now, what we want is to create table partitions in such a way that the sales for each state in the has its own partition (Table/Data will be partitioned on State-wise basis).

All I want to know from you is how can I create a table in such a way that whenever a new set-up (Branch) in new state is opened (i.e, Partition for new aranch is not existent) the table automaitcally creates, takes care of creating a new partition automatically.

Is it possible? If yest can you please kindly explain how?

Thanx in advance.

I have a table with columns

T_ID NUMBER(10),
CTYPE VARCHAR2(10),
VAL_OF_CTYPE VARCHAR2(80)
and some other columns.

Sample Values of above columns are

T_ID CTYPE VAL_OF_CTYPE
---- ----------- ------------
3 MONTH 200101
3 NAME 'A'
2 MONTH 200101
2 NAME 'B'
4 MONTH 200102
4 NAME 'A'
5 MONTH 200102
1 MONTH 200103
1 NAME 'K'

There can be 10 distinct values for CTYPE column.
All or some values of CTYPE are repeated with every t_id. This is a very large table with about 200 million rows.
The generation of values for T_ID is random, eg. 2 doesnt mean its before 3.

I want to partition it such that historic data can be
seperated from current data on the basis of MONTH.
For example, for the above data set, first 4 rows goes in one partition part_jan2001, then next 3 rows goes
in second partition part_feb2001, the next two rows
goes in third partition part_mar2001, and so on.

Yes, we know its generic, and we too hate this type
of model, but my problem is, I am using a 3rd party
application which designed this and I have no right to
change it.

Anyway, is there a way (not like I want it) but some
other way to partition this table so that to get
as much performance as we can.

It is written in documentation that every table
which is greater than 2GB must be considered for
partitioning. It is also written in manual that partitioning
can achieve great performance due to partition pruning,
so how can you say that partition is RARELY about performance.

Also, this table has 200 million rows. Whenever the
optimizer decides for full table scan ignoring
the available indexes, it takes too long to full scan it.

You say many times that "indexes are not always good,
full scns are not always bad". So what is the NUMBER of
rows to be accessed IS on which the optimizer decides
to full scan instead of using index.

We have transactional system. The reason of the large data
size is because we have a large no of customers.
About 1.5 million customers each with 10 months of his
history billing data. We cant say its a dataware house
because we are not doing any such multidimensional analysis
or data mining.
OR
can you tell me what size (no. of rows) is it, exceeding
which, your system cannot be called a transactional system.
200 million table is normal in our system because 10 months
history multiply by 1.5 million = about 200 million.

Please go to the link and search for 2GB
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm

Ok so what we do is "billing our customers" there is not
much like "analysis" so we can call it an OLTP system.

So, does that mean, history of 10 months is too much to
keep online?

The 2 GB thing is just wrong. There is almost no table that cannot be considered for some form of partitioning. Even tables of less than 1Mb can be partitioning candidates if, for example, partitioning can be used to simulate multicolumn statistics on that table's data.

The consideration would be, "in what ways might the system benefit or deteriorate due to various forms of partitioning being applied to this data, and what would be the net effect?". Just as you might consider every column of every table as a candidate for indexing.

Hello,Mr. Tom
I have a question abort oracle composite partition,Why oracle don't support list-list composite partition?
If I have a table
t(id int,type varchar2(10)
ID column have (20,30,50,60) values and TYPE column have 'Y' and 'N' values.
How can I make a list-list composte partiton on this table?

Best Regards!
Alan

We have a table which is range-partitioned based on a date field.

Now, We want to add a higher level partition based on status and sub-partition it using date field.

Status field can have two values '01' - Active & '02' - Closed

One possible approach is to do a range partitioning on status field, and hash partitioning on date field.

but hash partitioning on "date" field will restrict us from deleting old data by simply dropping/truncating old partitions.

Any suggestions?

by the way, Your replies are always wonderful!

Thanks for your reply.

Is range/range partitioning possible in 9i?

Hi Tom,

Our fact table has 50000 daily partitions with 2 million records in each partition. We load the fact table every 15 minutes. Around 95% of load data is new/inserts to on average 10 partitions, and remaining 5% of data is adjustments/updates to on average 40 partitions. We have bitmap indexes on the fact table.

We are currently rebuilding the indexes in a ¿for loop¿ sequentially for all the partitions loaded. This is taking more time than we have to meet our SLAs. Your earlier notes suggest we do not have to rebuild the indexes but that is being not accepted within our team :-(. Is there a way to parallelize rebuild index on selected partitions (as in above case 50 partitions out of 50k) using DBMS_PCLXUTIL.BUILD_PART_INDEX () or in any other way.

Thanks
csk

hi Tom, we are on 10g. Thanks -csk

Thanks Tom for your quick reply. Very good suggestion. Will post back if I see any issues. Thanks for now.

-csk

in reply to one of your responses. How would you handle creating new partitions and dropping off old partitions when the date passes the time retention period?

e.g. I need to remove types B for the 01-Jan-2004 but keep A a little longer.

------------------------------------------------
Followup   December 7, 2004 - 8pm US/Eastern:

ops$tkyte@ORA10GR1> Create Table t
  2  (
  3     code   varchar2(1),
  4     dt     date
  5  )
  6  partition by range(code,dt)
  7  (
  8      partition p1a values less than ( 'A', to_date('01-jan-2004') ),
  9      partition p2a values less than ( 'A', to_date('02-jan-2004') ),
 10      partition p3a values less than ( 'A', to_date('03-jan-2004') ),
 11      partition p1b values less than ( 'B', to_date('01-jan-2004') ),
 12      partition p2b values less than ( 'B', to_date('02-jan-2004') ),
 13      partition p3b values less than ( 'B', to_date('03-jan-2004') ),
 14      partition p1c values less than ( 'C', to_date('01-jan-2004') ),
 15      partition p2c values less than ( 'C', to_date('02-jan-2004') ),
 16      partition p3c values less than ( 'C', to_date('03-jan-2004') ),
 17      partition p1d values less than ( 'D', to_date('01-jan-2004') ),
 18      partition p2d values less than ( 'D', to_date('02-jan-2004') ),
 19      partition p3d values less than ( 'D', to_date('03-jan-2004') )
 20  )
 21  /
 
Table created.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> declare
  2     type vcarray is table of varchar2(1);
  3     l_codes vcarray := vcarray('A','B','C','D');
  4  begin
  5     for i in 1..l_codes.count
  6     loop
  7        for j in 0..2
  8        loop
  9           insert into t values ( l_codes(i), to_date('31-dec-2003')+j );
 10        end loop;
 11     end loop;
 12  end;
 13  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select 'p1a', x.* from t partition (p1a) x union all
  2  select 'p2a', x.* from t partition (p2a) x union all
  3  select 'p3a', x.* from t partition (p3a) x union all
  4  select 'p1b', x.* from t partition (p1b) x union all
  5  select 'p2b', x.* from t partition (p2b) x union all
  6  select 'p3b', x.* from t partition (p3b) x union all
  7  select 'p1c', x.* from t partition (p1c) x union all
  8  select 'p2c', x.* from t partition (p2c) x union all
  9  select 'p3c', x.* from t partition (p3c) x union all
 10  select 'p1d', x.* from t partition (p1d) x union all
 11  select 'p2d', x.* from t partition (p2d) x union all
 12  select 'p3d', x.* from t partition (p3d) x;
 
'P1 C DT
--- - ---------
p1a A 31-DEC-03
p2a A 01-JAN-04
p3a A 02-JAN-04
p1b B 31-DEC-03
p2b B 01-JAN-04
p3b B 02-JAN-04
p1c C 31-DEC-03
p2c C 01-JAN-04
p3c C 02-JAN-04
p1d D 31-DEC-03
p2d D 01-JAN-04
p3d D 02-JAN-04
 
12 rows selected.
 

That is just like a LIST with a RANGE underneath it. 

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  ( dt DATE,
  3    code VARCHAR2(1),
  4    data varchar2(20)
  5  )
  6  PARTITION BY RANGE (dt)
  7  SUBPARTITION BY LIST (code)
  8  SUBPARTITION TEMPLATE
  9  (
 10            SUBPARTITION a_stuff VALUES('A'),
 11            SUBPARTITION b_stuff VALUES('B'),
 12            SUBPARTITION c_stuff VALUES('C')
 13  )
 14  (
 15  PARTITION jan1 VALUES LESS THAN (TO_DATE('2-jan-2009','DD-MON-YYYY')),
 16  PARTITION jan2 VALUES LESS THAN (TO_DATE('3-jan-2009','DD-MON-YYYY')),
 17  PARTITION jan3 VALUES LESS THAN (TO_DATE('4-jan-2009','DD-MON-YYYY'))
 18  );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t (dt, code, data )
  2  select to_date( '01-jan-2009', 'dd-mon-yyyy') + trunc( dbms_random.value( 0, 2.99999999 ) ),
  3         chr( ascii('A')+ trunc( dbms_random.value( 0, 2.9999999 ) ) ),
  4             'x'
  5    from dual connect by level <= 10000
  6  /

10000 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select 'JAN1_A_STUFF', count(*) from t SUBPARTITION(JAN1_A_STUFF) union all
  2  select 'JAN1_B_STUFF', count(*) from t SUBPARTITION(JAN1_B_STUFF) union all
  3  select 'JAN1_C_STUFF', count(*) from t SUBPARTITION(JAN1_C_STUFF) union all
  4  select 'JAN2_A_STUFF', count(*) from t SUBPARTITION(JAN2_A_STUFF) union all
  5  select 'JAN2_B_STUFF', count(*) from t SUBPARTITION(JAN2_B_STUFF) union all
  6  select 'JAN2_C_STUFF', count(*) from t SUBPARTITION(JAN2_C_STUFF) union all
  7  select 'JAN3_A_STUFF', count(*) from t SUBPARTITION(JAN3_A_STUFF) union all
  8  select 'JAN3_B_STUFF', count(*) from t SUBPARTITION(JAN3_B_STUFF) union all
  9  select 'JAN3_C_STUFF', count(*) from t SUBPARTITION(JAN3_C_STUFF);

'JAN1_A_STUF   COUNT(*)
------------ ----------
JAN1_A_STUFF       1117
JAN1_B_STUFF       1126
JAN1_C_STUFF       1109
JAN2_A_STUFF       1150
JAN2_B_STUFF       1104
JAN2_C_STUFF       1108
JAN3_A_STUFF       1107
JAN3_B_STUFF       1071
JAN3_C_STUFF       1108

9 rows selected.

ops$tkyte%ORA10GR2> alter table t drop subpartition jan1_b_stuff;

Table altered.

ops$tkyte%ORA10GR2> select count(*) from t;

  COUNT(*)
----------
      8874

(forgot to say) and adding new partitions for each of the types also with later dates

e.g. adding 08-JAN-2004 for both Type A and Type B

Thanks for the speedy response, I was kind of hoping I wouldn't have to change the SUBPARTITIONing that I have, but I guess its back to the drawing board.

Thanks again I appreciate the time you spend answering the many questions.

Cheers

Jo

CREATE TABLE mp_revenue_and_pipe2
(reporting_date DATE NOT NULL ,
reporting_cycle VARCHAR2(10) NOT NULL ,
value_type VARCHAR2(20) NOT NULL ,
expectedfees NUMBER,
expenses_usd NUMBER,
actual_revenue NUMBER,
CHECK (value_type IN ('Pipeline','Revenue','FPMS')))
PARTITION BY RANGE (reporting_cycle,reporting_date)
SUBPARTITION BY LIST (value_type)
SUBPARTITION TEMPLATE
(SUBPARTITION pipeline VALUES ('Pipeline') TABLESPACE IRP_DATA,
SUBPARTITION revenue VALUES ('Revenue' ) TABLESPACE IRP_DATA,
SUBPARTITION OTHERS VALUES (DEFAULT) TABLESPACE IRP_DATA)
(
PARTITION h1_2007 VALUES LESS THAN ('HALF1' ,TO_DATE('01-JUL-2007','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h1_2008 VALUES LESS THAN ('HALF1' ,TO_DATE('01-JUL-2008','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h1_2009 VALUES LESS THAN ('HALF1' ,TO_DATE('01-JUL-2009','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h1_2010 VALUES LESS THAN ('HALF1' ,TO_DATE('01-JUL-2010','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h1_2011 VALUES LESS THAN ('HALF1' ,TO_DATE('01-JUL-2011','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h1_2012 VALUES LESS THAN ('HALF1' ,TO_DATE('01-JUL-2012','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h1_2013 VALUES LESS THAN ('HALF1' ,TO_DATE('01-JUL-2013','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h2_2007 VALUES LESS THAN ('HALF2' ,TO_DATE('01-JAN-2008','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h2_2008 VALUES LESS THAN ('HALF2' ,TO_DATE('01-JAN-2009','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h2_2009 VALUES LESS THAN ('HALF2' ,TO_DATE('01-JAN-2010','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h2_2010 VALUES LESS THAN ('HALF2' ,TO_DATE('01-JAN-2011','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h2_2011 VALUES LESS THAN ('HALF2' ,TO_DATE('01-JAN-2012','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h2_2012 VALUES LESS THAN ('HALF2' ,TO_DATE('01-JAN-2013','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h2_2013 VALUES LESS THAN ('HALF2' ,TO_DATE('01-JAN-2014','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION h2_2014 VALUES LESS THAN ('HALF2' ,TO_DATE('01-JAN-2015','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q1_2007 VALUES LESS THAN ('QUARTER1',TO_DATE('01-APR-2007','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q1_2008 VALUES LESS THAN ('QUARTER1',TO_DATE('01-APR-2008','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q1_2009 VALUES LESS THAN ('QUARTER1',TO_DATE('01-APR-2009','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q1_2010 VALUES LESS THAN ('QUARTER1',TO_DATE('01-APR-2010','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q1_2011 VALUES LESS THAN ('QUARTER1',TO_DATE('01-APR-2011','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q1_2012 VALUES LESS THAN ('QUARTER1',TO_DATE('01-APR-2012','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q1_2013 VALUES LESS THAN ('QUARTER1',TO_DATE('01-APR-2013','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q1_2014 VALUES LESS THAN ('QUARTER1',TO_DATE('01-APR-2014','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q2_2007 VALUES LESS THAN ('QUARTER2',TO_DATE('01-JUL-2007','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q2_2009 VALUES LESS THAN ('QUARTER2',TO_DATE('01-JUL-2009','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q2_2010 VALUES LESS THAN ('QUARTER2',TO_DATE('01-JUL-2010','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q2_2011 VALUES LESS THAN ('QUARTER2',TO_DATE('01-JUL-2011','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q2_2012 VALUES LESS THAN ('QUARTER2',TO_DATE('01-JUL-2012','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q2_2013 VALUES LESS THAN ('QUARTER2',TO_DATE('01-JUL-2013','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q2_2014 VALUES LESS THAN ('QUARTER2',TO_DATE('01-JUL-2014','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q3_2007 VALUES LESS THAN ('QUARTER3',TO_DATE('01-OCT-2007','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q3_2008 VALUES LESS THAN ('QUARTER3',TO_DATE('01-OCT-2008','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q3_2009 VALUES LESS THAN ('QUARTER3',TO_DATE('01-OCT-2009','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q3_2010 VALUES LESS THAN ('QUARTER3',TO_DATE('01-OCT-2010','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q3_2011 VALUES LESS THAN ('QUARTER3',TO_DATE('01-OCT-2011','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q3_2012 VALUES LESS THAN ('QUARTER3',TO_DATE('01-OCT-2012','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q3_2013 VALUES LESS THAN ('QUARTER3',TO_DATE('01-OCT-2013','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q3_2014 VALUES LESS THAN ('QUARTER3',TO_DATE('01-OCT-2014','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q4_2007 VALUES LESS THAN ('QUARTER4',TO_DATE('01-JAN-2008','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q4_2008 VALUES LESS THAN ('QUARTER4',TO_DATE('01-JAN-2009','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q4_2009 VALUES LESS THAN ('QUARTER4',TO_DATE('01-JAN-2010','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q4_2010 VALUES LESS THAN ('QUARTER4',TO_DATE('01-JAN-2011','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q4_2011 VALUES LESS THAN ('QUARTER4',TO_DATE('01-JAN-2012','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q4_2012 VALUES LESS THAN ('QUARTER4',TO_DATE('01-JAN-2013','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q4_2013 VALUES LESS THAN ('QUARTER4',TO_DATE('01-JAN-2014','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION q4_2014 VALUES LESS THAN ('QUARTER4',TO_DATE('01-JAN-2015','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION yr_2007 VALUES LESS THAN ('YEAR' ,TO_DATE('01-JAN-2008','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION yr_2008 VALUES LESS THAN ('YEAR' ,TO_DATE('01-JAN-2009','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION yr_2009 VALUES LESS THAN ('YEAR' ,TO_DATE('01-JAN-2010','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION yr_2010 VALUES LESS THAN ('YEAR' ,TO_DATE('01-JAN-2011','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION yr_2011 VALUES LESS THAN ('YEAR' ,TO_DATE('01-JAN-2012','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION yr_2012 VALUES LESS THAN ('YEAR' ,TO_DATE('01-JAN-2013','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION yr_2013 VALUES LESS THAN ('YEAR' ,TO_DATE('01-JAN-2014','DD-MON-RRRR')) TABLESPACE IRP_DATA,
PARTITION yr_2014 VALUES LESS THAN ('YEAR' ,TO_DATE('01-JAN-2015','DD-MON-RRRR')) TABLESPACE IRP_DATA
)
/

Hello Tom and thanks for the extremely useful information you provide through this site.

In an earlier post in this thread you said:
"I don't get how you would effectively use a "list" on date. Just RANGE on date."

In a number of databases I have worked with there are columns of type DATE that store a "business" date. These business dates never include a time component (i.e., the time component is always "00:00:00"). Because they are DATE data type they typically get set up with RANGE partitions. I think the actual data in these columns argue for a LIST partition.

It seems a LIST partition in this case would allow the CBO to perform simple partition pruning and be done with the business date filter because it knows everything in the partition matches.

For the RANGE partition, the CBO has additional steps after the partition pruning. The CBO has to look at the statistics for this partition to decide how much data inside the partition matches. And if you don't have good columns stats or histogram showing the terrible data skew in this business date column you could get a bad plan.

That being said going with a LIST partition on a DATE data type seems very unusual and worries me that this may be somewhat untested Oracle waters (fyi, we are running 10.2.0.4).

Assuming a typical query always filters on 1 business date and you could not change the data type (we cannot because it would require changing many tables and many interfaces) how would you partition this type of table? What do you see as the advantages/disadvantages of one approach over the other? Any pitfalls or gotchas to watch out for?

Thank you for your time. James

Does creating non partitioned index on a partitioned table hamper performance or manageability in any way...

I would like to create composite Range-Range partition with Interval clause. Is it possible?

CREATE TABLE test_table
(
TABLE_ID1 NUMBER(*,0) NOT NULL ENABLE,
TABLE_ID2 NUMBER(*,0) NOT NULL ENABLE,
OTHER COLUMNS ..............................
TRANS_DATETIME DATE NOT NULL ENABLE,
SETTLEMENT_DATE DATE NOT NULL ENABLE,

)
PARTITION BY RANGE (SETTLEMENT_DATE) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
SUBPARTITION BY RANGE (TRANS_DATETIME) INTERVAL(NUMTOYMINTERVAL(1, 'YEAR'))
SUBPARTITION TEMPLATE
(SUBPARTITION SUB_TH_P1 VALUES LESS THAN (TO_DATE('01-01-2011', 'DD-MM-YYYY')),
SUBPARTITION SUB_TH_P2 VALUES LESS THAN (TO_DATE('01-01-2012', 'DD-MM-YYYY')))
( PARTITION TH_P1 VALUES LESS THAN (TO_DATE('01-01-2011', 'DD-MM-YYYY')),
PARTITION th_p2 VALUES LESS THAN (TO_DATE('01-01-2012', 'DD-MM-YYYY'))
);

Hi

I have to partition a table using range partitiong with a date then subpartition by city code (ranges from 1 to 34) using list.

What is best, range partitioning using composite key with date, city code or it is best using range-list subpartitioning? I cant think of any difference really!

Thanks in advance

David