Toad World® Forums

invalid indexes show twice in the INVALID OBJECTS pane

Hi folks,

Very small thing in TOAD 10.6.1.3. I noticed that my invalid indexes have double
entries in the INVALID OBJECTS pane.

I sniffed the query and its down below. I think the logic got jumbled as either
you should filter indexes out of the top query or just UNION them to remove
duplicates.

A secondary issue is that it’s the nature of domain spatial indexes to
occasionally go bad per the DOMIDX_STATUS field but still have a STATUS of
VALID. It would be helpful to have TOAD also check DOMIDX_STATUS as well as
STATUS and show these problems as well.

Cheers,

Paul

SELECT

‘NHDPLUS’ owner,

object_name,

object_type,

LAST_DDL_TIME,

object_id

FROM

sys.user_objects o

WHERE

status <> ‘VALID’ AND

object_type <> ‘SYNONYM’

UNION ALL

SELECT

‘NHDPLUS’ owner,

object_name,

object_type,

LAST_DDL_TIME,

object_id

FROM

sys.user_objects

WHERE

object_type = ‘INDEX’ AND

object_name in (

SELECT

index_name

FROM

user_indexes

WHERE

status = ‘UNUSABLE’

UNION ALL

SELECT

index_name

FROM

user_ind_partitions ip

WHERE

status = ‘UNUSABLE’

UNION ALL

SELECT

index_name

FROM

user_ind_subpartitions isp

WHERE

status = ‘UNUSABLE’

)

sorry for the delay in replying. This will be fixed in the next beta.