Hello All,
SELECT
SHAPE,
ADD_HOUSE_NUMBER_1 || ADD_HOUSE_SUFFIX_1 AS HOUSE_NUMBER_1,
ADD_HOUSE_SUFFIX_1 AS HOUSE_SUFFIX_1,
ADD_HOUSE_NUMBER_2 AS HOUSE_NUMBER_2,
ADD_HOUSE_SUFFIX_2 AS HOUSE_SUFFIX_2
FROM VMPROP.V_PROPERTY_MP_ADDRESS
WHERE (ADD_BLG_UNIT_ID_1 IS NULL
OR ADD_BLG_UNIT_ID_1 = 1)
AND PROP_STATUS = 'A';
The above query consumes lot of logical reads, though it has got indexes on all where condition, it uses only PROP_STATUS = 'A' index. As ADD_BLG_UNIT_ID_1 is null i have created a function-based index to include null values, but still it does not uses indexes. Please give me your valuable suggestion to improve this query.
LIOs LIOs
Stmt Id PIO blks LIOs Rows per Row Runs per Run
------------------- ----------------- -------------- ------------------ ---------------- --------------- ------------
1742159603 207,610 809,395 111,722 7 22 36,791
Regards,
joe