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’
)