Home » RDBMS Server » Performance Tuning » Index Use and Renaming Partitions
| Index Use and Renaming Partitions [message #188769] |
Mon, 21 August 2006 10:26  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hello
I have some doubt regarding Index on a single column and a composite
Unique Index
table A
cola1
colb2
colc1
Index on (cola1)
Unique index on (cola1,colb2)
In this case the first index is it waste of resources or not , Since we have cola1 as leading column if where clause gives cola1=some thing then that unique index
will be used or not.
I have consider this because like this there are many indexes which are on single column and then also the leading column in a composite Index (some times unique Composite and some cases only composite ) on very huge tables , Wanted to know whether in such cases can i remove the index single column ..
Please Suggest on this .
Another question on Renaming Partitions ?
I have already 12 tables partitined each having 132 partitions on monthly basis holding 10 years
of data a total of 1584 partitions.
I have named partitions like this <tablename>_PART_YYYYMM ( CAT_PART_199901) or (PROC_BAT_PART_200010)
Since the requirement is to change the naming convention now they want all the partitions to be just like this
P_YYYYMM (P_200010) or ( P_200607)
I need to change all 1584 partitions.
I know this command for a single partition renaming
alter table <table_name> rename partition P1 to P2 ;
But for whole 12 tables (1584 Partitions) any dynamic script to extract and rename in 1 step.In this i have to extract YYYYMM from old partition name and create a comman name for all partitions like P_YYYYMM, Since in the old partitions <table_name>_PART length varies not getting exact how to catch it, I tried this on one tables it is working , since i know the lenght of table name, can somebody guide me on this please to do in single step for 12 tables.
select ' alter table ' || table_name || ' rename partition ' || partition_name || ' to ' || ' P'|| substr(partition_name,9)|| ';' from
all_tab_partitions where table_name='DOC'
In this case the old name is like this "DOC_PART_200104"
and the new will be " P_200104"
Regards
[Updated on: Mon, 21 August 2006 11:39] Report message to a moderator |
|
|
|
| Re: Index Use and Renaming Partitions [message #188889 is a reply to message #188769] |
Tue, 22 August 2006 05:00  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You are right. If a B-Tree index is a leading subset of another B-Tree index, then it is redundant.
For your partition rename, try SUBSTR(partition_name, -6) to get the last 6 chars of the partition name.
Ross Leishman
|
|
|
|
| Re: Index Use and Renaming Partitions [message #188944 is a reply to message #188889] |
Tue, 22 August 2006 09:03 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks Ross for Reply
it worked with substr and also with this
select ' alter table ' || table_name || ' rename partition ' || partition_name
|| ' to ' || ' P_'|| SUBSTR(partition_name,instr(partition_name, 'PART_' ,1)+ 5 ,length(partition_name))|| ';' from
all_tab_partitions
Another question regarding this Index
CREATE UNIQUE INDEX XAK_USR_CUSID_GEUSRID ON USR ( DECODE(GE_USR_ID, NULL, NULL, CUST_ID), DECODE(CUST_ID, NULL, NULL, GE_USR_ID) );
Since a function is there it becomes Functional Index correct ! and if i say at SQL prompt
SQL> show parameter query
NAME TYPE VALUE
------------------------------------ ----------- -------------
query_rewrite_enabled string FALSE
query_rewrite_integrity string enforced
Does that mean if we use select statement involving this column with these parameter setting will the index be used,
Basically the reason for creating index like this is i was trying to have a Unique constraint on 2 column but since null are found, but still with using Decode i can create Index but not able to create Constraint
My aim is to avoid index and just create a Unique constraint with this option of Decode by which index is getting created.
The business rule is:
In the USR table, if the GE_USR_ID is not null, it cannot repeat for the same CUST_ID
GE_USR_ID CUST_ID
=========== ========
NULL 100 -- Valid
NULL 100 -- Valid
1 100 -- Valid
2 100 -- Valid
1 100 -- Not allowed. There already exists a GE_USR_ID = 1 for CUST_ID = 100
SQL> alter table usr add constraint XAK Unique ( GE_USR_ID,CUST_ID) using Index (CREATE UNIQUE INDEX
XAK ON USR ( DECODE(GE_USR_ID, NULL, NULL, CUST_ID), DECODE(CUST_ID, NULL, NULL, GE_USR_ID) )
2 /
alter table usr add constraint XAK Unique ( GE_USR_ID,CUST_ID) using Index (CREATE UNIQUE INDEX XAK
ERROR at line 1:
ORA-00921: unexpected end of SQL command
SQL> alter table usr add constraint XAK modify ( DECODE(GE_USR_ID, NULL, NULL, CUST_ID), DECODE(CUST
_ID, NULL, NULL, GE_USR_ID) ) constraint
2 XAK Unique;
alter table usr add constraint XAK modify ( DECODE(GE_USR_ID, NULL, NULL, CUST_ID), DECODE(CUST_ID,
*
ERROR at line 1:
ORA-00904: : invalid identifier
Thanks Again !
[Updated on: Tue, 22 August 2006 10:03] Report message to a moderator |
|
|
|
| Re: Index Use and Renaming Partitions [message #189106 is a reply to message #188944] |
Wed, 23 August 2006 04:31 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
You're on the right track. The SHOW PARAMETER command has nothing to do with function-based indexes though.
- You cannot create a unique/PK constraint using functions - you can only use basic columns. You can create a UNIQUE function-based index though which does the same thing.
- Unique and PK constraints use indexes to enforce themselves, so you cannot have a constraint without an index unless the constraint is unenforced.
Ross Leishman
|
|
|
|
Goto Forum:
Current Time: Fri May 03 09:03:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
