#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0

    Using a Multiple Column Index


    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
  2. #2
  3. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    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,
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    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
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    I thought it was stupid to index by date
  10. #6
  11. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    308
    Rep Power
    11
    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.

IMN logo majestic logo threadwatch logo seochat tools logo