OraFAQ Forum: SQL & PL/SQL » Index status showing N/A

Home » SQL & PL/SQL » SQL & PL/SQL » Index status showing N/A

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Index status showing N/A [message #15354]

Mon, 04 October 2004 13:21

lily
Messages: 44
Registered: September 2000

Member

Index status showing N/A

My application is running fine.
I did analyze and estimate statistics of my indexes of 'hit' table. But even after the succesful analyzation, it is showing older date and the status is N/A. Pl advice. How do I make these indexes 'AVAILABLE'?

--------------------------
SQL> analyze index ISI_USAGE_ADMIN.HIT_PUBCODE_IDX estimate statistics;

Index analyzed.

SQL> analyze index ISI_USAGE_ADMIN.HIT_DOCID_IDX estimate statistics;

Index analyzed.

SQL> analyze index ISI_USAGE_ADMIN.HIT_SESSIONID_IDX estimate statistics;

Index analyzed.

SQL> analyze index ISI_USAGE_ADMIN.HIT_PPVYN_IDX estimate statistics;

Index analyzed.

SQL> analyze index ISI_USAGE_ADMIN.HIT_PK estimate statistics;

Index analyzed.

SQL> analyze index ISI_USAGE_ADMIN.HIT_ACCESSTIME_IDX estimate statistics;

Index analyzed.

SQL> analyze index ISI_USAGE_ADMIN.HIT_USERNAME_IDX estimate statistics;

Index analyzed.

SQL> analyze index ISI_USAGE_ADMIN.HIT_PROJECTCODE_IDX estimate statistics;

Index analyzed.

SQL> analyze index ISI_USAGE_ADMIN.HIT_SERVICCODE_IDX estimate statistics;
where table_name = 'HIT'; 2 3 4 5

INDEX_NAME LAST_ANALYZED STATUS
------------------------------ ------------------- --------
HIT_ACCESSTIME_IDX 2004-04-11 22:50:06 N/A
HIT_CUSTOMERID_IDX 2004-04-12 01:39:05 N/A
HIT_DOCID_IDX 2004-04-11 20:40:28 N/A
HIT_PK 2004-04-11 22:03:58 N/A
HIT_PPVYN_IDX 2004-04-11 21:41:11 N/A
HIT_PRODCODE_IDX 2004-04-12 01:04:05 N/A
HIT_PROFILEID_IDX 2004-04-12 01:39:06 N/A
HIT_PROJECTCODE_IDX 2004-04-11 23:55:00 N/A
HIT_PUBCODE_IDX 2004-04-11 20:06:37 N/A
HIT_SERVICCODE_IDX 2004-04-12 00:25:41 N/A
HIT_SESSIONID_IDX 2004-04-11 21:11:31 N/A

HIT_SERVICCODE_IDX 2004-04-12 00:25:41 N/A
HIT_SESSIONID_IDX 2004-04-11 21:11:31 N/A

HIT_SERVICCODE_IDX 2004-04-12 00:25:41 N/A
HIT_SESSIONID_IDX 2004-04-11 21:11:31 N/A

INDEX_NAME LAST_ANALYZED STATUS
------------------------------ ------------------- --------
HIT_USERNAME_ACCESSTIME_IDX 2004-04-11 19:33:29 N/A
HIT_USERNAME_IDX 2004-04-11 23:23:45 N/A
-----------------------------------------------------------

Report message to a moderator

Re: Index status showing N/A [message #15360 is a reply to message #15354]

Tue, 05 October 2004 02:12

Frank Naude
Messages: 4596
Registered: April 1998

Senior Member

Hi,

The "N/A" status normally indicates that the index is partitioned. To get the status, do a select from DBA_IND_PARTITIONS. Here is an example:

SQL> CONNECT sh/sh
Connected.

SQL> SELECT index_name, last_analyzed, status
  2    FROM user_indexes
  3   WHERE index_name = 'COSTS_PROD_BIX'
  4  /

INDEX_NAME                     LAST_ANAL STATUS
------------------------------ --------- --------
COSTS_PROD_BIX                 23-FEB-04 N/A

SQL> SELECT index_name, partition_name, last_analyzed, status
  2    FROM user_ind_partitions
  3   WHERE index_name = 'COSTS_PROD_BIX'
  4  /

INDEX_NAME                     PARTITION_NAME                 LAST_ANAL STATUS
------------------------------ ------------------------------ --------- --------
COSTS_PROD_BIX                 COSTS_1995                     23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_1996                     23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_H1_1997                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_H2_1997                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q1_1998                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q1_1999                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q1_2000                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q1_2001                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q1_2002                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q1_2003                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q2_1998                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q2_1999                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q2_2000                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q2_2001                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q2_2002                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q2_2003                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q3_1998                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q3_1999                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q3_2000                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q3_2001                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q3_2002                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q3_2003                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q4_1998                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q4_1999                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q4_2000                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q4_2001                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q4_2002                  23-FEB-04 USABLE
COSTS_PROD_BIX                 COSTS_Q4_2003                  23-FEB-04 USABLE

28 rows selected.

Best regards.

Frank

Report message to a moderator

Re: Index status showing N/A [message #15523 is a reply to message #15360]

Tue, 12 October 2004 10:45

lily
Messages: 44
Registered: September 2000

Member

Thanks a lot Frank.

regards,
Lily

Report message to a moderator

Previous Topic:	Help using VARRAYs
Next Topic:	stored procedure to compare 2 SQL statements

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 29 15:50:10 CDT 2025