Home » Developer & Programmer » Reports & Discoverer » matrix report takes time (reports 6i)
matrix report takes time [message #281446] Fri, 16 November 2007 22:53 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
hello sir

i have created user entry report which takes long time.
there's few tables which scans fully.

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Hint=CHOOSE		  	 	 	 	      	             	 
  UNION-ALL		  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      NESTED LOOPS		  	 	 	 	      	             	 
        TABLE ACCESS FULL	DT_GATE_ENTRY	  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
          INDEX UNIQUE SCAN	PK_MKUSER	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	HD_MAT_REQUISITION	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_HD_MAT_REQ	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	HD_MAT_REQUISITION	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_HD_MAT_REQ	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	DT_RECEIPT	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_DT_COMP_RCPT_ITEM	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	DT_DESP	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_DT_DESP	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_ITEM	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_ITEM	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_REASON	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_COMP_REASON	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_GROUP	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_MST_GROUP	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_SUB_GROUP	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_MST_SUB_GROUP	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_CATEGORY	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_CATG_CODE	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_COLOUR	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_COLOUR_CODE	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_GRADE	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_GRADE_CODE	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_SERIAL_NO	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_SNO	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	DT_RMDA	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_DT_RMDA	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	DT_REJECTION	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_DT_REJECTION	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	HD_PROC_GPASS	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_HD_PROC_GPASS	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	HD_PROC_GPASS	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_HD_PROC_GPASS	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	HD_PURCHASE_RFQ	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_HD_PUR_RFQ	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	HD_PUR_PRO_REQUISITION	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_HD_PUR_PRO_REQ	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	DT_PUR_ORDER	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_DT_PUR_ORDER	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	BILL_RESULT	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_BILL_RES	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_SUP_ITEM	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_COMP_SUP_ITM	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_VENDOR_ITEM	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_VEND_ITEM	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	HD_INDENT	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_HD_INDENT	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_CUSTOMER	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_CUSTOMER	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_CUSTOMER_LOC	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_CUST_LOC	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      NESTED LOOPS		  	 	 	 	      	             	 
        TABLE ACCESS FULL	DT_ENQUIRY_PRODUCT	  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
          INDEX UNIQUE SCAN	PK_MKUSER	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      NESTED LOOPS		  	 	 	 	      	             	 
        TABLE ACCESS FULL	DT_QUOTATION	  	 	 	 	      	             	 
        TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
          INDEX UNIQUE SCAN	PK_MKUSER	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_PRODUCT	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_PRODUCT_CODE_MST	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	DT_PROFORMA_INV	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_DT_PROF_INV	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	COMM_INVOICE	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_COMM_INVOICE	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_ACCOUNT	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_ACCOUNT_CODE	  	 	 	 	      	             	 
    SORT GROUP BY		  	 	 	 	      	             	 
      TABLE ACCESS BY INDEX ROWID	MST_CUST_PRODUCT	  	 	 	 	      	             	 
        NESTED LOOPS		  	 	 	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	MK_USERS	  	 	 	 	      	             	 
            INDEX RANGE SCAN	PK_MKUSER	  	 	 	 	      	             	 
          INDEX RANGE SCAN	PK_MST_CUST_PRODUCT	  	 	 	 	      	             	 



there's comparision with user and respective table is
.comp_code
.auth_code-user_code

now please advise me how will i optimise it, so that i take less time.

regards
Re: matrix report takes time [message #281476 is a reply to message #281446] Sat, 17 November 2007 03:24 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Did you create indexes on relevant columns used to join these tables?
Previous Topic: fresher with report 6i
Next Topic: How to Pass Parameter
Goto Forum:
  


Current Time: Fri Dec 09 11:25:49 CST 2016

Total time taken to generate the page: 0.05522 seconds