Help using Multi-Column Indexes - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Help using Multi-Column Indexes

TatorTator Posts: 2Member
I am pulling data from an Oracle 8i data warehouse. The DBA set up a multi-column index on a date and two text fields. I am having a difficult time figuring out how to pull the data with the benefit of the index when I only need to pull a date range. The query runs very quickly if I select something for both text fields, but very slowly if I only select the date range. Here's my query - the two text fields are h.ASSET_NUMBER and h.INLFUENCE_CODE:

SELECT REGION_DESCRIPTION, MONTH, COUNT(Value) as Total
FROM (SELECT h.REGION, r.REGION_DESCRIPTION,
CASE WHEN (h.RO_REPAIR_DATE - TO_CHAR(h.RO_REPAIR_DATE, 'DD') + 1) = TO_DATE('2003/07/01', 'yyyy/mm/dd') THEN TO_DATE('2003/07/01', 'yyyy/mm/dd')
WHEN (h.RO_REPAIR_DATE - TO_CHAR(h.RO_REPAIR_DATE, 'DD') + 1) = TO_DATE('2003/08/01', 'yyyy/mm/dd') THEN TO_DATE('2003/08/01', 'yyyy/mm/dd')
WHEN (h.RO_REPAIR_DATE - TO_CHAR(h.RO_REPAIR_DATE, 'DD') + 1) = TO_DATE('2003/09/01', 'yyyy/mm/dd') THEN TO_DATE('2003/09/01', 'yyyy/mm/dd')
WHEN (h.RO_REPAIR_DATE - TO_CHAR(h.RO_REPAIR_DATE, 'DD') + 1) = TO_DATE('2003/10/01', 'yyyy/mm/dd') THEN TO_DATE('2003/10/01', 'yyyy/mm/dd')
ELSE TO_DATE('1900/01/01', 'yyyy/mm/dd') END as Month,
h.RO_NUMBER as Value
FROM REPAIR_ORDER_HEADER h, REGION r
WHERE h.REGION = r.REGION
AND h.DIVISION = 144
AND h.ASSET_NUMBER LIKE '%' ---> need to pull all asset numbers
AND h.INFLUENCE_CODE LIKE '%' ---> need to pull all influence codes
AND h.RO_REPAIR_DATE >= TO_DATE('2003/07/01','yyyy/mm/dd')
AND h.RO_REPAIR_DATE <= TO_DATE('2003/10/31','yyyy/mm/dd'))
GROUP BY REGION_DESCRIPTION, MONTH

Any help would be greatly appreciated,
Trevor
Sign In or Register to comment.