Home » RDBMS Server » Performance Tuning » Index Use and Renaming Partitions
Index Use and Renaming Partitions [message #188769] Mon, 21 August 2006 10:26 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Temp segment issue
Next Topic: Udmp Traces
Goto Forum:
  


Current Time: Wed Dec 07 11:06:04 CST 2016

Total time taken to generate the page: 0.18615 seconds