A reader, May 24, 2004 - 11:21 am UTC
Tom,
So if understand it correct then i will be opting for global index on the primary key and local index for ACCT_BY_DATE column.
TIA
May 24, 2004 - 11:24 am UTC
you have no choice but a global index on the primarykey.
on the acct_by_date column -- that is entirely up to you as to how or even IF you partition it/index it. that can be done local or global
A reader, May 24, 2004 - 11:25 am UTC
Locks or Latches
Neeraj Nagpal, May 24, 2004 - 2:46 pm UTC
"Hence, any insert would have to check ALL local partitions
for 55 and lock each index partition to ensure no one even tries to insert ACCT_ID=55 right now"
Do mean there would be real locks to lock each index partition ? or it is some latch which is the "Scalability Inhibitor" here.
May 24, 2004 - 4:06 pm UTC
it means N-1 of N index partitions would have to be locked, in an exclusive mode, for the duration of the transaction to prevent 55 from being inserted into them.
1 of the N index partitions could be treated "as normal", but it wouldn't really matter since no one else could insert any other value (they could not lock the other index partitions themselves)
It would be a theoretical heavy duty lock -- it doesn't happen in reality that way
what to partition on
Karma, May 24, 2004 - 8:37 pm UTC
I get trading file from mainframe daily with around 200-300K rows. I need to keep minimum 2 years worth of data and the key i have on this table would be julian date+seq_num which is only good for unique ness because data is never accessed using this values.
THis table is very wide ( roughly 120 columns) along with as_of_date , enter_dt, trade_dt, settle_dt. Most of the time data is accessed only by either enter_dt or trade_dt or settle_dt. as_of_Date just tells me that data is of which date.
I just dont know how to partition this table and how to index.
ANy help?
May 24, 2004 - 11:51 pm UTC
nope.
you didn't mention what you goal with partitioning would be (administrative, faster query, what)...
you didn't mention if there is any relation between enter/trade/settle_dt's.
you didn't mention which date you use to decide "it is older than 2 years"
you need to sit down and figure out what you are trying to accomplish with partitioning...
how you use the data....
and then it might become more clear how to partition to achieve the goal without affecting how you use it (performance wise) too much.
Number of Partitions
MEHMOOD, May 25, 2004 - 5:37 am UTC
Dear Tom:
Actually I have one table i.e. Transactions. I have partitioned it in 2, on the basis of company_code. Company_code 1 have 5 million records and Company_code 2 have 3 million records. The partiotion key i.e. Company_code is a part of primary key. So as you said it has the local index. My queries against this table is so poor. Should i make some more partitions i.e. composite partitons?? How can i make queries fast enough against this table??
May 25, 2004 - 7:36 am UTC
partitioning is NOT like setting "fast=true"
why are your queries slow -- is it because they retrieve N million records? no degree of partitioning will speed that up.
Partitioning can, when inappropriately|appropriately applied, make a system run many orders of magnitude slower|faster -- or have no affect whatsoever on performance.
You need to look at your queries as say "how could I use this tool -- in fact all tools Oracle has -- to make this go faster". Think about the physics of the data, what partitioning could/would do and whether that would have any material affect on your query performance - positive or negative.
what to partition on
karma, May 25, 2004 - 6:39 am UTC
Goal is
1) Make data available for 2 years online. i.e drop data older then 2 years. Older data to be dropped based on as_of_date but data is never accessed by user based on this date.
2) Performance and Performance. Users will always access data from this table either by enter_d, trade_d or settle_D along with acct_id. So users query will say give me data for a given acct where enter_d = sysdate or settle_d = sysdate+1. Or users will say give me all data where trade_d = sysdate. Any combinations. Thus it makes it difficult to figure out what to partiton on.
3) Data will be loaded daily ( 200-300K) All inserts but accumulate up to 2 years.
4)Data is never accessed by julian date+ seq_num. Useless PK.
5) Relationship between this dates. trade_d = date trade was executed. enter_d = date it was entered in the system. But they could go back and adjust this date. So enter_d could be less then trade_d. Settle_d = date trade was settled which is greater then or equal to trade_dt since you cannot settle a trade which is not traded.
So is partioning going to help here or cause more problems? What should be correct approach?
May 25, 2004 - 7:44 am UTC
1,2,3) sounds like table is range partitioned on AS_OF_DATE to facilitate purging.
indexes would be GLOBAL -- and you would use "update global indexes" during partition operations:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:14473000745047 <code>
4) i would not even index it then, doesn't seem relevant to your system?
with the range partitioned table, purging is made as easy as possible.
with global indexes -- either in a single partition or partitioned by their own ranges -- performance will not be affected at runtime in a negative fashion.
what to partition on
Karma, May 25, 2004 - 6:54 am UTC
One more thing i.e. settle_d could be NULL where a thinly traded stock trade could not be settled for a long time.
Also users will be joining with other large tables to get final report.
partition or not
karma, May 25, 2004 - 10:00 am UTC
o.k that makes sense but what would be global index on? Data is only accessed through either enter_d or trade_d or settle_d + account_id but never by as_of_date.
So if we range partitioned by as_of_date then does'nt this have to be part of global index? and if include them in index ( account_id + as_of_date + enter_d + trade_d + settle_d + julian_d + seq_n ) then how would optimizer use index since query will always say give me data by account_id + ( either or all trade_d , enter_d , settle_d)
What would global index look like so that data is accessed faster and partition pruning is done as well.
May 25, 2004 - 11:14 am UTC
you will be creating indexes.
say one on (account_id,settle_d)
another on (account_id,trade_d)
and maybe on one (account_id,enter_d)
the table is range partitioned on as_of_date.
the three indexes would be range partitioned on either
column1
column2
column2/column1
column1/column2
upto you. a global index by definition is partitioned *differently* than the underly table is partitioned.
the partition pruning that would take place for:
select * from t where account_id = 5 and enter_d = sysdate;
would be on the index -- only one index would be range scanned, and then the rowids would be used to retrieve the data from any partition it might be in.
If you had a LOCAL index on (account_id,enter_d), that query would not be able to prune ANY partitions, you would have to peek at them all.
If you have my book "Effective Oracle By Design", I do cover this topic in much more detail.
What to partition on
karma, May 29, 2004 - 12:38 pm UTC
Thanks For great explanation. I have already order your book and eagerly waiting to read chapter on partitioning and global indexes.
One thing is not clear to me as to what is the difference between global indexes and global partiotioned index. When and why would use global partitioned index?
Also, our DBA mentioned that we should use global index and also have local index for table partition. I am not sure how this would work ? How will it help for performance? I searched for example on this site but nothing is available. Appreciate if you can explain with example
Thanks
May 30, 2004 - 10:10 am UTC
global index and global partitioned index are one in the same.
a local index means there is a 1 to 1 relationship between index partitions and table partitions. If you have 10 table partitions you will have 10 index partitions. Further, the index entries in partition 1 will ONLY point to rows in partition 1 and so on (like having 10 tables with 10 indexes all unioned together)
a global index doesn't mean either of those two things. the table is partitioned by whatever rules make sense for it, the index likewise. So you could have 10 table partitions and only 2 index partitions. Further, the data in index partition 1 might point to data in any number (from 0 to 10) of partitions of the table.
concurrency during "drop partition ... update global indexes"
David, July 02, 2004 - 6:56 pm UTC
Tom,
I have high-volume OLTP database where I partitioned tables by date to facilitate purging. All non-date -realted indexes are global non-paritioned.
Since update global indexes operation takes very long time (my tables are ~4-25 Gigagbyte in size ),
I'm concerned about locking/other concurrency issues on the table where "drop partition ... update global indexes" takes place, because people continue update/insert the table on pretty high rate (60-80 dml ops per second per table).
Looking through the oracle docs I didn't found clear statment saying that dml is allowed against the table being "drop partition"-ed (is the shared table lock being used during drop partition?)
Thank you for you time,
David
July 03, 2004 - 10:07 am UTC
well, it is not the aggregate size of the tables that is the issue, it is the individual size of the partitions -- if you slice it small enough, each drop will not be that "large"
but as long as the partition being dropped has no active transactions going against it (you'll get ora-54 if it does during the drop), the drop will naturally take place -- even with concurrent/outstanding DML against other partitions. The update global indexes is done via DML like operations (as if a delete took place)
Primary key index in Partitioning
William Wong, July 06, 2004 - 12:31 pm UTC
I understand in general the use of global or local indexes in a partitioned table. But now I have a bigger question because of the subpartition option available in Oracle 92040. We plan to partition a 55 million records table by range-hash composite partition (would like to have a hash-hash partition is available). The range partition (10 partitions) is by BOL_NO which is the first column of a 3 column unique PK and the hash (8 partitions) is by CARTON. I am not sure if I should opt for both LOCAL indexes or GLOBAL indexes. I am worrying about the locking problem on the 8 - 10 partitions to go through when using a local indexes. Is global indexes more suitable for composite partitioned table ? Another option for me seems to be using a GLOBAL partitioned PK index and a GLOBAL non-partitioned index on CARTON. Please give your expert advise. Thank you.
July 06, 2004 - 2:20 pm UTC
you have a hash hash - if you wanted 8 ranges with 8 hashes and wanted a "hash hash", just use 64 hashed partitions!
there is no difference between "composite" and "non-composite" as far as index choice is concerned.
Your partitioning scheme either allows you to have local indexes -- or not.
Look at what you've partitioned on (the entire set). Now, look at your predicates.
Say you partitioned on BOL_NO,CARTON (carton by the way doesn't sound like a good hash -- is it almost unique? does it have TONS of values -- a good hash key needs that attribute).
If your queries are predominantly by
a) bol_no
b) carton
c) bol_no, carton
you can use local indexes (we can index partition eliminate)
if not, if you ask questions "show me the last 55 products we sold" -- something by date -- then you most likely need a global index on date in order to do that fast.
so, ignore that it is composite -- look at the key itself and ask yourself -- would we be able to efficiently answer these questions, or would a global index be called for.
Thank you!
David, July 06, 2004 - 12:47 pm UTC
Thank you for the explanation :)
The hint about smaller partition sizes is very useful (and makes complete sense if index update happens like from dml)
Have a good week,
David
Primary key index in Partitioning !
William Wong, July 06, 2004 - 2:55 pm UTC
Thanks for your great idea of hash+hash. Let me explain a bit more on my case: The PK is bol_no(8)+location(4)+carton(20). The carton is actual a 20 digits carton number and is the other index on the table. Both indexes are heavily used but in most case only bol_no+location in the PK is used to join with another tables of similar keys. Same as the usage on the carton number. I like the hash+hash approach but cannot figure how it can apply to my situation. (You don't really mean there is a hash-hash composite syntax ?) May be you can show me. I am thinking of composite partition as cutting the table in 2 dimensions so there are 8 x 8 cubes. When using local index, access will have to scan 8 partition indexes and same as access using carton. If I use global non-partitioned indexes, then I will only have one index to scan either using bol or carton. In single partition table, we have local index on the partition keys and global on others, this always ensure 1 scan on the index. By the way, the main objective in my case is performance in OLTP.
July 06, 2004 - 3:34 pm UTC
I mean -- what use would a hash/hash be? you just HASH into N partitions, you are done.
If the main objective is OLTP - you don't want to scan 8 index partitions -- you want (need) to scan ONE.
Primary key index in Partitioning
William Wong, July 06, 2004 - 4:07 pm UTC
Thank you for the clarification on the hash-hash issue. I think I have a mis-conception on composite partition. May be it is only best for date range partitioning. In my case, I believe the indexes are more important. I think I will go for a hash partition on the PK with local PK index. I wonder if a global hash partitioned index on carton will make much help in performance or no different than a global non-partitioned index ? Your recommendation is very valuable in my decision.
July 06, 2004 - 4:37 pm UTC
there is no such thing (until 10g) as a global hash partitioned index.
Only range partitioning with global indexes.
Primary key index in Partitioning
William Wong, July 06, 2004 - 4:47 pm UTC
So it is easier for me to decide without the choice. Thank you very much again. Have a nice day !
Global Partitioned Index
karma, September 08, 2004 - 12:59 pm UTC
I have table which is partitioned monthly and we load daily 1.3 Million rows. Currently it has only 2 months of data i.e. Aug and Sep. It is monthly partitioned based on run_d and data is never accessed based on this run_d.
So, we have 2 global partitioned index based on trade_d + Account and Settle_d + Account.
Table and Index is analyzed using dbms_stat.gather table and index stats. Now a simple query takes about 7+ minutes and i am lost as to whats happening. I know you dont have all details but do you see something glaringly wrong?
Same result with or without hint
select --+ index(i, ))
trade_d, shr_q, acctbrch_c, acctbase_c
from all_trade
where acctbrch_c = 'X24'
and acctbase_c = '055301'
and trade_d > to_date('18/08/2004','dd/mm/yyyy' )
Elapsed: 00:10:26.06
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11513 Card=1 Bytes=3
2)
1 0 PARTITION RANGE (ITERATOR)
2 1 TABLE ACCESS (BY GLOBAL INDEX ROWID) OF 'ALL_TRADE' (Cos
t=11513 Card=1 Bytes=32)
3 2 INDEX (RANGE SCAN) OF 'ALL_TRADE_D_UK' (UNIQUE)
(Cost=11512 Card=2055324)
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
70135 consistent gets
69885 physical reads
0 redo size
363 bytes sent via SQL*Net to client
275 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
September 08, 2004 - 1:40 pm UTC
are you referencing account in that query (is acctbrch_c or acctbase_c "account"?)
query plans says "we'll range scan that index, picking up about 2million possible records, but once we get to the table and apply the other predicate -- we'll find about 1 record (you found 4)"
seems like a classic "you indexed the wrong data for this query in the wrong order"
you want this query to be fast? trade_d would be the LAST column and acctbase_c, acctbrch_c would precede it.
Global partitioned index
karma, September 08, 2004 - 2:00 pm UTC
Well the data is going to be accessed most likey by account+trade_d or account+settle_d. The reason trade_d is prefixed is because we want to have glboal index to be partitioned and oracle only allows prefixed-global partitioned index. If i move account (i.e acctbrch_c + acctbase_c) to precede trade_d or settle_d then they need to be range partition based on acctbrch_c + acctbase_c.
If we dont partition global index then it would be huge file for index scan as we need to keep data for 2 years.
Is this valid concern or ?
September 08, 2004 - 2:35 pm UTC
a huge index is less of a concern - for range scans - for the goal is to scan as little an area as possible.
that and in a range scan, you walk the root/branch/branch... to a leaf then we go leaf to leaf.
See the problem here is that for "where trade_d > ...." it has to scan TONS AND TONS of index entries -- going to the table for each on -- only to discover "doh, row doesn't match"
Even if you put acctbrch_c/acctbase_c into this index -- but put them second, you still have to scan MILLIONS of index entries to find the 4 you really want (the x > ? and y = ? when the index is on (x,y) has to look at all X's > ? and then inspect their Y values. If the index were on (y,x), it would go right to the Y value and the least X value and just range scan only the 4 relevant records!!!!)
Global Partitioned Index
karma, September 08, 2004 - 3:38 pm UTC
Thanks! This makes lot of sense but we went with the idea of global partitioned index for some performance reasons thinking optimizer have to do less work to get to the data using rowids. So,
1) In this case does it make sense to go with GLOBAL index rather then global partitioned index ?
3) In the case of global index which takes 3G/month and we need to keep data for 2 years i.e. 72G. How long would it take to rebuild index when we do partition management ? Is there some formula to figure how long would it take ? Or things to keep in mind ? I know it depends on cpu, memory, temp space etc but what is the worst case scenario to rebuild huge index in parallel with large sort area, temp space etc?
September 08, 2004 - 3:47 pm UTC
1) only if it makes administrative sense -- it won't make much of an impact "optimizer" wise.
3) well -- after ALL partition operations (or during) the global partitioned OR non-partitioned index would have to be rebuilt or maintained. It would be roughly "the same" -- may even be longer for the partitioned index actually. Think about it -- a global partitioned/non-partitioned index would have to read the entire table to be rebuilt! both of them.
but you can always partition your indexes - just use the values on the leading edge is all -- they have values as well!
Dave Aldridge, September 09, 2004 - 12:23 am UTC
Quote: "why are your queries slow -- is it because they retrieve N million records? no degree of partitioning will speed that up. ... Partitioning can, when inappropriately|appropriately applied, make a system run many orders of magnitude slower|faster -- or have no affect whatsoever on performance."
Not meaning to be picky, but just noting that this comment is not valid when you consider the use of partitioning to reduce a full table scan into a full partition scan. You might reduce a scan of 10 million rows into 2.5 million with an appropriate partitioning scheme.
September 09, 2004 - 8:07 am UTC
true, if you could reduce the full scan from 10million to just the 2.5million you want -- but then again, your time to process the 2.5million records (write them to disk, do whatever it is you do with 2.5 million records) is probably the bottleneck at that point :)
A slightly different problem
Michael, March 10, 2005 - 11:07 am UTC
We are on 9.2.0.5 on Solaris. Our storage is on a Hitachi SAN, RAID 5 for our data (RAID 1+0 for TEMP, ROLLBACK, ETC).This database is a warehouse with 9 schemas for various applications. My question relates to the rollout of the latest schema, which will need monthly partitions for some of the tables. We anticipate keeping 6 months to 1 year of history (user has not made up mind) for those tables.
I have read the chapter on partitioning and indexes in your "one on one" book (great book!) and the above thread. However, I'm not quite sure what to do in the deployment of the latest schema.
This shop recently adopted a policy banning "fat keys" as primary keys on tables, and I agree with that policy. This has let to the use of surrogate "dumb" keys generated with sequences. Fine and dandy.
Some of the tables in the current schema I have to put into production will be quite large and need to be partitioned by month, so I am range partitioning them by the proper date column for that table. In all cases, that date column is part of the natural key, along with 2 or 3 other columns. The question relates to how to index these tables. (We will have a separate unique index on the "fat" natural key.)
My initial inclination was to create a non-unique local index on the partition key (date) column, and then a global index on the sequence number generated (primary key) "id" column. However, I have read that it is possible to create a local partitioned index for the primary key if I simply change the primary key constraint to add the date column (partition key for the table) as the leading column in the pk constraint. (By the way, none of the tables in question have "child" tables which would reference the expanded primary key.) So does the following make sense?
CREATE TABLE LTR_PRMSE (
ACCT_NO VARCHAR2(20) NOT NULL,
LTR_NO VARCHAR2(20) NOT NULL,
PRMSE_AMT NUMBER(9,2) NOT NULL,
PRMSE_DATE DATE NOT NULL,
MTHD_ID VARCHAR2(4) NULL,
PRMSE_USER_ID VARCHAR2(10) NULL,
INITL_LOAD_DATE DATE NOT NULL,
LTR_PRMSE_SEQ_NO NUMBER NOT NULL
)
PCTFREE 20
PCTUSED 60
TABLESPACE collpart
PARTITION BY RANGE (prmse_date)
(
PARTITION LPMS0105 VALUES LESS THAN (TO_DATE(' 2005-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE COLLPART
PCTUSED 60
PCTFREE 20
INITRANS 1
MAXTRANS 255,
PARTITION LPMS0205 VALUES LESS THAN (TO_DATE(' 2005-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE COLLPART
PCTUSED 60
PCTFREE 20
INITRANS 1
MAXTRANS 255,
PARTITION LPMS0305 VALUES LESS THAN (TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE COLLPART
PCTUSED 60
PCTFREE 20
INITRANS 1
MAXTRANS 255,
PARTITION LPMS0405 VALUES LESS THAN (TO_DATE(' 2005-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
NOLOGGING
NOCOMPRESS
TABLESPACE COLLPART
PCTUSED 60
PCTFREE 20
INITRANS 1
MAXTRANS 255
)
NOCACHE
PARALLEL ( DEGREE 4 INSTANCES 1 )
MONITORING;
;
CREATE UNIQUE INDEX PK_LTR_PRMSE ON LTR_PRMSE
(
prmse_date asc,
LTR_PRMSE_SEQ_NO ASC
)
TABLESPACE collpart_ix
INITRANS 2
MAXTRANS 255
LOCAL (
PARTITION LPMS0105
LOGGING
NOCOMPRESS
TABLESPACE COLLPART_ix
PCTFREE 10
INITRANS 2
MAXTRANS 255
,
PARTITION LPMS0205
LOGGING
NOCOMPRESS
TABLESPACE COLLPART_ix
PCTFREE 10
INITRANS 2
MAXTRANS 255
,
PARTITION LPMS0305
LOGGING
NOCOMPRESS
TABLESPACE COLLPART_ix
PCTFREE 10
INITRANS 2
MAXTRANS 255
,
PARTITION LPMS0405
LOGGING
NOCOMPRESS
TABLESPACE COLLPART_ix
PCTFREE 10
INITRANS 2
MAXTRANS 255
)
NOPARALLEL;
ALTER TABLE LTR_PRMSE ADD CONSTRAINT PK_LTR_PRMSE PRIMARY KEY (prmse_date, LTR_PRMSE_SEQ_NO)
USING INDEX;
Since the surrogate key is dumb and would never be used in any query, does the above make sense? It almost seems like a waste to have the primary key index (which would be large) be only on the dumb key if it will never be used. On the other hand, adding the date to the primary key partially defeats the purpose of having a surrogate key!
Your opinion, as always, will be highly valued.
March 10, 2005 - 7:15 pm UTC
if the sequence must be unique -- that constraint doesn't do that.
but if you never point to this, no foreign keys -- and you have a unique constraint on the fat thing - whats up? why bother? why have this sequence column at all? what'll use it?
global or local,
sns, June 09, 2005 - 9:28 am UTC
How can I say whether the Index is local or global partitioned index?
Thanks,
June 09, 2005 - 11:19 am UTC
tkyte@ORA9IR2W> select index_name, locality from user_part_indexes;
INDEX_NAME LOCALI
------------------------------ ------
T_IDX1 LOCAL
T_IDXZ GLOBAL
thanks,
sns, June 10, 2005 - 2:02 pm UTC
one more question,
How do we know whether the index is BINARY or BITMAP?
thanks,
June 10, 2005 - 3:54 pm UTC
select index_type from user_indexes;
Partitioned Index question
Bakunian, August 18, 2005 - 1:35 am UTC
Tom,
In DSS environment range partitioned table with 10 columns, 5 columns are members of unique local index, additionally each column has its own non-unique local index in case ad hoc query issued therefore total of 11 indexes.
I am thinking dropping those 5 non-unique duplicates. Will optimizer be able to use unique index to answer query if only one column of unique index will be present in predicate?
Thank you for your time
August 18, 2005 - 3:57 pm UTC
no.
create index on t(a,b,c,d,e)
will be able to answer queries that have "where a <operation> :bind and ...."
and MAYBE "where b <operation> :bind..."
but would not be useful typically for queries on c, d, e without A (and maybe B)
A reader, October 21, 2005 - 11:26 am UTC
Hi Tom,
I have a table which will have close to 10 million records. It is range partitioned on date with a global index on id. The number of partitions are configurable and a batch process runs every night which drops the oldest partition and creates a new one. During the time when the old partition is dropped and a global index re-created, the system is unusable. Each partition has 2 million records and I want to keep this time to a minimum as there are numerous records being inserted in the table every second. Will a composite partitioning (range partition on date and hash partition on id) help to reduce the time. Currently the time taken is 30 secs. Could you provide any other solution.
October 21, 2005 - 11:40 am UTC
you can do the drop and have the global index be maintained in current versions of the software. Sure the "drop" will take longer but the downtime goes to "zero" basically.
Else, if you drop and do not maintain the global index, you have to rebuild it afterwards.
A reader, October 21, 2005 - 12:20 pm UTC
And what about Composite Partitioning? How useful would that be from a performance point of view.
October 21, 2005 - 1:58 pm UTC
for what? partitioning is a tool, we need a problem to solve before we can say "good" or "bad"
Archiving high volume tables
A reader, October 27, 2005 - 12:08 pm UTC
Tom,
I would like your opinion on the options avalable for archiving/purging high volume tables. We have a few tables that have 35 Million + records. Operations against these tables are taking a considerable amount of time. There is a lot of data in these tables that will not be used very frequently. The options I have in mind are
1) Create a back up table that will have all the data except data for the current year. Purge all data except current year data from the high volume table.
2) Partition the table based on year.
I would like to know the best way to archive/purge these tables.
Thank you
October 27, 2005 - 1:24 pm UTC
c) index table so as to not have to process data you don't need to process....
2) is the logical choice in most cases.
Archiving high volume tables
A reader, October 27, 2005 - 3:12 pm UTC
>>c) index table so as to not have to process data you don't need to process....
How does indexing table help to not process data not needed. Would appreciate it if you can provide additional details.
>>2) is the logical choice in most cases.
Do you have a preference one way or the other.
Thank you
October 28, 2005 - 1:34 am UTC
c) look at your query that retrieves the data you need. IF that query retrieves data you DON"T need (the data you want to purge) REWRITE THE QUERY until it doesn't retrieve that data.
Then, lets talk about tuning that query to make it able to retrieve data efficiently from your table.
Let me say this - if you need say "about 500 records" to process something - I don't care of the table has
a) 50000 rows
b) 5000000 rows
c) 500000000 rows
......
we should be able to get 500 rows from that table in *about* the same time. Only if you meant to get 500 rows but you end up retrieving 50000 rows and then looking for the 500 you really need would we have a scale problem.
2) I would look at the algorithm in the application first, having data online can be very nice - and it should not necessarily affect your processing (see #1 again).
However, if you need to purge because the volumes will just get way out of control over time (saving of disk space is the concern more than anything) - then partitioning is the technology to investigate.
Partitioning
Raghav, November 04, 2005 - 9:19 am UTC
Hi Tom
I need some clarifications regarding the partitioning of existing (online) tables.
I have a table with more than 1000000 records. Hash index has been used on this table on primary key. Partitioning has not been used. Data is available on financial year basis.
In the above scenerio, can we use the partitioning of the table on the basis of financial year. Will it has effect on the data and its performance? Will there be an increase in the performance? What implications will be there if I do the partitioning on live data? What precautions are to be taken? Is there any impact on the existing programs (data entry screens, reports etc) after partitioning?
Thanks and Regards
Raghav
November 04, 2005 - 5:08 pm UTC
hash index has been used....
partitioning has not been used?????
oxymoron going on there - does not compute. do you mean "hash cluster"?
Partitioning
Raghav, November 11, 2005 - 8:44 am UTC
Hi Tom
I feel I am not clear about hash index and partitioning. My query is related to partitioning. In my database, the tables are having primary keys / combinational primary keys and the indexes are based on those primary keys.
There is a table of same type but having a 1000000 of records and the data is archived basing on financial year to reduce load from the main table (to increase the data entry speed).
My question is if there is any posibility to use the partitioning of this table financial year wise, then the load of creating archival tables will be reduced and using this partition the access to this table will be fast.
Please guide me and correct me if I am wrong.
Thanks and Regards
Raghav
November 12, 2005 - 8:33 am UTC
"combinational primary keys" ? not sure what that is, do you mean a concatenated set of columns that make up a primary key?
There is a table of same type but having a 1000000 of records and the data is
archived basing on financial year to reduce load from the main table (to
increase the data entry speed).
data entry speed should not be affected by table size too much....
Do you have access to either of my books "Effective Oracle by Design" or "Expert Oracle Database Architecture". I cover the "physics behind partitioning" and explain the cases where you would or would not want to use it. One would need more information to answer this question (1,000,000 rows is a pretty small table), such as the physical schema, why you are having performance issues with it (are you? or are you hypothesizing you will because 1,000,000 sounds "big" ?)
Indexing
Raghav, November 21, 2005 - 7:38 am UTC
Hi Tom
The process, which is taking too long time, is searching the table which is having 1000000 records. In that particular program (process), it is scanning all the records to fetch a set of 6 records each time for updation into a temporary table and once the updation is over delete those set of records from live table and insert the updated data from the temporary table. It is observed that there is no index on those non-primary columns on which the filtering is done.
we have created an index on those set of columns and the process was completing the cycle within seconds.
Thanks and Regards
Raghav
TO_CHAR() in partition key column
VA, January 10, 2006 - 1:24 pm UTC
I have a large partitioned table partitioned by a 2-column primary key (as_of_date and cust_id)
There is a requirement to search for all December partitions for stuff.
If I do a query like
where to_char(as_of_date,'fmMonth')='December'
and cust_id=1234
It skips the PK index because of the function applied on the leading column and does a full scan of all partitions in the table. This is unacceptable.
Any ideas?
Thanks
January 10, 2006 - 1:46 pm UTC
don't apply functions to database columns needlessly.
where as_of_date >= to_date( '01-dec-2005', 'dd-mon-yyyy' )
and as_of_date < to_date( '01-jan-2005', 'dd-mon-yyyy' )
and cust_id = ....
A reader, January 10, 2006 - 1:49 pm UTC
I don't think I can do that.
The table has 10 Decembers and other months and daily and monthly data. I need the December end monthly data in my query.
Yes, I can hardcode the dates I need in my query
where as_of_date in (to_date('12/31/2005','mm/dd/yyyy'),....)
But this is not a maintainable solution as I would have to keep modifying the WHERE clause every year.
Any other ideas? Thanks
January 10, 2006 - 2:53 pm UTC
you would need a function based index then, partitioning is a red herring here, it is not the culprit, it is the function on the date column.
<quote>Any other ideas?</quote>
Gabe, January 10, 2006 - 4:55 pm UTC
Where is the time dimension table, when one needs it?
again about partitioning
natasha naumenko, March 13, 2007 - 8:43 am UTC
Hi Tom.
There is a table
pays_info with primary key
rec_id. There is also column
rec_entried which contains information about pay date. And there is another table
pays_apx with rec_id foreign key (some additional information about pays). Both tables are about 30Gb. I have two tasks: archive old data and increase performance. The most frequently query looks like
select * from pays_info where rec_entried between :d1 and :d2
So, i wanted to partition table pais_info by range of rec_entried, and pays_apx by range of rec_id. In that case i would be able to drop the old data when need to and to increase performance of most part of queries. But there is one problem ¿ i can¿t create local unique index on rec_id. And i can¿t stop the system for rebuilding global index after dropping old partitions for more than 2 hours.
Could you give me an advice what should i do? Maybe the best way is to partition both tables by range of rec_id, because the performance is good enough and the main goal is to archive old data? Or create little partitions so than rebuilding index when dropping data would take little time?
Sorry for my English and thanks for your time
March 13, 2007 - 11:34 am UTC
... and to increase performance of most part of queries. ....
how would that be achieved exactly???
you never need to stop for a global index rebuild, you can drop the partition and MAINTAIN the indexes during the drop - it is an online operation (so you don't care if it takes a day, it is "online" after all...)
Mohamed Houri, March 14, 2007 - 5:22 am UTC
In my opinion the best way is to partition both tables by range on
rec_entried(pay date) and to have them both key primared by (rec_ide, rec_entried). The second table will have (rec_ide, rec_entried) both as primary and foreign key
Since your key partition (rec_entried) will be a part of the primary key you can then create a localy partitioned index on (rec_ide, rec_entried)
Since your main queries will involve in their where clauses the key partition rec_entried you may have more chance to see oracle optimiser doing partition pruning(partition elimination) and hence enhancing perfomance.
This kind of key partitioning and key primaring (together with foreign key) will allow you to exchange partition before droping them as shown in this example
http://govt.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:728425384831#60451928328887 Mohamed
What to index...
Dawn, March 23, 2007 - 12:30 pm UTC
Hi Tom,
I'm on a 9i database, and I have a table where the primary key consists of two columns, id & event_date. Obviously, there needs to be a unique index on id & event_date.
Due to the nature of the data and the queries, I want to range partition this table by month on event_date.
However, despite lots of reading up on this, I'm not sure about what indexes I will need. I've come up with two ideas so far:
1. Locally partitioned index on event_date; global non-partitioned unique index on id & event_date
2. Locally partitioned unique index on event_date & id
I'm tending towards 1 at the moment, as I know for sure that this will cover everything, and won't cause locking of all partitions, etc, but at the same time, it seems a bit overkill, since I'm storing the same column values in two different indexes.
I don't know if 2 is just a really dumb, bad idea, or whether it's ok to use, given that the partition key does not contain the entire primary key.
I've gone round in circles on this and confused myself, and I would appreciate any help you could give me.
Thanks!
March 26, 2007 - 6:48 am UTC
... Obviously, there needs to be a unique index on id & event_date. ...
Not obvious - in fact - not even true... A unique constraint or primary key constraint may be implemented under the covers using a unique index on just the columns of interest, a non-unique index on just the columns of interest or a unique/non-unique on the columns of interest plus other columns....
#2 would have the same locking considerations as #1. Not sure why you think #1 would be better or more desirable??
In this case, you are partitioning by DATE - it understands that if the partition key is the DATE column, uniqueness of (DATE,NUMBER) can be enforced since the number column only need be unique within any given partition.
Re. What to index...
Dawn, March 26, 2007 - 7:11 am UTC
Thanks for that, Tom. Reason why I was tending towards option 1 over option 2 is that I knew for sure that it wouldn't break anything, whereas I wasn't sure about option 2.
Given what you've said, I'm now plumping for option 2 (which I *thought* would be ok, and preferred, since it's less indexes overall!).
Thanks for clarifying things for me.
Clarification on Partition scheme/unique local index options
michelle h, May 13, 2007 - 12:35 am UTC
Tom,
I have read about partitioning in Expert On-on-One and have ordered your latest Expert Oracle book. I have also read Oracle's docs regarding partitioning, esp. Administrator's Guide and Data Warehousing Guide, and the input on this tand other Ask Tom threads has been helpful. But I¿m still a little muddy on some areas I hope you can clarify.
Configuration:
Oracle 10g r2 ;RAID 5, RAID 1; Data Warehouse scenario
Questions:
1) Oracle docs state:"A local index can be created UNIQUE if the partitioning columns form a subset of the index columns. "
In the case of a range-hash composite partitioning scheme, though, it seems I cannot create a unique local index (primary key) for the column used as the hash column:
(following method from your "one-on-one" book):
create table test.test_partition(ptid number primary key,
start_date date,
end_date date
) tablespace users
partition by range (start_date,end_date)
subpartition by hash (ptid) subpartitions 2
(
PARTITION part1
VALUES LESS THAN (to_date('01/02/2006','mm/dd/yyyy'),to_date('01/01/2007','mm/dd/yyyy')),
PARTITION part2
VALUES LESS THAN (to_date('01/02/2007','mm/dd/yyyy'),to_date('01/01/2008','mm/dd/yyyy'))
)
select segment_name, partition_name, segment_TYPE from dba_segments where OWNER='TEST'
SEGMENT_NAME
--------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------
TEST_PARTITION
SYS_SUBP40 TABLE SUBPARTITION
TEST_PARTITION
SYS_SUBP39 TABLE SUBPARTITION
TEST_PARTITION
SYS_SUBP38 TABLE SUBPARTITION
SEGMENT_NAME
--------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------
TEST_PARTITION
SYS_SUBP37 TABLE SUBPARTITION
SYS_C006638
INDEX
So, is the meaning that I can only have a unique local index for a composite partition scheme on the
partition columns and not on the subpartition columns? In other words, it does not consider the partition columns those defined by the partition scheme but those defined at the PARTITION and not SUBPARTITION level?
2) Assuming my analysis for question 1 is correct, I am trying to determine the best primary key and partitioning table and index strategy.
Here is my situation:
I am working on a project that involves redesign of a data warehouse.
a) The existing data warehouse "main" table has 100 million records with an annual growth rate of 100 ¿ 120 million.
b) Data is transferred into this table from OLTP tables.
c) The data transfer occurs based on a date range (report_begin_date, report_end_date).
d) There are monthly and yearly data transfers.
· The monthly transfers involve deleting all records in the data warehouse for the given year through the designated month (ex: report dates are 1/1/2006 ¿ 12/31/2006. The ¿March¿ move moves all 1/2006 ¿ 3/2006 data).
· The yearly move involves deleting all data in the data warehouse for a given year and then transferring all in existence from the OLTP table into the data warehouse.
· The monthly move will have a minimum of 8 mil records, and the yearly will be between 100-120 million.
e) The reporting for the system consistently uses the report_begin_date and report_end_date range as part of the search criteria.
f) Data will also be exported from the data warehouse on a monthly basis at the record-level to submit to a national reporting agency.
g) The redesign will be a hybrid design. Most of the lookup tables will be a star design on the main table, but there will also be the "snowflake" design for associative data tables.
My objectives of my primary key and partitioning selections are as follows:
1) Ease of administration ¿ I want the monthly and yearly moves to go smoothly while allowing the unaffected years of data to remain online. This, of course, means a global index, if used, cannot be invalidated during the data moves which seems doable in 10g. Further, I want to reduce any locking/contention as best possible.
2) Performance ¿ I want the joins between the main and associative tables to be optimized for retrievals. While the search criteria on the main table will use reporting begin/end dates, I could see simply joining the associative tables to the main table on the main_id.
Here is an example of the table structure, less primary keys, indexes and partitions:
CREATE TABLE main_table(main_id number,
report_start_date date,
report_end_date date,
lkup_1_id int,
lkup_2_id int,
etc¿
)
CREATE TABLE main_assoc_table_1(assoc_id_1 number PRIMARY KEY,
Main_id number,
report_start_date date,
report_end_date date,
assoc_1_code varchar2(25),
assoc_1_desc varchar2(120),
)
While I realize every situation is different, I keep reading over and over in Oracle docs that, in general, you should use local indexes for Data Warehousing situations, so I have tried to structure my proposed redesign with this in mind.
However, it seems from parts of this thread and also Oracle docs that it is now more doable to have a global index on a partitioned table without invalidating the global index when dropping a partition but still seems to imply "but still use local indexes if at all possible". (See:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14223/indexes.htm#sthref396, "Choosing Between Local Indexes and Global Indexes" section)
Here is my proposed strategy to meet the objectives for ease of administration and performance
OPTION I
1) Composite range-hash table partitioning on the main and associative tables by range on the report_begin_date and report_end_date columns and hash by main_id column.
2) Primary key on (main_id) on the main_table, with foreign key constraints on main_id in the associative tables. I debated on the primary key and chose the single column which will uniquely identify the row over a wider composite column of (report_begin_date, report_end_date, main_id). My thought is less storage needs for a smaller constraint/index.
3) Local index on (report_begin_date, report_end_date) for the main and associative tables.
4) Global index on (main_id) on the main and associative tables.
Based on the system usage patterns and objectives I described, does the PK, type of partitioned index seem well-suited, or would the following make more sense?
OPTION 2
1) same as above
2) PK on on (report_begin_date, report_end_date, main_id) on main_table. PK on on (report_begin_date, report_end_date,assoc_id) on associative tables.
3) Local index on (report_begin_date, report_end_date, main_id/assoc_id).
4) N/A
Option 2 keeps to local indexes, which may a better option.
Thanks for the review and feedback.
michelle h, May 13, 2007 - 8:56 am UTC
Quick note on last post - my "main table" refers to the primary fact table, in case that was not clear.
May 14, 2007 - 2:01 pm UTC
1) a unique index that is local would have to have the entire set of partition keys:
7 PARTITION BY RANGE (dt)
8 subpartition by hash(x)
9 (
10 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
11 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
12 PARTITION junk VALUES LESS THAN (MAXVALUE)
13 )
14 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create unique index t_idx1 on t(dt) local;
create unique index t_idx1 on t(dt) local
*
ERROR at line 1:
ORA-14188: sub-partitioning columns must form a subset of key columns of a UNIQUE index
ops$tkyte%ORA10GR2> create unique index t_idx2 on t(x) local;
create unique index t_idx2 on t(x) local
*
ERROR at line 1:
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
ops$tkyte%ORA10GR2> create unique index t_idx3 on t(dt,x) local;
Index created.
Got it!
A reader, May 14, 2007 - 2:39 pm UTC
Thank you. I think the light has gone on.
I read the Oracle definition for a local unique index to mean "a subset of the partition columns" but in fact it is the partition columns in *full* form a subset of the index.
Is this index meaningful?
A reader, September 19, 2007 - 11:04 am UTC
Tom,
My report table uses weekly range partitions on the POST_DT column. I noticed the table has a non unique index on POST_DT only. I don't think the index is helping in this case. Actually, I executed several select count(*) where post_date = some date using the no_index hint and those run faster than selects that use the index.
I think the index would be beneficial if it contains additional columns, not just POST_DT which is the partitioning column.
Any thoughts?
Thanks.
Sample row counts:
select post_dt, count(*) from report where POST_DT >= to_date('01-SEP-2007','DD-MON-YYYY') group by post_dt order by post_dt
POST_DT COUNT(*)
--------- ----------
01-SEP-07 69716
02-SEP-07 68640
03-SEP-07 67582
04-SEP-07 66531
05-SEP-07 65507
06-SEP-07 64499
07-SEP-07 63466
08-SEP-07 62425
09-SEP-07 61373
10-SEP-07 60368
11-SEP-07 59379
12-SEP-07 58343
13-SEP-07 57320
14-SEP-07 56290
15-SEP-07 55231
16-SEP-07 54154
17-SEP-07 53098
18-SEP-07 52054
September 19, 2007 - 1:13 pm UTC
... I executed several select count(*) where
post_date = some date using the no_index hint and those run faster than selects
that use the index. ....
so what I say - unless your application only issues select count(*)'s using post_date = date (which is highly unlikely)
without understanding the questions people actually ask of your data - no one could comment on this.
That's how data is selected
A reader, September 19, 2007 - 2:07 pm UTC
The queries against the table are all based on "where post_dt = date".
Based on that information, is there a benefit on having an index on post_dt?
September 20, 2007 - 9:28 pm UTC
insufficient data still.
they are all 'based on', that does not imply "consist only of"
you don't say what you select either
or what the queries are
In general, you retrieve 1/7th of the data. that is about 14% of the data - an index on the date column may or may not be "useful" depending on the clustering factor of the index (which is probably very good if this data is loaded 'in order')
OK
A reader, November 21, 2007 - 7:23 am UTC
Tom,
If we rename some range partitioned table partitions, do the indexes of the old table partitions get renamed to new table partitions?
November 20, 2007 - 1:25 pm UTC
when you tried it what happened?
to rename an index, you rename an index....
to rename a partition, you rename a partition....
I would not expect the index partitions to be renamed...
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(x) local;
Index created.
ops$tkyte%ORA10GR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';
PARTITION_NAME
------------------------------
JUNK
PART1
PART2
ops$tkyte%ORA10GR2> alter table t rename partition junk to high_bounded_data;
Table altered.
ops$tkyte%ORA10GR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';
PARTITION_NAME
------------------------------
JUNK
PART1
PART2
partition by date and primary key
A reader, June 18, 2009 - 11:07 am UTC
Dear Tom,
In the begining of this thread you wrote
"Hence, in this case, you are partitioning by ACCT_BY_DATE, but the primary key is
ACCT_ID. The index used for the enforcement of that primary key *must* be a global index
- there is no chance of using a LOCAL."
But my question is:
What if in the same situation we will create a NORMAL NON PARTITIONED index (ACCT_ID,ACCT_BY_DATE) to enforce the primary key?
Will Oracle still lock all N-1 partitions in order to ensure that the current being inserted acct_id doesn't exist in the N-1 partitions?
Thanks in advance
June 18, 2009 - 3:27 pm UTC
a normal non-partitioned index is a global index by another name. a global index is just an index that is partitioned DIFFERENTLY than the underlying table is.
The "lock all n-1 partitions" was a "this is what we would have to do hypothetically" - it NEVER happens, we don't permit you to do what you would need to do to get into that situation.
Here it is my example
A reader, June 19, 2009 - 3:14 am UTC
Hi Tom,
Sorry but I didn't clearly understood your question. Here it is my example
create table part_tab
(
acct_id number(10),
acct_by_dat date ,
x varchar2(6 byte),
y number(10)
)
partition by range (acct_by_dat)
(
partition r_20091101 values less than (to_date(' 01112009','ddmmyyyy')),
partition r_20091201 values less than (to_date(' 01122009','ddmmyyyy'))
);
create unique index part_tab_pk on part_tab
(acct_id, acct_by_dat);
alter table part_tab add
constraint part_tab_pk
primary key
(acct_id,acct_by_dat)
using index;
In this situation we have range partitioned table with a primary key containing the partition key but the index enforcing this Primary key is a global non partitioned table.
This is what I wanted to have your opinion about.
Thanks
June 19, 2009 - 3:40 pm UTC
I have no idea who "you are" or what question I asked of you. I didn't ask any questions on this thread recently.
What opinion could I possibly have on a structure when I know NOTHING about how that structure will be used ????????
The bottom line (I assume you are the last posting right above), is we NEVER lock the n-1 partitions. That was PURELY and 100% a "hypothetical situation"
The hypothetical situation was: suppose we did let you create a LOCAL unique index on attributes that are not in the partition key. IF we did that, THEN we would have to lock all of the other partitions. So, that is (one of) the reason we DO NOT let you do that.
It is 100% hypothetical - we do NOT lock the entire table to enforce unique - not in any case.
Please re-read the original answer, it seems to me to be quite clear that it is 100% hypothetical.
<quote>
The index used for the enforcement of that primary key *must* be a
global index - there is no chance of using a LOCAL.
The reason -- if you think about the local index -- the value ACCT_ID = 55
could be in ANY PARTITION. Hence, any insert would have to check ALL local
partitions for 55 and lock each index partition to ensure no one even tries to
insert ACCT_ID=55 right now. It would be a huge scalability inhibitor as only
one person at a time could insert/update or delete the primary key.
</quote>
Peter, October 09, 2015 - 12:04 pm UTC
Tom,
"the primary key index can ONLY be local IF and ONLY IF, the primary key is in fact the
(or part of the) partition key."
I'd say instead, that the partition key must be the (or part of the) primary key. In case of a composite partition key and a single-column primary key (not supplied with unique index), we have to look through several partitions to validate the record being inserted.
October 09, 2015 - 12:08 pm UTC
Fair point.
A reader, October 15, 2019 - 1:17 pm UTC
Really?
Hmmm, August 13, 2021 - 1:49 pm UTC