Thanks Tom!

I am actually partitioning on a unique key that is the most highly queried (WHERE 'partitioning_key_used=X') so I guess I should be fine from that side.

I will further investigate on the volumes and use that to decide on the number of partitions. I will also order your book next thing after submitting this review :).

Thanks again!

Hi Tom,

You have mentioned about increase in the work done by a local partioned index and recommended not to partition the index as an option.

<quote>
you will do 16 index range scans - you will do 16 times the work!

you would have wanted to either

a) not partition that index
b) partition that index by range - A-K go into this partition, L-Z go into this one
c) hash partition it (if you always use equals)

</quote>

We have a heavily inserted (100s of records every second round the clock) table which is range partitioned for the purpose of purging old data. The table has few non partitioned indexes for the purpose of fast query response. They are not local partitioned for the very reason you have mentioned.

But during dropping of old partition, while the non partitioned indices are rebuilt, there is contention for the indices (enq-TX index contention). As the table is highly inserted table this causes "issues" in the application.

Hash partitioning the index might not be appropriate since queries will not use equals. Do we have any alternative? Or is it a compromise between query response time against partition drop time?

Regards,

Albert Nelson A.

Tom, can you please say a bit more about the algorithm used to determine which hash partition to use for a specific key? I'm having trouble understanding why a power of two would be important. We're considering implementing hash partitioning on a completely unique column, and it seems to me as if it should be easy to keep that evenly hash partitioned without having to resort to a specific number of partitions.

ops$tkyte%ORA10GR2> CREATE TABLE t1 ( x   int ) PARTITION BY hash(x) ( partition part1 , partition part2 , partition part3 , partition part4 );

Table created.

ops$tkyte%ORA10GR2> CREATE TABLE t2 ( x   int ) PARTITION BY hash(x) ( partition part1 , partition part2 , partition part3 , partition part4, partition part5 );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 select rownum from all_objects;

49961 rows created.

ops$tkyte%ORA10GR2> insert into t2 select rownum from all_objects;

49961 rows created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select h, count(*), sum(count(*)) over (),
  2            substr( rpad('*',100,'*'), 1, 100*ratio_to_report(count(*)) over ()) hist
  3    from (select 1 h from t1 partition (part1)
  4          union all select 2 h from t1 partition(part2)
  5          union all select 3 h from t1 partition(part3)
  6          union all select 4 h from t1 partition(part4)
  7                  )
  8   group by h
  9   order by h
 10  /

         H   COUNT(*) SUM(COUNT(*))OVER() HIST
---------- ---------- ------------------- --------------------------------------------------
         1      12435               49961 ************************
         2      12430               49961 ************************
         3      12719               49961 *************************
         4      12377               49961 ************************

ops$tkyte%ORA10GR2> select h, count(*), sum(count(*)) over (),
  2            substr( rpad('*',100,'*'), 1, 100*ratio_to_report(count(*)) over ()) hist
  3    from (select 1 h from t2 partition (part1)
  4          union all select 2 h from t2 partition(part2)
  5          union all select 3 h from t2 partition(part3)
  6          union all select 4 h from t2 partition(part4)
  7          union all select 5 h from t2 partition(part5)
  8                  )
  9   group by h
 10   order by h
 11  /

         H   COUNT(*) SUM(COUNT(*))OVER() HIST
---------- ---------- ------------------- --------------------------------------------------
         1       6243               49961 ************
         2      12430               49961 ************************
         3      12719               49961 *************************
         4      12377               49961 ************************
         5       6192               49961 ************

Tom-

I realize I'm about to shoot the messenger here, so let apologize in advance. Why, why, why, would Oracle push a restriction on the number of hash partitions as restrictive as the powers of two? It seems much simpler and more flexible to hash the key to an integer and use modulo arithmetic to determine which hash partition to use for the data. Can you explain why that algorithm does not work?

Hi Tom,

In your followup on July 9, 2008 - 10am US/Eastern, you have asked

<quote>
when you drop the old partitions, you are maintaining the indexes - so in effect, it is like you are issuing a delete against the indexes

Can you clarify a little more why this would in your case cause the contention you describe.

</quote>

Sorry I am unable to clarify further. It is observed in the OEM as the waiting event experienced by lots of active sessions trying to insert into the table whose partition was being dropped.

Thanks and regards,

Albert Nelson A.

Hi Tom,

No, they are not unique indexes.

Regards,

Albert Nelson A

Hi Tom,

I did not understand what you meant by entire schema but here is the DDL for the table and its indexes:


CREATE TABLE "CITYNET2"."ESC_VEHICLE_IVD_LOG"
( "VEHICLE_ID" VARCHAR2(10 BYTE) NOT NULL ENABLE,
"JOB_NO" VARCHAR2(12 BYTE),
"HEADER_INFO" RAW(12),
"DETAIL" VARCHAR2(60 BYTE),
"EVENT_CODE" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"LOG_DT" DATE NOT NULL ENABLE,
"DRIVER_ID" VARCHAR2(9 BYTE),
"EVENT_STATUS" NUMBER(1,0),
"EVENT_DT" DATE,
CONSTRAINT "FK_ESC_VEH_IVD_LOG__EVNT" FOREIGN KEY ("EVENT_CODE")
REFERENCES "CITYNET2"."ESC_EVENT_CODES" ("EVENT_CODE") DISABLE,
CONSTRAINT "FK_ESC_VEH_IVD_LOG__VEH" FOREIGN KEY ("VEHICLE_ID")
REFERENCES "CITYNET2"."ESC_VEHICLE" ("VEHICLE_ID") ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "CN2TABPART1"
PARTITION BY RANGE ("LOG_DT")
(PARTITION "IVD_LOG_20080713" VALUES LESS THAN (TO_DATE(' 2008-07-13 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2TABPART1" NOCOMPRESS ,
PARTITION "IVD_LOG_20080720" VALUES LESS THAN (TO_DATE(' 2008-07-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2TABPART1" NOCOMPRESS ,
/* Other partitions deleted for brevity */
PARTITION "IVD_LOG_20080907" VALUES LESS THAN (TO_DATE(' 2008-09-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2TABPART1" NOCOMPRESS ,
PARTITION "IVD_LOG_OTHERS" VALUES LESS THAN (MAXVALUE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2TABPART1" NOCOMPRESS ) ;


CREATE INDEX "CITYNET2"."IDX_ESC_VEH_IVD_LOG__EVN_COD" ON "CITYNET2"."ESC_VEHICLE_IVD_LOG" ("EVENT_CODE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2IND3" ;

CREATE INDEX "CITYNET2"."IDX_ESC_VEH_IVD_LOG__JOB_NO" ON "CITYNET2"."ESC_VEHICLE_IVD_LOG" ("JOB_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2IND3" ;

CREATE INDEX "CITYNET2"."IDX_ESC_VEH_IVD_LOG__LOG_DT" ON "CITYNET2"."ESC_VEHICLE_IVD_LOG" ("LOG_DT")
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE "CN2INDPART1" LOCAL
(PARTITION "IVD_LOG_20080713"
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2INDPART1" ,
PARTITION "IVD_LOG_20080720"
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2INDPART1" ,
/* Other partitions deleted for brevity */
PARTITION "IVD_LOG_20080907"
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2INDPART1" ,
PARTITION "IVD_LOG_OTHERS"
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2INDPART1" ) ;

CREATE INDEX "CITYNET2"."IDX_ESC_VEH_IVD_LOG__VEH_ID" ON "CITYNET2"."ESC_VEHICLE_IVD_LOG" ("VEHICLE_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2IND3" ;

CREATE INDEX "CITYNET2"."IDX_VEH_IVD_LOG__DRV_ID$DT" ON "CITYNET2"."ESC_VEHICLE_IVD_LOG" ("DRIVER_ID", "LOG_DT")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "CN2IND2" ;

Regards,

Albert Nelson A.

Hi Tom,

I was trying to figure out hashing algorithm used by Oracle to allocate rows to a particular hash partition. Then I stumbled upon ORA_HASH function.

I observed below behavior

Consider, these 4 variables (part_col, no_of_part, bucket_size, prev_2_power)

part_col be candidate partitioning column

no_of_part is the number of partitions we are considering

bucket_size is derived from no_of_part as follows:

if no_of_part is powers of 2(2,4,8,16...) then
bucket_size = no_of_part - 1

else if no_of_part not powers of 2 then
bucket_size = "next higher power of 2" - 1

for eg, if you have 24 no_of_part then (32 -1) = 31

prev_2_power is no_of_part if its a power of 2 number else its previous power of 2. for ex. for 24 partitions, 16 would be prev_2_power

THEN
Partition Number =

case
ora_hash(part_col, bucket_size)+ 1 > no_of_part
then 
ora_hash(part_col, bucket_size)+ 1 - prev_2_power
else
ora_hash(part_col, bucket_size)+ 1 /* +1 since bucket start with 0 but partitions start with 1 */
end;

I tested above algo for couple of cases and it worked correct. Also it explains why there is skew if we dont use power of 2 partitions. as anyways oracle bucket data into next 2power number of buckets and then just merges them using above logic to make desired no of partitions.

Above expression can be used along with a group by to evaluate partitioning skew before actually creating partitioned table.

I am not sure though that if this the logic or documented somewhere but seems to hold good.

PS: Pardon for re-posting this. I had posted it on "hash partitioning" thread but I got no response there. Seems that thread is inactive.

Regards,
Saurabh

Hi Tom,
excuce me for my english, I have a table of 3.000.000 of record with comporite partition by range/hash (2/16) that I must join with tables of thousand records partitioned same way.
How many partition I can add for the best performance query and load data?

Myke

Hi Tom,

We have two audit tables (VPD enabled) which stores all OLTP transactions information and there are views on top them to fetch point in time data with heavy no of columns and more complex business logic. These views are used by batch jobs to pull the report information and batch jobs are spending most of the time(80% of the time) to query DB.

Table1 is having 10 million records (stores what was table changed, on what date and some additional info)

Table2 is having 50 million records (refers to Table1 and contains list of columns that were changed for that transaction and stores old value and new value for that column)

I wanted to implement hash partitioning for these tables based on client_key value to decrease the query response times.

I have following questions.

1) When we set the context for a session, that session can see data only for that context. In a way Oracle filters out the data by looking only for that context variable (what I meant is though my table is having millions of records it only look for the records for that context maybe I m wrong), does partitioning on VPD enabled tables will really improves the performance?

2) Based on what logic Oracle keeps the records in different partitions for hash partitioning, let’s assume that I have 8 partitions and 8 clients with different client_keys for each client. If I implement hash partitioning on client_key does Oracle keeps the records in same partition those who belongs to same client_key?

3) How many partitions will be better in this case?

4) Sometimes we are facing ORA-06910 Sort area size too small issues from application, however workarea_size_policy is set to “auto”. Ideally Oracle should ignore sort_area_size parameter value, if workarea_size_policy is set to auto, in that case what will be maximum sort area size can be allocated for a session?

5) If a query involves too much sorting may be it should take more time to execute it (by doing that sort operation in disk and sending that result back to buffer if sort area size is not enough), though workarea_size_policy is set to auto, how come we are receiving ORA-06910 error, is there any other property which is limiting the sort area size or is there something wrong on DB queries or from application? Could you please suggest some areas to focus to not get that error, because of this our jobs getting failed without executing completely.

Thanks in Advance

Thanks,
Siva.

Hi Tom,

Thanks for your reply and sorry for my late response.

There are no full table scans on both of the tables, Explain plan shows "TABLE ACCESS BY LOCAL INDEX ROWID" on both the tables.

Just wanted to know, how partitioning won't help if there are no full table scan.

Thanks,
Siva.

Hi Tom,

Could you please help me out here, we are really suffering from the query response times.

I have tried by applying partition on those tables by creating replica of them, I have seen significant improvement (from 00:4:30 to 00:2:30 sec) on query response time on new partitioned tables.

Well, this is how I implemented the partitioning; I created 8 partitions for Table1 (including its indexes) and 16 partitions for Table2 (including its indexes) and Hash Partitioning on Client_Key. In a way all the information for one Client is going to store in one and only one single partition.

So, if we run a query on those tables by applying VPD on Client_Key, then what I m assuming is Table/ Index scan happens only within the single partition where that CLIENT_KEY is located. So in this way we minimized the no of records to be searched/ scanned/ filtered (Since only one partition will be involved in at any point of time). Is this the way partitioning works or Am I wrong here?

If I m wrong, how the query response time came down to 00:2:30 from 4 mins?

Note: I calculated time taken for the query after flushing Shared Pool and Buffer cache.

Thanks,
Siva.

Hi Tom - I have a non-partitioned table with 250 million rows. The table has a not-null 'DATASET_ID' column. On average about 75,000 rows will share the same DATASET_ID value, so we have roughly 3,333 unique DATASET_IDs in the table.

Our application frequently needs to select all the rows for a given DATASET_ID, so I'm thinking of hash partitioning this table on DATASET_ID. All the queries we execute against this table include 'DATASET_ID=x' in the where clause, so every select would hit only a single partition.

Is there a reason I shouldn't create a large number of partitions if I know all my queries will be based on DATASET_ID? For example, if I create 1024 partitions then there should be about 3 DATASET_IDs per partition so I would expect a full-partition scan would be a pretty efficient way to retrieve the rows given a DATASET_ID. Or why not go with 4096 partitions and have about 1 DATASET_ID per partition?

Thanks...


Luke

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    x   int
  4  )
  5  tablespace ptest
  6  PARTITION BY hash(x) partitions 4096
  7  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> insert into t select level from dual connect by level <= 3333;

3333 rows created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> declare
  2          l_sql clob := q'|
  3          select cnt, count(*) cnt_with_that_cnt
  4            from (
  5          select part#,
  6                 count(*) cnt
  7            from (|';
  8  
  9          l_str long := null;
 10  begin
 11          for x in (select partition_name, PARTITION_POSITION
 12                      from user_tab_partitions
 13                             where table_name = 'T'
 14                     order by PARTITION_POSITION )
 15          loop
 16                  l_str := l_str || ' select ' || x.PARTITION_POSITION || ' part#
 17                                        from t partition(' || x.partition_name || ') ';
 18                  dbms_lob.writeAppend( l_sql, length(l_str), l_str );
 19                  l_str := ' union all ';
 20          end loop;
 21          l_str := ' ) group by part# ) group by cnt order by cnt';
 22          dbms_lob.writeAppend( l_sql, length(l_str), l_str );
 23  
 24  
 25          open :x for l_sql;
 26  end;
 27  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> print x

       CNT CNT_WITH_THAT_CNT
---------- -----------------
         1              1498
         2               572
         3               173
         4                33
         5                 8

ops$tkyte%ORA11GR2> insert into t select floor(dbms_random.value(1,1000000)) from dual connect by level <= 3333;


ops$tkyte%ORA11GR2> print x

       CNT CNT_WITH_THAT_CNT
---------- -----------------
         1              1507
         2               578
         3               164
         4                42
         5                 2

3K is not a huge number. Wouldn't it be better to list partition this table and have all rows with the same DATASET_ID in one segment? Given the name, it might even be loaded as a DATASET at the time ...

Greetings Tom,

I have a question on your latest follow up.

I have a table of about 16 million and growing, with pretty huge columns, but no unique keys. Retention is one year and it is batch loaded once every month, sometimes twice a month.

There is a column TRANS_ID(sequence generated number) that is same for all records loaded into the table during a single batch insert transaction.

At this point the volume of records loaded per batch is pretty skewed, sometimes it's huge (2-3 million), sometimes it's as little as 20k records.

Is List partitioning better or Hash for this table using the TRANS_ID key?