| matrix report takes time [message #281446] |
Fri, 16 November 2007 22:53  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
