doesnt this sound contradictory
A reader, November 18, 2002 - 4:05 am UTC
Hi
You said
"Now, if they had used a global index on EMP partitioned by EMPNO (either all in one partition or by range on empno to spread it out) they may well increase performance"
"It will be the queries that do NOT use year_week_no - they will have to index range scan N partitions"
In the second quote it seems that you mean queries not using year_week_no would not gain performance even the indexes involved are global, doesnt sound consistent with first quote.... :-?
November 18, 2002 - 8:12 am UTC
when you take two sentences, from two different paragraphs and put them next to each -- sure they can sound contradictory.
sentence one comes from a paragraph describing that if you partition a table by DATE but query by EMPNO -- you should globally partition the index by EMPNO perhaps -- else you'll pay the penalty of N index range scans for each index access.
sentence two comes from a much later paragraph that starts off with:
...
Now, in your case, if the vast preponderance of queries all have:
where year_week_no = :b1 and ....
in them, and you partition the tables AND indexes by that
.......
point is: local and global indexes are both good, both useful, both have their place. Understand them or just don't use partitioning cause you'll totally botch everything up. The sentences don't contradict eachother -- they just apply in wholly different cases.
partition table just to speed up data purge
A reader, November 20, 2002 - 6:50 am UTC
Hi
Is it feasible to partition a table by date to speed up data purge?
We have a 50 millions rows table in an OLTP database (CRM)from which we have to delete 20 millions of old customer cases, this table has around 66 columns, 16 indexes which 2 of them are compositely indexed (3 columns each), as you can imagine deleting this table is extremely slow, 80000 rows takes 20 minutes so roughly calculating it would take around 3 days to delete 20 millions of rows.
Since the delete statement is
delete from XXX where end_date between X and Y
note: end_date is indexed and used during this delete
we have been thinking partition by that field and then drop the corresponding partitions.
My question is, in an OLTP database (highly intensive database) if we partition such table and create all old indexes as global indexes (to avoid scanning of several local index structures) with zero local index would this affect normal transactions performance?
Very few queries use end_date in WHERE clause
November 21, 2002 - 12:14 pm UTC
well, the global indexes in 8i and before would go invalide during the "purge" and would have to be rebuilt.
Can you afford the downtime?
In 9i, you can maintain the global indexes during the purge. Will not be "as fast" but no index rebuild needed (and should be faster then a delete but I haven't tested how much faster)
downtime is affordable
A reader, November 21, 2002 - 12:17 pm UTC
Hi
I reckon in this case downtime is affordable although I think we dont need to bring database down, we would rebuild the indexes online with parallel degree and nologging, I doubt rebuild index would take three days :D
So I guess this may be a approach :-?
November 21, 2002 - 1:26 pm UTC
correct -- downtime here means "your OLTP application isn't working whilst this is happening"
Which index is best
Balaji CN, November 25, 2002 - 11:04 pm UTC
Hi Tom
I have a basic doubt.
I have a global index on a partitioned table Say a date field Hire_dt.
I drop the Global index and create a local index on the same column Hire_dt.
Which one would give a better performance Global index or Local index.Assume I have a min of 1 Million records in the table.
Regards
November 26, 2002 - 7:54 am UTC
You know what -- if one always gave better performance then the other in all circumstances, why in the world would we have bothered inventing the other type???
This is an unanswerable question given the amount of information we have here.
What you need to do is understand the characteristics, the functionality of, the structure of the global index vs the local index.
Suppose you do queries like:
select count(*) from t where hire_dt between A and B
suppose further that you had partitioned such that A is in one partition and B is in another. Suppose this query is executed thousands of times per day.
Now, I might want a single global index so that I do a SINGLE index range scan in order to count those records. Otherwise when I did that query -- I would have to perform AT LEAST two index range scans and perhaps more then 2 depending on the range in order to answer the question.
Suppose instead you do queries like:
select * from t where hire_dt = A
Now, a local index would be a nice thing. I'll have N indexes -- I'll be spreading my workload out (we'll have less people banging on each local index partition then we would if there were just one global index partition).
Suppose it is crucial to be able to age data out by hire_dt (eg: anyone hired more then 7 years ago -- get rid of them). Now, when I do the alter table drop partition -- all global indexes (in 8i and before) will go invalid -- my system is basically offline till I rebuild. If I used local indexes -- not so.
And so on. Partitioning is a tool, a very nice tool. You have to look at what your primary goals are, how your system uses the data -- and then (and only then) can you begin to pick the partitioning scheme that will work nicely for you!
local vs global
A reader, November 06, 2003 - 4:23 am UTC
Hi Mr Kyte
What is better for optimizer,
CREATE INDEX SALES_TIME_BIX ON SALES
(TIME_ID)
LOCAL (
PARTITION SALES_1995
NOLOGGING
TABLESPACE SH_DATA_01,
PARTITION SALES_1996
NOLOGGING
TABLESPACE SH_DATA_01
)
OR
CREATE INDEX SALES_TIME_BIX ON SALES (TIME_ID)
GLOBAL PARTITION BY RANGE(TIME_ID)
(
PARTITION SALES_IDX_1995 VALUES LESS THAN
(TO_DATE('01-JAN-1996','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')),
PARTITION SALES_IDX_1996 VALUES LESS THAN
(TO_DATE('01-JAN-1997','DD-MON-YYYY','NLS_DATE_LANGUAGE = American')),
PARTITION SALES_IDX_MAXVALUE VALUES LESS THAN
(MAXVALUE)
)
Or they are both the same just that local is more manageable?
November 06, 2003 - 7:53 am UTC
the answers are
the first one
the second one
neither
both
it depends.
they are apples and toaster ovens. you need to understand the "physics" here. how the data will be stored.
suggestion - on a white board -- draw pictures. Understand what would be in the table partitions. then above that, draw out what would be in the index partitions for the local one, then below it, draw out what would be in the global index partitions.
Then, run some predicates by it and see if it would be able to partition eliminate on the top or the bottom or neither or both.
neither is more manageable in every case.
neither is better in every case.
I did draw on a paper
A reader, November 06, 2003 - 8:22 am UTC
suggestion - on a white board -- draw pictures. Understand what would be in the
table partitions. then above that, draw out what would be in the index
partitions for the local one, then below it, draw out what would be in the
global index partitions.
Hi Mr Kyte
I drawed this on paper, local indexes I understand perfectly. The thing is with global paratitioned range indexes. If I globally partition the index into same number (and same criteria) of partitions as table partitions I dont see what is the difference between local and global!
Global Index
alex, February 17, 2004 - 3:47 pm UTC
Hello Tom
Can I askyou what is the diffeence between global and local index and when to use them.
Which one is better to use.
Thanks
February 17, 2004 - 7:00 pm UTC
if you have access to my book "effective oracle by design", I cover that with examples in there (as well as expert one on one Oracle)
the concepts guide does too.
In a nutshell
a local index is an index that is equi-partitioned with the table itself. Say the table is hash partitioned by the ID (unique key) column into 8 hash partitions. That means there will will 8 hash partitions for the local index and all of the data in partition-1 of the INDEX will point only to data in partition-1 of the table and so on (equi-partitioned, 1 to 1)
a global index is an index that is NOT equi-partitioned. taking same example of the 8 way table -- a globally partitioned index can have a totally different number of partitions and the index entries in index partition-1 can point to ANY of the table partitions.
First time for this
Michael, April 22, 2004 - 2:12 pm UTC
We are on 9iR2, a robust Sun Server and a Hitachi SAN.
I recently inherited a large DH/DSS database. One of the biggest tables is over 100G, 115 million rows, in 49 monthly partitions. More partitions are being added all the time, going backward and forward in time. We currently have back to May of 2000 and will be going back to 1998.
I have been asked to create a non-unique index on two NON-Key columns in this table that are used for querying, including some join operations.
I decided to create a special tablespace for this index with local/uniform 10M extents, based on index sizing exercizes. My index SQL looks like this:
create index mthccacchist_ix2 on mnthly_cc_acct_hist (acct_id, acct_open_dt) local
tablespace acct_hist_indx2 pctfree 10
/
I did this rather than naming 49 partitions in the sql. I know Oracle will name the individual index segments for me. Two questions:
What do those names look like? I have no test platform right now to test this on. (That will be coming soon). And, what happens when new partitions are added to the table and populated? Do I have to rebuild the entire index?
Any recommendations will be appreciated.
April 23, 2004 - 8:58 am UTC
they will be named after the table partitions and since the index is local, partition operations will maintain it transparently for you, you will not need a rebuild.
ops$tkyte@ORA9IR2> CREATE TABLE audit_trail_table
2 ( timestamp date,
3 username varchar2(30),
4 action varchar2(30),
5 object varchar2(30),
6 message varchar2(80)
7 )
8 PARTITION BY RANGE (timestamp)
9 ( PARTITION jan_2002 VALUES LESS THAN
10 ( to_date('01-feb-2002','dd-mon-yyyy') ) ,
11 PARTITION feb_2002 VALUES LESS THAN
12 ( to_date('01-mar-2002','dd-mon-yyyy') ) ,
13 PARTITION the_rest VALUES LESS THAN
14 ( maxvalue )
15 )
16 /
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index partitioned_idx_local
2 on audit_trail_table(username)
3 LOCAL
4 /
Index created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select partition_name, status
2 from user_ind_partitions
3 where index_name = 'PARTITIONED_IDX_LOCAL'
4 /
PARTITION_NAME STATUS
------------------------------ --------
JAN_2002 USABLE
FEB_2002 USABLE
THE_REST USABLE
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table audit_trail_table
2 split partition the_rest
3 at ( to_date('01-may-2003','dd-mon-yyyy') )
4 into ( partition apr_2003, partition the_rest )
5 /
Table altered.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select partition_name, status
2 from user_ind_partitions
3 where index_name = 'PARTITIONED_IDX_LOCAL'
4 /
PARTITION_NAME STATUS
------------------------------ --------
APR_2003 USABLE
THE_REST USABLE
JAN_2002 USABLE
FEB_2002 USABLE
ops$tkyte@ORA9IR2>
This is the example I was looking for!
Robert, July 21, 2004 - 4:45 pm UTC
local index on huge table ...
Pravesh Karthik from India, April 21, 2005 - 10:18 am UTC
Tom,
I have a table with 200 million records. i am trying to created index (local index) on that. its has already taken 3 hours. how to know whether the index creation is going on or not.
is there any way other way create a local index on huge table ...
I am trying as below
CREATE BITMAP INDEX BX_DMX_1 ON TB_DMX_FCT_AP_AUDIT
(
AP_INV_FISCAL_MTH ASC,
AP_INV_JOURNAL_NUM ASC
)
TABLESPACE AP_AUDIT_I1
LOCAL (PARTITION IP_DMX_INV_FISCAL_MTH_2005
,
PARTITION IP_DMX_INV_FISCAL_MTH_2004
,
PARTITION IP_DMX_INV_FISCAL_MTH_2003
,
PARTITION IP_DMX_INV_FISCAL_MTH_2002
,
PARTITION IP_DMX_INV_FISCAL_MTH_2001
,
PARTITION IP_DMX_INV_FISCAL_MTH_2000
,
PARTITION IP_DMX_INV_FISCAL_MTH_1999
,
PARTITION IP_DMX_INV_FISCAL_MTH_1998
,
PARTITION IP_DMX_INV_FISCAL_MTH_1997
,
PARTITION IP_DMX_INV_FISCAL_MTH_1996
)
;
CREATE INDEX IX_DMX_2 ON TB_DMX_FCT_AP_AUDIT
(
AP_INV_FISCAL_MTH ASC,
AP_INV_ACCT_FI_DD ASC,
AP_INV_ACCT_FI_CCC ASC,
AP_INV_ACCT_FI_GGGG ASC
)
TABLESPACE AP_AUDIT_I1
LOCAL (PARTITION IP_DMX_INV_FISCAL_MTH_2005
,
PARTITION IP_DMX_INV_FISCAL_MTH_2004
,
PARTITION IP_DMX_INV_FISCAL_MTH_2003
,
PARTITION IP_DMX_INV_FISCAL_MTH_2002
,
PARTITION IP_DMX_INV_FISCAL_MTH_2001
,
PARTITION IP_DMX_INV_FISCAL_MTH_2000
,
PARTITION IP_DMX_INV_FISCAL_MTH_1999
,
PARTITION IP_DMX_INV_FISCAL_MTH_1998
,
PARTITION IP_DMX_INV_FISCAL_MTH_1997
,
PARTITION IP_DMX_INV_FISCAL_MTH_1996
)
;
Thanks.
Pravesh Karthik
April 22, 2005 - 8:43 am UTC
you have parallel index creation -- sure, you can do it in parallel.
you have v$session_longops that you can use to monitor long running processes.
Help Help Help
R Subramaniam, December 07, 2006 - 5:29 pm UTC
Hello Tom,
We have a terrible problem at our site. We run Oracle 9.2.0.4 and have a FACT table with abt 6-10 Million records.
V_UNIT_CDE NOT NULL VARCHAR2(4)
V_SEGMENT NOT NULL VARCHAR2(20)
V_ROW_CDE NOT NULL VARCHAR2(4)
V_COL_CDE NOT NULL VARCHAR2(4)
V_MONTH NOT NULL VARCHAR2(6)
V_SCENARI NOT NULL VARCHAR2(5)
N_AMOUNT NOT NULL NUMBER(22,7)
F_REPORT_ NOT NULL CHAR(1)
F_PROC_FLAG CHAR(1)
D_DATETIME DATE
This is a partitioned table by V_MONTH (Range Partition) and SUB Partitioned by V_UNIT_CDE, V_SEGMENT, V_ROW_CDE, V_COL_CDE & V_SCENARIO (Hash Partition)
We have a Primary Key Global Index on this table on V_UNIT_CDE, V_SEGMENT, V_ROW_CDE, V_COL_CDE, V_MONTH & V_SCENARIO
This is a very volatile table. Almost 30-40% of the records get changed (DELETES/INSERTS) and the performance degrades with time. Operations are performed by many concurrent users
We found out that with Index rebuilds the performance is restored. This madness of index rebuild has increased to a state where at certain days we perform index rebuilds every 8 hours.
Can you please suggest any alternatives.
I know that you are strongly against Index rebuilds.
Some facts. (I dont know why they do it but its done)
1) We only perform index rebuilds (every 8 hours) and that restores the system performance. We do not analyze the table. We analyze them every week.
2) The table is a LMT and we are really not worried abt space.
3) Index statistics
select index_name
, num_rows, sample_size, last_analyzed
, user_stats, blevel, leaf_blocks, distinct_keys
, avg_leaf_blocks_per_key, avg_data_blocks_per_key
from user_indexes
where table_name = 'CUBE_MSTR_CURR'
/
INDEX_NAME NUM_ROWS SAMPLE_SIZE LAST_ANAL USE BLEVEL
------------------------------ ---------- ----------- --------- --- ----------
LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
----------- ------------- ----------------------- -----------------------
SYS_C002757 6765938 6765938 07-DEC-06 NO 2
11242 6765938 1
Please help us.
Thanks.
R Subramaniam
December 07, 2006 - 5:55 pm UTC
umm, you spammed my email with this already, we already talked.
Apologies
A reader, December 08, 2006 - 11:04 am UTC
My Sincere Apologies Tom. I infact was putting the message in both the places. Henceforth I will keep it purely in the asktom web site.
Sorry and Thanks for the information.
Regards
R Subramaniam