Home » SQL & PL/SQL » SQL & PL/SQL » May be Cartesian Join
May be Cartesian Join [message #266786] Tue, 11 September 2007 11:41 Go to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Hi,

What i am trying to do is rename the partitioned indexes to be
same the table partition name, as we are adding new partitions to the table so the new ones will not match the old index partition names, so before adding new partitionst to the table i want to rename the index partitions,

It looks this select i have is doing cartesian Production gets multiple rows, what is missing in this
select  'alter index ' || c.index_name  || ' rename partition ' || c.partition_name || 
' to  ' ||b.partition_name    from 
user_indexes a,user_tab_partitions b,user_ind_partitions c 
where a.table_name=b.table_name
and a.index_name=c.index_name 
and a.PARTITIONED='YES'
and b.table_name='ABC'


Oracle is 9.2.0.7


Thanks

[Updated on: Tue, 11 September 2007 11:42]

Report message to a moderator

Re: May be Cartesian Join [message #266790 is a reply to message #266786] Tue, 11 September 2007 11:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Assuming this is local indexes (otherwise this does not work), partition_position must be the same for index and table partitions.

Regards
Michel
Re: May be Cartesian Join [message #266792 is a reply to message #266790] Tue, 11 September 2007 11:53 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Thanks Michel

Yes what i am trying to rename is for Local Partitioned Indexes only.
Is my select statement OK , but i am getting cartesian result.


Regards

[Updated on: Tue, 11 September 2007 11:53]

Report message to a moderator

Re: May be Cartesian Join [message #266793 is a reply to message #266792] Tue, 11 September 2007 11:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do what I said: equal partition_position

Regards
Michel
icon14.gif  Re: May be Cartesian Join [message #266798 is a reply to message #266793] Tue, 11 September 2007 12:07 Go to previous messageGo to next message
feroze
Messages: 144
Registered: September 2005
Location: chicago
Senior Member
Thanks Michel, Great.

One quick clarification can this

Alter index rename be run ONLINE (on an OLTP 24/7) will it effect or hold locks on index.


thanks Again

[Updated on: Tue, 11 September 2007 12:10]

Report message to a moderator

Re: May be Cartesian Join [message #266801 is a reply to message #266798] Tue, 11 September 2007 12:20 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Rename is just an update in the dictionary, it will lock dictionary rows not the index itself (or during a very short time).
ONLINE is useless (I don't even know if you can specify it in this case).

Regards
Michel
Previous Topic: Bind variables with DBMS_XMLGEN
Next Topic: number datatype
Goto Forum:
  


Current Time: Mon Dec 05 18:58:30 CST 2016

Total time taken to generate the page: 0.12445 seconds