9 Replies Latest reply: Jul 19, 2013 5:33 AM by Sven W. RSS

    Please help how to improve the performance of this query.

    user9077483

      Hi All,

       

       

      This is my main query as of now it's not returning any rows.

      It's taking 1 minute to display the result for no rows.

      The cost of the query is 617483.

      In this query V_QUALITY_CONTROL_REPORT_DATA is a view.

       

       

      [code]SELECT   FAX_ID,

               FAX_OFFER_ID,

               HOLD_TYPE,

               ACTION_OWNER,

               ORDER_NUMBER,

               OMEGA_ORDER_NUMBER,

               COUNTRY_NAME,

               CUSTOMER_PREFIX,

               DEFAULT_COUNTRY_CODE AS COUNTRY_CODE,

               PO_NUMBER,

               OFFER_NUMBER,

               REVENUE,

               CREATED_BY,

               CREATION_DATE,

               CUSTOMER_NAME,

               CUSTOMER_NUMBER,

               SALESPERSON_NAME,

               SALESPERSON_EMAIL,

               PAYMENT_TYPE,

               AGED_DAYS,

               RESULT_OF_ORDER_DATA_CALL,

               ORDER_SEGMENT,

               ORDER_CREATOR,

               ORDER_CREATOR_EMAIL,

               CUSTOMER_BASE,

               OMEGA_CUSTOMER_NUMBER,

               CUSTOMER_BILL,

               LAST_TRANSITION_DATE,

               LAST_TRANSITION_CHANGED_BY,

               LAST_TRANSITION_WORKGROUP_FROM,

               LAST_TRANSITION_WORKGROUP_TO,

               LAST_TRANSITION_REASON,

               LAST_TRANSITION_COMMENT,

               TOTAL_SELLING_PRICE,

               CURRENCY_CODE

        FROM   ORL.V_QUALITY_CONTROL_REPORT_DATA

      WHERE   AGED_DAYS < 8;

       

       

      --V_QUALITY_CONTROL_REPORT_DATA

      The below query is the definition of the view.

      This query is returning 2162761 records.

      The cost of the query is 809487.

      In this query FAX_LAST_TRANSITION_V is a view.

       

       

      SELECT   FH.ID AS FAX_ID,

                  FOD.ID AS FAX_OFFER_ID,

                  'ORL Rejection' AS HOLD_TYPE,

                  'Sales' AS ACTION_OWNER,

                  FOD.ORDER_NUMBER,

                  FOD.OMEGA_ORDER_NUMBER,

                  BU_MAPPING.COUNTRY_NAME,

                  BU_MAPPING.CUSTOMER_PREFIX,

                  BU_MAPPING.DEFAULT_COUNTRY_CODE,

                  FOD.PURCHASE_ORDER_NUMBER AS PO_NUMBER,

                  FOD.OFFER_NUMBER,

                  FOD.VALUE AS REVENUE,

                  FOD.CREATED_BY,

                  FOD.CREATION_DATE,

                  FH.CUSTOMER_NAME,

                  FH.CUSTOMER_NUMBER,

                  SPV.salesperson_name,

                  SPV.email AS SALESPERSON_EMAIL,

                  FH.PAYMENT_TYPE,

                  ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS,

                  ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_DATA (BU_MAPPING.BU_ID,

                                                                 FOD.ORDER_NUMBER)

                     AS RESULT_OF_ORDER_DATA_CALL,

                  ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_SEGMENT ()

                     AS ORDER_SEGMENT,

                  ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR ()

                     AS ORDER_CREATOR,

                  ORL.ORL_QUALITY_CONTROL_HELPER.GET_ORDER_CREATOR_EMAIL ()

                     AS ORDER_CREATOR_EMAIL,

                  ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BASE ()

                     AS CUSTOMER_BASE,

                  REPLACE (

                     ORL.ORL_QUALITY_CONTROL_HELPER.GET_OMEGA_CUSTOMER_NUMBER (),

                     BU_MAPPING.CUSTOMER_PREFIX

                  )

                     AS OMEGA_CUSTOMER_NUMBER,

                  ORL.ORL_QUALITY_CONTROL_HELPER.GET_CUSTOMER_BILL ()

                     AS CUSTOMER_BILL,

                  ORL.ORL_QUALITY_CONTROL_HELPER.GET_TOTAL_SELLING_PRICE ()

                     AS TOTAL_SELLING_PRICE,

                  ORL.ORL_QUALITY_CONTROL_HELPER.GET_CURRENCY_CODE ()

                     AS CURRENCY_CODE,

                  LTH.CHANGED_DATE AS LAST_TRANSITION_DATE,

                  LTH.CHANGED_BY AS LAST_TRANSITION_CHANGED_BY,

                  LTH.FROM_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_FROM,

                  LTH.TO_WORKGROUP_DESCRIPTION AS LAST_TRANSITION_WORKGROUP_TO,

                  LTH.REASON_DESCRIPTION AS LAST_TRANSITION_REASON,

                  LTH.COMMENTS AS LAST_TRANSITION_COMMENT

           FROM               ORL.FAX_OFFER_DETAIL FOD

                           INNER JOIN

                              ORL.FAX_HEADER FH

                           ON FH.ID = FOD.FAX_ID

                        INNER JOIN

                              ORL.FAX_SOURCE FS

                           INNER JOIN

                              APPS_GLOBAL.GLOBAL_BU_MAPPING BU_MAPPING

                           ON BU_MAPPING.BU_ID = FS.BUID

                        ON FS.ID = FH.FAX_SOURCE

                     LEFT OUTER JOIN

                        ORL.FAX_LAST_TRANSITION_V LTH

                     ON LTH.FAX_ID = FH.ID

                  LEFT OUTER JOIN

                     ORL.SALESPERSON_V SPV

                  ON SPV.salesperson_id = FH.SALES_PERSON;

       

       

      --FAX_LAST_TRANSITION_V

       

       

      The below query is the definition of the view.

      This query is returning 2377476 records.

      The cost of the query is 69614.

       

       

         SELECT   FH.ID AS FAX_ID,

                  FROMW.ID AS FROM_WORKGROUP_ID,

                  FROMW.NAME AS FROM_WORKGROUP_NAME,

                  FROMW.DESCRIPTION AS FROM_WORKGROUP_DESCRIPTION,

                  TOW.ID AS TO_WORKGROUP_ID,

                  TOW.NAME AS TO_WORKGROUP_NAME,

                  TOW.DESCRIPTION AS TO_WORKGROUP_DESCRIPTION,

                  WTR.ID AS REASON_ID,

                  WTR.REASON AS REASON_DESCRIPTION,

                  FTH.CHANGED_BY,

                  FTH.CHANGED_DATE,

                  FTH.COMMENTS,

                  FTH.IMPERSONATED_BY

           FROM                  ORL.FAX_HEADER FH

                              INNER JOIN

                                 ORL.FAX_TRANSITION_HISTORY FTH

                              ON FH.LAST_TRANSITION_ID = FTH.ID

                           INNER JOIN

                              ORL.WORKGROUP_TRANSITION_REASON WTR

                           ON WTR.ID = FTH.TRANSITION_REASON_ID

                        INNER JOIN

                           ORL.WORKGROUP_TRANSITION WT

                        ON WTR.WORKGROUP_TRANSITION_ID = WT.ID

                     INNER JOIN

                        ORL.WORKGROUP FROMW

                     ON WT.CURRENT_WORKGROUP_ID = FROMW.ID

                  INNER JOIN

                     ORL.WORKGROUP TOW

                  ON WT.NEXT_WORKGROUP_ID = TOW.ID;[/code]

       

       

      Total number of records in each table and view.

       

       

      [code]SELECT COUNT(*) FROM ORL.FAX_OFFER_DETAIL;--3210202

       

      SELECT COUNT(*) FROM ORL.FAX_HEADER;--2423269

       

      SELECT COUNT(*) FROM ORL.FAX_SOURCE;--2368

       

      SELECT COUNT(*) FROM GLOBAL_BU_MAPPING;--9

       

      SELECT COUNT(*) FROM ORL.FAX_LAST_TRANSITION_V;--2377476

       

      SELECT COUNT(*) FROM ORL.SALESPERSON_V;--24639

       

      SELECT COUNT(*) FROM ORL.FAX_TRANSITION_HISTORY--3019203

       

      SELECT COUNT(*) FROM ORL.WORKGROUP_TRANSITION_REASON--10754

         

      SELECT COUNT(*) FROM ORL.WORKGROUP_TRANSITION--6193

         

      SELECT COUNT(*) FROM ORL.WORKGROUP--1388

         

      SELECT COUNT(*) FROM ORL.V_QUALITY_CONTROL_REPORT_DATA--2162761[/code]

       

       

      Please help how to improve the performance of this query.

       

       

      Thanks in advance.

        • 1. Re: Please help how to improve the performance of this query.
          Hoek

          Database version?

          Execution plan(s) and/or tkprof output?

          Indexes?

          ...

          See:

          https://forums.oracle.com/thread/865295

          • 2. Re: Please help how to improve the performance of this query.
            Sven W.

            The problem with this query could be that you are filtering on column FH.DATE_RECEIVED, however the filter logic can't take advantage of any index on this column. What datatype is this column and is there an index on it? First thing I would do is expose the column DATE_RECIEVED into the outer most view.

            Then find a way to filter on this using a construct where the column is on one side and an expression is on the other side.

             

             

            Instead of

            ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)) AS AGED_DAYS
            ...
            where aged_days < 8
            

             

            do something like this

            where x.DATE_RECEIVED > trunc(sysdate) - 8
            

             

            Kindly note that the output is different, especially because of the rounding logic.

            • 3. Re: Please help how to improve the performance of this query.
              user9077483

              The data type of the column DATE_RECIEVED is  TIMESTAMP(6).

              And it has the normal index.

              I am posting the indexes on all the tables being used in this query.

               

               

              Indexes on FAX_HEADER table.

               

               

              CREATE BITMAP INDEX APPS_GLOBAL.BITMAPINDX_WORKGROUP_ID_RAM ON ORL.FAX_HEADER(WORKGROUP_ID)

               

               

              CREATE INDEX ORL.FAX_HEADER_FS_IDX ON ORL.FAX_HEADER(FAX_SOURCE)

               

               

              CREATE INDEX ORL.FAX_HEADER_IDX2 ON ORL.FAX_HEADER(UPPER("CUSTOMER_NAME"))

               

               

              CREATE INDEX ORL.FAX_HEADER_IDX4 ON ORL.FAX_HEADER(DATE_RECEIVED)

               

               

              CREATE INDEX ORL.FAX_HEADER_IDX5 ON ORL.FAX_HEADER(SALES_PERSON)

               

               

              CREATE INDEX ORL.FAX_HEADER_IDX6 ON ORL.FAX_HEADER(CUSTOMER_OMEGA_NUMBER)

               

               

              CREATE INDEX ORL.FAX_HEADER_SUBJ_IDX2 ON ORL.FAX_HEADER( REGEXP_SUBSTR (UPPER("SUBJECT"),U'(QUOTE|DEVIS)005CD{0,}005Cd{3,}',1,1,'i',1))

               

               

              CREATE INDEX ORL.IDX_FAX_HEADER_LAST_TRANSITION ON ORL.FAX_HEADER(LAST_TRANSITION_ID)

               

               

              CREATE BITMAP INDEX ORL.IDX_FAX_HEADER_SPECIAL_RULE ON ORL.FAX_HEADER(IS_SPECIAL_RULE_APPLIED)

               

               

              CREATE INDEX ORL.IDX_FAX_HEADER_SUBJECT ON ORL.FAX_HEADER(SUBJECT)

               

               

              CREATE INDEX ORL.IDX_FAX_HEADER_UPPER_SUBJECT ON ORL.FAX_HEADER(UPPER("SUBJECT"))

               

               

              CREATE UNIQUE INDEX ORL.PK_FAX_HEADER ON ORL.FAX_HEADER(ID)

               

               

              Indexes on FAX_OFFER_DETAIL table.

               

               

              CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX1 ON ORL.FAX_OFFER_DETAIL(FAX_ID)

               

               

              CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX2 ON ORL.FAX_OFFER_DETAIL(SOURCE_OFFER_NO)

               

               

              CREATE INDEX ORL.FAX_OFFER_DETAIL_IDX3 ON ORL.FAX_OFFER_DETAIL(VERSION_NO)

               

               

              CREATE INDEX ORL.IDX_OFFER_NUMBER ON ORL.FAX_OFFER_DETAIL(OFFER_NUMBER)

               

               

              CREATE INDEX ORL.IDX_OMEGA_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(OMEGA_ORDER_NUMBER)

               

               

              CREATE INDEX ORL.IDX_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(ORDER_NUMBER)

               

               

              CREATE INDEX ORL.IDX_PURCHASE_ORDER_NUMBER ON ORL.FAX_OFFER_DETAIL(PURCHASE_ORDER_NUMBER)

               

               

              CREATE UNIQUE INDEX ORL.PK_FAX_OFFER_DETAIL ON ORL.FAX_OFFER_DETAIL(ID)

               

               

              Indexes on FAX_SOURCE table.

               

               

              CREATE INDEX APPS_GLOBAL.BITMAPINDX_WORKFLOW_ID_RAM ON ORL.FAX_SOURCE(WORKFLOW_ID)

               

               

              CREATE INDEX ORL.IDX_BUID_RAM ON ORL.FAX_SOURCE(BUID)

               

               

              CREATE INDEX ORL.IDX_FAX_NUMBER_RAM ON ORL.FAX_SOURCE(UPPER("FAX_NUMBER"))

               

               

              CREATE BITMAP INDEX ORL.IDX_IS_DELETED_RAM ON ORL.FAX_SOURCE(IS_DELETED)

               

               

              CREATE UNIQUE INDEX ORL.PK_FAX_SOURCE ON ORL.FAX_SOURCE(ID)

               

               

              CREATE UNIQUE INDEX ORL.UK_BU_FAX_SOURCE_NAME ON ORL.FAX_SOURCE(BUID, FAX_NUMBER)

               

               

              Indexes on FAX_TRANSITION_HISTORY table.

               

               

              CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX1 ON ORL.FAX_TRANSITION_HISTORY(FAX_ID, ID)

               

               

              CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX2 ON ORL.FAX_TRANSITION_HISTORY(TRANSITION_REASON_ID)

               

               

              CREATE INDEX ORL.FAX_TRANSITION_HISTORY_IDX3 ON ORL.FAX_TRANSITION_HISTORY(CHANGED_DATE)

               

               

              CREATE UNIQUE INDEX ORL.PK_FAX_TRANSITION_HISTORY ON ORL.FAX_TRANSITION_HISTORY(ID)

               

               

              Indexes on WORKGROUP_TRANSITION_REASON table.

               

               

              CREATE UNIQUE INDEX ORL.PK_WORKGROUP_TRANSITION_REASON ON ORL.WORKGROUP_TRANSITION_REASON(ID)

               

               

              CREATE UNIQUE INDEX ORL.UK_TRANSITION_REASON ON ORL.WORKGROUP_TRANSITION_REASON(WORKGROUP_TRANSITION_ID, REASON)

               

               

              CREATE INDEX ORL.WORKGROUP_TRANS_REASON_IDX1 ON ORL.WORKGROUP_TRANSITION_REASON(IS_DELETED)

               

               

              Indexes on WORKGROUP_TRANSITION table.

               

               

              CREATE UNIQUE INDEX ORL.PK_WORKGROUP_TRANSITION ON ORL.WORKGROUP_TRANSITION(ID)

               

               

              CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX1 ON ORL.WORKGROUP_TRANSITION(CURRENT_WORKGROUP_ID)

               

               

              CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX2 ON ORL.WORKGROUP_TRANSITION(NEXT_WORKGROUP_ID)

               

               

              CREATE INDEX ORL.WORKGROUP_TRANSITION_IDX3 ON ORL.WORKGROUP_TRANSITION(IS_DELETED)

               

               

              Indexes on WORKGROUP table.

               

               

              CREATE UNIQUE INDEX ORL.PK_WORKGROUP ON ORL.WORKGROUP(ID)

               

               

              CREATE INDEX ORL.WORKGROUP_IDX1 ON ORL.WORKGROUP(BUID)

               

               

              CREATE INDEX ORL.WORKGROUP_IDX2 ON ORL.WORKGROUP(IS_ACTIVE)

               

               

              CREATE INDEX ORL.WORKGROUP_IDX3 ON ORL.WORKGROUP(IS_DELETED)

               

               

              CREATE INDEX ORL.WORKGROUP_IDX4 ON ORL.WORKGROUP(IS_EMC)

               

               

              CREATE INDEX ORL.WORKGROUP_IDX5 ON ORL.WORKGROUP(IS_BACKLOG)

               

               

              CREATE INDEX ORL.WORKGROUP_IDX6 ON ORL.WORKGROUP(DESCRIPTION)

               

               

               

               

              Explain plan for FAX_LAST_TRANSITION_V view.

               

               

              PLAN_TABLE_OUTPUT

              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

               

               

              Plan hash value: 115069906

               

               

              ----------------------------------------------------------------------------------------------------------------------

              | Id  | Operation                   | Name                           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

              ----------------------------------------------------------------------------------------------------------------------

              |   0 | SELECT STATEMENT            |                                |  2377K|   578M|       | 39584   (1)| 00:07:56 |

              |*  1 |  HASH JOIN                  |                                |  2377K|   578M|       | 39584   (1)| 00:07:56 |

              |   2 |   TABLE ACCESS FULL         | WORKGROUP                      |  1388 | 80504 |       |    14   (0)| 00:00:01 |

              |*  3 |   HASH JOIN                 |                                |  2377K|   446M|       | 39555   (1)| 00:07:55 |

              |   4 |    TABLE ACCESS FULL        | WORKGROUP                      |  1388 | 80504 |       |    14   (0)| 00:00:01 |

              |*  5 |    HASH JOIN                |                                |  2377K|   315M|       | 39525   (1)| 00:07:55 |

               

               

              PLAN_TABLE_OUTPUT

              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

               

               

              |   6 |     TABLE ACCESS FULL       | WORKGROUP_TRANSITION           |  6193 | 80509 |       |    25   (0)| 00:00:01 |

              |*  7 |     HASH JOIN               |                                |  2377K|   285M|       | 39485   (1)| 00:07:54 |

              |   8 |      TABLE ACCESS FULL      | WORKGROUP_TRANSITION_REASON    | 10754 |   535K|       |    66   (0)| 00:00:01 |

              |*  9 |      HASH JOIN              |                                |  2377K|   170M|    55M| 39403   (1)| 00:07:53 |

              |  10 |       VIEW                  | index$_join$_001               |  2423K|    27M|       | 17672   (1)| 00:03:33 |

              |* 11 |        HASH JOIN            |                                |       |       |       |         |             |

              |  12 |         INDEX FAST FULL SCAN| IDX_FAX_HEADER_LAST_TRANSITION |  2423K|    27M|       |  7171   (1)| 00:01:27 |

              |  13 |         INDEX FAST FULL SCAN| PK_FAX_HEADER                  |  2423K|    27M|       |  6831   (1)| 00:01:22 |

              |  14 |       TABLE ACCESS FULL     | FAX_TRANSITION_HISTORY         |  3019K|   181M|       |  8212   (1)| 00:01:39 |

              ----------------------------------------------------------------------------------------------------------------------

               

               

               

               

              PLAN_TABLE_OUTPUT

              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

               

               

              Predicate Information (identified by operation id):

              ---------------------------------------------------

               

               

                 1 - access("WT"."NEXT_WORKGROUP_ID"="TOW"."ID")

                 3 - access("WT"."CURRENT_WORKGROUP_ID"="FROMW"."ID")

                 5 - access("WTR"."WORKGROUP_TRANSITION_ID"="WT"."ID")

                 7 - access("WTR"."ID"="FTH"."TRANSITION_REASON_ID")

                 9 - access("FH"."LAST_TRANSITION_ID"="FTH"."ID")

                11 - access(ROWID=ROWID)

               

               

              31 rows selected.

               

               

              Please help me.

               

               

              Thanks.

              • 4. Re: Please help how to improve the performance of this query.
                Sven W.

                user9077483 wrote:

                 

                The data type of the column DATE_RECIEVED is  TIMESTAMP(6).

                And it has the normal index.

                ...

                if it is timestamp, then do a condition with a timestamp expression like

                 

                where x.DATE_RECEIVED >= trunc(systimestamp) - interval '8' day


                btw. It would help if you state your functional requirement for the condition. Instead of abstract and potentially faulty code. For example a 7 day interval might be the better choice.


                • 5. Re: Please help how to improve the performance of this query.
                  user9077483

                  I have inserted the following records into table for testing purpose.

                   

                   

                  DATE_RECEIVED               ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE))   SYSDATE-CAST(FH.DATE_RECEIVEDASDATE)        SYSDATE   

                   

                   

                  7/7/2013 12:18:56.095550 PM    4                                                   3.66002314814815                      7/11/2013 4:09:22 AM   

                  7/3/2013 12:18:56.095550 PM    8                                                   7.66002314814815                      7/11/2013 4:09:22 AM

                  7/2/2013 12:18:56.095550 PM    9                                                   8.66002314814815                      7/11/2013 4:09:22 AM

                   

                   

                  If I use the below queries out put is different.

                   

                   

                  SELECT  FH.DATE_RECEIVED, ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)),SYSDATE - CAST (FH.DATE_RECEIVED AS DATE),

                  FROM ORL.FAX_HEADER FH

                  WHERE ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE))<8;

                   

                   

                  DATE_RECEIVED                ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE))   SYSDATE-CAST(FH.DATE_RECEIVEDASDATE)        SYSDATE   

                   

                   

                  7/7/2013 12:18:56.095550 PM       4                                                   3.66002314814815                      7/11/2013 4:09:22 AM

                   

                   

                  SELECT  FH.DATE_RECEIVED, ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)),SYSDATE - CAST (FH.DATE_RECEIVED AS DATE)

                  FROM ORL.FAX_HEADER FH

                  WHERE FH.DATE_RECEIVED>=trunc(systimestamp) - interval '8' day;

                   

                   

                  DATE_RECEIVED               ROUND (SYSDATE - CAST (FH.DATE_RECEIVED AS DATE))   SYSDATE-CAST(FH.DATE_RECEIVEDASDATE)        SYSDATE   

                   

                   

                  7/7/2013 12:18:56.095550 PM    4                                                   3.66002314814815                      7/11/2013 4:09:22 AM   

                  7/3/2013 12:18:56.095550 PM    8                                                   7.66002314814815                      7/11/2013 4:09:22 AM

                   

                   

                  In this condition it's not considering time ,it's considering only date.

                  trunc(systimestamp) - interval '8' day

                   

                   

                  If we use interval '7' day also a proble.

                   

                   

                  The functionality is if the records is 7 days old it should be displayed.

                   

                   

                  What is the bset way.

                   

                   

                  Please help me.

                   

                   

                  Thanks in advance.

                  • 7. Re: Please help how to improve the performance of this query.
                    SomeoneElse

                    > Check below link it will help you.....

                     

                    Some of these are nonsense.  Sorry.


                    • 8. Re: Please help how to improve the performance of this query.
                      user9077483

                      Hi,

                       

                      Any body can please help me.

                       

                      Thanks.

                      • 9. Re: Please help how to improve the performance of this query.
                        Sven W.

                        Do you still have a performance issue when implementing the suggested solution?

                        Is the index on DATE_RECEIVED used now?

                         

                        Whether you do a 7 day or 8 day intervall is up to you. If the remaining problem ist simply to find the proper function, then post some sample data (you did, but it was hardly readable) but also post the expected result and an explaination, like "seven exact days in the past, that means time (hours) should be considered, so don't start at midnight"