Home » SQL & PL/SQL » SQL & PL/SQL » Local Partitioned Index Status
Local Partitioned Index Status [message #238106] Wed, 16 May 2007 13:41 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,

Few clarification on Local Partitioned Indexes

We have partition tables with 120 partitions,
I have added local partitioned indexs equal to the number of partitions,
why it shows the status as N/A for partiioned index
select status from user_indexes where index_name='XAK_BAT_LBIDPRDTBATNB_P'
SQL> /

STATUS
--------
N/A


we gather daily schema stats in 2 different ways
1 for non partitioned tables
2 for partiotioned tables with 10% Options
using DBMS_STATS.gather_Table_stats('SCHEMA',TABLE_NAME,10);

I still see the status of these partioned indexes as N/A, So are these indexes available or not,
and why it shows the last_analyzed column for indexes as (Not Analyzed)

But to Verify i saw a plan of a Query it shows this partitioned Index, so confused why the status is N/A
Plan
SELECT STATEMENT  CHOOSECost: 1  Bytes: 171  Cardinality: 1  		
	2 TABLE ACCESS BY LOCAL INDEX ROWID BAT Cost: 1  Bytes: 171  Cardinality: 1  Partition #: 1  Partitions accessed #92	
		1 INDEX RANGE SCAN UNIQUE XAK_BAT_LBIDPRDTBATNB_P Cost: 1  Cardinality: 1  Partition #: 2  Partitions accessed #92



Thanks


Re: Local Partitioned Index Status [message #238109 is a reply to message #238106] Wed, 16 May 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As index is partitioned it has no status (N/A=Non Applicable), this is its partitions (or subpartitions) that have a status as each partition can have a different status.

Regards
Michel
Re: Local Partitioned Index Status [message #238159 is a reply to message #238109] Wed, 16 May 2007 21:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Look in user_ind_partitions to see the status of each partition.

Ross Leishman
Re: Local Partitioned Index Status [message #238368 is a reply to message #238159] Thu, 17 May 2007 12:04 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks All,

One questions is i am gathering STATS for partitoned table as
 dbms_stats.gather_table_stats(user,'TABLE_NAME','P_'||(to_Char(sysdate,'YYYYMM')),20);


Question is, since i am always gathering the stats for current partition, the index shows LAST_ANALYZED column in this table user_ind_partitions as NOT ANALYZED, so is there a diffrent approach for partitioned index i should adopt.

Confusion is , IS Optimizer getting the current STATS for the new partitioned index or not.

And going forward i did this
exec dbms_stats.gather_index_stats('SCHEMA','XIE_TXN_BATID_PRCDT_GPNB')

And now it shows Current Date on Last Analyzed Column of UNER_IND_PARTITIONS table so does this mean do we have to gather seperately for the local partiioned index we add.Please help and give some answer on it.



Regards

[Updated on: Thu, 17 May 2007 12:17]

Report message to a moderator

Re: Local Partitioned Index Status [message #238369 is a reply to message #238368] Thu, 17 May 2007 12:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add parameter "cascade=>TRUE" to your gather_table_stats statement.

Regards
Michel
Re: Local Partitioned Index Status [message #238371 is a reply to message #238369] Thu, 17 May 2007 12:41 Go to previous messageGo to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Thanks for follow up,


we are using this statement for stats of partitioned tables, where i can use cascade>=TRUE here
dbms_stats.gather_table_stats(user,'table_name','P_'||(to_Char(sysdate,'YYYYMM')),10)



Thanks
Re: Local Partitioned Index Status [message #238372 is a reply to message #238106] Thu, 17 May 2007 12:44 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
SQL> DESC DBMS_STATS
Previous Topic: Executing a trigger
Next Topic: Performance issue during delete
Goto Forum:
  


Current Time: Fri Dec 09 15:35:09 CST 2016

Total time taken to generate the page: 0.36794 seconds