If you have more indexes ...
April 4, 2008 - 4pm Central time zone
Reviewer: Dragos Craciun from Romania
To make all local indexes on a partition unusable:
ALTER TABLE <table_name> MODIFY PARTITION <part_name> UNUSABLE LOCAL INDEXES;
To rebuild:
ALTER TABLE <table_name> MODIFY PARTITION <part_name> REBUILD UNUSABLE LOCAL INDEXES;
April 7, 2008 - 10am Central time zone
Reviewer: arun pradhan from Birmingham,AL
Hi Tom,
Thanks And its a great solution...I have been using temporary table and exchanging the partition till now... I beleive, making index unusable is same as having no indexes at all.
Arun Pradhan
April 7, 2008 - 10am Central time zone
Reviewer: arun pradhan from Birmingham,AL
Hi Tom,
Thanks And its a great solution...I have been using temporary table and exchanging the partition till now... I beleive, making index unusable is same as having no indexes at all.
Arun Pradhan
Local partitions
July 23, 2009 - 10am Central time zone
Reviewer: reader from UK
This is fantastic - I never knew you could do that!
Thanks Tom
July 30, 2009 - 2pm Central time zone
Reviewer: Sachin from USA
i tried what you said to set partition index unusable. but when i run select query optimiser won't
consider indexes at all.
Followup August 3, 2009 - 5pm Central time zone:
explain what you mean?
it won't use unusable indexes - this is true (new default setting:
ops$tkyte%ORA10GR2> show parameter unusable
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
skip_unusable_indexes boolean TRUE
it is true by default) - but so what? It isn't usable so we skip it?
If you want it to fail, unset that default setting, set it to false.
local index dropped
August 23, 2010 - 3am Central time zone
Reviewer: A reader
Hi Tom,
I have following confusions about local indexes. It would be great if you can explain the same.
we have a range partitoned table and loacl index in the following way.
craete table tab1
(....)
partiton(...)
(.. part1..,
..part2..,
..part3..)
create index idx ...
local
(.. part1..,
..part2..,
..part3..)
1. If we drop one partition of a table why all the local indexes(part1,part2,part3) get dropped but
idx remains in database? I couldn't find index in dba_part_indexes view
2. In the above case if we add a new partion how we will create indexes using idx.
3. What is the difference of creating local indexes by
create index CREATE INDEX year_idx
on all_facts (order_year)
LOCAL
and
CREATE INDEX year_idx
on all_facts (order_year)
LOCAL
(partion_name,
....
)
Because in both the cases i found sub_objects are created as par partiton names.
Followup August 26, 2010 - 9am Central time zone:
it does not work the way you describe:
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 )
12 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t(x) local
2 ( partition I_part1, partition I_part2 );
Index created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select partition_name from user_tab_partitions where table_name = 'T';
PARTITION_NAME
------------------------------
PART1
PART2
ops$tkyte%ORA10GR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';
PARTITION_NAME
------------------------------
I_PART1
I_PART2
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t drop partition part1;
Table altered.
ops$tkyte%ORA10GR2> select partition_name from user_tab_partitions where table_name = 'T';
PARTITION_NAME
------------------------------
PART2
ops$tkyte%ORA10GR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';
PARTITION_NAME
------------------------------
I_PART2
that shows if you drop a table partition, only the affected index partition goes away... so that
is counter to your #1
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t add partition part3 values less than
(to_date('15-mar-2003','dd-mon-yyyy'));
Table altered.
ops$tkyte%ORA10GR2> select partition_name from user_tab_partitions where table_name = 'T';
PARTITION_NAME
------------------------------
PART3
PART2
ops$tkyte%ORA10GR2> select partition_name from user_ind_partitions where index_name = 'T_IDX';
PARTITION_NAME
------------------------------
PART3
I_PART2
that shows if you add a table partition, we automagically add the local index partition for you -
#2
and the difference between just "local" and "local (partition x, partition y)" is that the former local names the partitions after the table partitions (sensible) and the latter local (partition ....) lets you name the partitions differently (not as sensible in my opinion)
Partition On table.
September 2, 2010 - 11pm Central time zone
Reviewer: Snehasish Das from Kolkata , India
Hi Tom,
I was thinking if we could set the partition of the tables as unused. Suppose we have a partition
in which we think the data is corrupted and we dont want our query to fetch data from that
partition. Is there any way we can set the table partition to be unused. I think we can set the
tablespace offline for that partition but that is a dba stuff. Can you tell us if it is possible
like setting the index unused.
Regards,
Snehasish Das
Followup September 9, 2010 - 7pm Central time zone:
create an empty table that looks like the partitioned table, create indexes on it to match the local indexes on the partitioned table, exchange that empty table with the partition you believe to be logically corrupt.
if the partition is physically corrupt, you would have to media recovery it or drop it (and then restore it later and add it back in)
you cannot "set unused" a partition, you have to remove it. setting an index unused doesn't "change the answer to queries", setting a table partition 'unused' would. therefore you need to drop it.
Index on Partition
October 7, 2011 - 4pm Central time zone
Reviewer: Ananth from Richmond VA, USA
Hi Tom,
Can i create index only on one of the partitions.
we Have 66 paritions and we dont require indexes on 65 paritions.
Is there any way to create index only on 66th partition..?
Regards
Ananth
|