| How to tune this query [message #22136] |
Wed, 25 September 2002 07:57  |
Sameer
Messages: 60
Registered: March 1998
|
Member |
|
|
Dear experts this query is taking long time to execute can u give me an alternate sol. for this. Thnx in advance.
CREATE OR REPLACE VIEW GSA_STOCK_PEND_VIEW AS
SELECT "COMPANYCODE","BATCHNO","GSANO","ACQDATE","TICKETNOFROM","TICKETNOTO","NOOFTICKET","FORMTYPE","SRNO","ISSUED","TICKETNO" FROM GSA_STOCK_VIEW
WHERE TICKETNO NOT IN ( SELECT TICKETNO
FROM GSA_TICKET_INVENT_DETAIL_TAB )
both view and inner query's table has 85,000 records or more
|
|
|
|
| Re: How to tune this query [message #22137 is a reply to message #22136] |
Wed, 25 September 2002 08:41  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
try something like
FROM GSA_STOCK_VIEW V, GSA_TICKET_INVENT_DETAIL_TAB T
WHERE V.TICKETNO = T.TICKETNO (+)
AND T.TICKETNO IS NULL
|
|
|
|
| Re: How to tune this query [message #22139 is a reply to message #22136] |
Wed, 25 September 2002 09:34 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Check out the explain plan on Mike's query first, but here is another option to evaluate:
select companycode, batchno, gsano, acqdate, ticketnofrom, ticketnoto,
noofticket, formtype, srno, issued, ticketno
from gsa_stock_view sv
where not exists (select null
from gsa_ticket_invent_detail_tab tidt
where tidt.ticketno = sv.ticketno);
In either case, you definitely want an index on gsa_ticket_invent_detail_tab(ticketno).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
