[ttally]

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
---> need to pull all asset numbers

[shafique]

Because your index is based on the text items those are using in the where clause so that whenever you use these columns, oracle uses the index and brings the result very fast. Try to create a new index on RO_REPAIR_DATE, if it works fine then OK. But i want to bring one thing in your notice that too many indexes also slow down the query performance. check how many indexes already in use for this table? If some of these are no longer in use drop it.


Regards,

[ttally]

I have asked the DBA to split out the date field from the index, but the DBA will not split the index. I don't want to have to get management involved in order to resolve. Do you have any other suggestions before I take any drastic steps? I have been researching this for quite a while with no luck.

Thanks,
Trevor

[shammat]

Try this

AND h.ASSET_NUMBER LIKE = h.ASSET_NUMBER LIKE
AND h.INFLUENCE_CODE LIKE = h.INFLUENCE_CODE LIKE

instead of your like '%'

If it doesn't speed up the query try to include those lines more often. Sometimes the query analyzer can be fooled by that, and will use the index

[metaBarf]

I thought it was stupid to index by date

[shafique]

Well, I can understand your problem. You can take the advantage of Materialized View that is a database object and contains the result of a query. You need 'Create any Materialized View' system grant to do so. By this Whenever your REPAIR_ORDER_HEADER table will be update, Oracle will update your view as well, this time you need to run query on materialized view.
The syntax is:
CRAETE MATERIALIZED VIEW mview_rp_ord_header
build immediate
refresh complete on commit
AS
SELECT...... //type your query here.


Regards,

I did not test it but i beleive it should work.