Home » RDBMS Server » Performance Tuning » need help with rewriting the query (8.0.6)
| need help with rewriting the query [message #456174] |
Sat, 15 May 2010 09:56  |
ateeqrahman786
Messages: 52
Registered: December 2009
Location: Hyderabad,India
|
Member |
|
|
Hi,
i have traced the query and the tkprof result is as follows:
SELECT BUS_UNIT,REQUEST_NO,REQUEST_DATE,REQUEST_TIME,REG_NO,TOP_BRASS_YN,REQUEST_ADMT_THEATRE_FLAG,PT_CODE,PT_NAME,CTYP_CUST_TYPE,CUST_CUSTO MER,PRIORTY
FROM
A_INP_THEATRE_ADMT WHERE bus_unit = :1 and trunc(request_date) >= (trunc(sysdate)-90) order by request_no desc
call count cpu elapsed disk query current rows
------- ------ ---- --------- ----- ------- ------ ------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 7 0.11 0.10 0 36606 0 77
------- ------ -------- ---------- ---------- ---- ----
total 9 0.11 0.12 0 36606 0 77
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 675
Rows Row Source Operation
------- ---------------------------------------------------
81 SORT ORDER BY
81 VIEW A_INP_THEATRE_ADMT
81 SORT UNIQUE
81 UNION-ALL
46 NESTED LOOPS
7629 TABLE ACCESS BY INDEX ROWID A_INP_THEATRE_REQUEST
7630 INDEX RANGE SCAN (object id 418783)
59 TABLE ACCESS BY INDEX ROWID A_PT_MASTER
118 INDEX RANGE SCAN (object id 5282)
35 NESTED LOOPS
28279 TABLE ACCESS BY INDEX ROWID A_INP_ADMT_MSSG
28280 INDEX RANGE SCAN (object id 418787)
43 TABLE ACCESS BY INDEX ROWID A_PT_MASTER
86 INDEX RANGE SCAN (object id 5282)
---------------------------------------------
could someone please rewrite the query as "A_INP_THEATRE_ADMT" is a view.Its accessing 36606 rows to fetch just 77 rows.
Regards,
Ateeq
[Updated on: Sat, 15 May 2010 10:00] Report message to a moderator |
|
|
|
|
|
| Re: need help with rewriting the query [message #456204 is a reply to message #456176] |
Sun, 16 May 2010 00:01  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
SELECT BUS_UNIT,REQUEST_NO,REQUEST_DATE,REQUEST_TIME,REG_NO,TOP_BRASS_YN,REQUEST_ADMT_THEATRE_FLAG,PT_CODE,PT_NAME,CTYP_CUST_TYPE,CUST_CUSTO MER,PRIORTY
FROM
A_INP_THEATRE_ADMT WHERE bus_unit = :1 and trunc(request_date) >= (trunc(sysdate)-90) order by request_no desc
is functionally identical to
SELECT BUS_UNIT,REQUEST_NO,REQUEST_DATE,REQUEST_TIME,REG_NO,TOP_BRASS_YN,REQUEST_ADMT_THEATRE_FLAG,PT_CODE,PT_NAME,CTYP_CUST_TYPE,CUST_CUSTO MER,PRIORTY
FROM
A_INP_THEATRE_ADMT WHERE bus_unit = :1 and request_date >= (trunc(sysdate)-90) order by request_no desc
So remove the TRUNC() and maybe allow it touse an index if it exists.
Ross Leishman
|
|
|
|
| Re: need help with rewriting the query [message #456208 is a reply to message #456176] |
Sun, 16 May 2010 01:17 |
ateeqrahman786
Messages: 52
Registered: December 2009
Location: Hyderabad,India
|
Member |
|
|
The below script is for the view A_INP_THEATRE_ADMT.
----------------------------------------------------
CREATE OR REPLACE FORCE VIEW ULTGNP.A_INP_THEATRE_ADMT
(BUS_UNIT, REQUEST_NO, REG_NO, REQUEST_DATE, REQUEST_TIME,
PT_CODE, CTYP_CUST_TYPE, CUST_CUSTOMER, TOP_BRASS_YN, PT_NAME,
PRIORTY, REQUEST_ADMT_THEATRE_FLAG)
AS
SELECT B.BUS_UNIT,REQUEST_NO,REG_NO,REQUEST_DATE,REQUEST_TIME,
A.PT_CODE,CTYP_CUST_TYPE,CUST_CUSTOMER,TOP_BRASS_YN,
PT_FIRST_NAME_E||' '||PT_SECOND_NAME_E||' '||PT_LAST_NAME_E PT_NAME,
DECODE(REQUEST_PRIORTY_FLAG,'N','NORMAL','E','EMERGENCY','N') PRIORTY,
REQUEST_ADMT_THEATRE_FLAG
FROM A_PT_MASTER A, A_INP_THEATRE_REQUEST B
WHERE A.BUS_UNIT = B.BUS_UNIT AND
A.PT_CODE = B.PT_CODE AND
NVL(A.PT_CASH_PRIVATE,'C') NOT IN ('C','P','E') AND
B.TOP_BRASS_YN <> 'Y' AND
B. REQUEST_STATUS_FLAG = 'O' AND
NVL(B.ADMIT_STATUS_FLAG,'O') <> 'C'
UNION
SELECT B.BUS_UNIT,REQUEST_NO,REG_NO,REQUEST_DATE,REQUEST_TIME,
A.PT_CODE,CTYP_CUST_TYPE,CUST_CUSTOMER,TOP_BRASS_YN,
PT_FIRST_NAME_E||' '||PT_SECOND_NAME_E||' '||PT_LAST_NAME_E PT_NAME,
DECODE(REQUEST_PRIORTY_FLAG,'N','NORMAL','E','EMERGENCY','N') PRIORTY,
REQUEST_ADMT_THEATRE_FLAG
FROM A_PT_MASTER A, A_INP_ADMT_MSSG B
WHERE A.BUS_UNIT = B.BUS_UNIT AND
A.PT_CODE = B.PT_CODE AND
NVL(A.PT_CASH_PRIVATE,'C') NOT IN ('C','P','E') AND
B.TOP_BRASS_YN <> 'Y' AND
B.REQUEST_STATUS_FLAG = 'O' AND
NVL(B.ADMIT_STATUS_FLAG,'O') <> 'C';
CREATE PUBLIC SYNONYM A_INP_THEATRE_ADMT FOR ULTGNP.A_INP_THEATRE_ADMT;
CM: Added code tags, please do so yourself next time - see the orafaq forum guide if you're not sure how.
[Updated on: Sun, 16 May 2010 03:33] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu May 02 18:39:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
