Home » SQL & PL/SQL » SQL & PL/SQL » Optimize the query
Optimize the query [message #243901] Sun, 10 June 2007 23:21 Go to next message
san_bajpai75
Messages: 4
Registered: June 2007
Junior Member
Hi,

I am using the below query in reports 6i.
its taking too much of time.
can any one help me.
-- for journal entries that are not against a location and are PPVs
-- QUERY 1
SELECT toj.code_combo_display gl_code,
DECODE( toj.fiscal_per, 1,'JUN',2,'JUL',3,'AUG',4,'SEP',5,'OCT',6,'NOV',7,'DEC',8,'JAN',9,'FEB',10,'MAR',11,'APR',12,'MAY',toj.fiscal_per )||'-'||TO_CHAR(toj.fiscal_yr) GL_period,
TO_CHAR(toj.export_id) export_id,
toj.second_object_id document,
SUBSTR(RTRIM(tc.user_code,' '),1,7) user_type,
TO_CHAR(toj.eff_date,'DD-MON-YYYY') trans_date,
NVL(toj.cost_applied,0) toj_cost_applied,
toj.journal_category_id toj_journal_category_id,
LTRIM(toj.stk_no,'0') ppv_toj_stk_no,
toj.journal_code_id toj_journal_code_id,
toj.batch_no toj_batch_no,
toj.invoice_line_no toj_invoice_line_no
FROM tsw_codes tc,
tsw_out_journal toj,
tsw_out_journal_batch tojb
WHERE tc.code_id = toj.journal_category_id
AND tc.fk_code1 = toj.journal_code_id
AND toj.batch_no = tojb.batch_no
AND toj.journal_code_id = tojb.journal_code_id
AND tc.code_id = 10087
AND toj.journal_code_id = 10013
AND NVL( toj.batch_no,0 ) > 0
AND toj.code_combo_display BETWEEN :p_beg_gl_code AND :p_end_gl_code
AND toj.eff_date BETWEEN TO_DATE( :p_beg_period_date,'DD-MON-YYYY' )
AND TO_DATE( :p_end_period_date||' 23:59', 'DD-MON-YYYY HH24:MI' )
--
-- for journal entries not against a location and are NOT PPVs
-- QUERY 2
-- I have removed this part of query as it will never retrieve any rows, but may take some time doing this
-- There will never be tsw_code rows with code_id = 10087 AND user_code NOT LIKE 'PPV_ST%'
--
UNION
-- Journal entries against a location that are PPV related
-- Query 3
SELECT toj.code_combo_display gl_code,
DECODE(toj.fiscal_per, 1,'JUN',2,'JUL',3,'AUG',4,'SEP',5,'OCT',6,'NOV',7,'DEC',8,'JAN',9,'FEB',10,'MAR',11,'APR',12,'MAY',toj.fiscal_per)||'-'||TO_CHAR(toj. fiscal_yr) GL_period,
TO_CHAR(toj.export_id) export_id,
toj.second_object_id document,
SUBSTR(RTRIM(tc.user_code,' '),1,7) user_type,
TO_CHAR(toj.eff_date,'DD-MON-YYYY') trans_date,
NVL(toj.cost_applied,0) toj_cost_applied,
toj.journal_category_id toj_journal_category_id,
LTRIM(toj.stk_no,'0') ppv_toj_stk_no,
toj.journal_code_id toj_journal_code_id,
toj.batch_no toj_batch_no,
toj.invoice_line_no toj_invoice_line_no
FROM tsw_codes tc,
tsw_out_journal toj ,
tsw_out_journal_batch tojb,
eim_stock_adjustment_items esai1
WHERE tc.code_id = toj.journal_category_id
AND tc.fk_code1 = toj.journal_code_id
AND toj.batch_no = tojb.batch_no
AND toj.journal_code_id = tojb.journal_code_id
AND toj.journal_category_id = esai1.object_type_id
AND toj.second_object_id = esai1.object_id
AND tc.code_id = 10087 -- PPV_ST only
AND toj.journal_code_id = 10013
AND NVL(toj.batch_no,0) > 0
AND toj.eff_date BETWEEN to_date(:p_beg_period_date,'DD-MON-YYYY')
AND to_date(:p_end_period_date||' 23:59', 'DD-MON-YYYY HH24:MI')
AND toj.code_combo_display BETWEEN :p_beg_gl_code AND :p_end_gl_code
AND esai1.loc_code BETWEEN NVL(:p_beg_loc, esai1.loc_code) AND NVL(:p_end_loc, esai1.loc_code)
UNION
-- Journal entries against a location that are not PPV related
-- Query 4
SELECT toj.code_combo_display gl_code,
DECODE(toj.fiscal_per, 1,'JUN',2,'JUL',3,'AUG',4,'SEP',5,'OCT',6,'NOV',7,'DEC',8,'JAN',9,'FEB',10,'MAR',11,'APR',12,'MAY',toj.fiscal_per)||'-'||TO_CHAR(toj. fiscal_yr) gl_period,
TO_CHAR(toj.export_id) export_id,
toj.object_id document,
SUBSTR(RTRIM(tc.user_code,' '),1,7) user_type,
TO_CHAR(toj.eff_date,'DD-MON-YYYY') trans_date,
NVL(toj.cost_applied,0) toj_cost_applied,
toj.journal_category_id toj_journal_category_id,
LTRIM(toj.stk_no,'0') ppv_toj_stk_no,
toj.journal_code_id toj_journal_code_id,
toj.batch_no toj_batch_no,
toj.invoice_line_no toj_invoice_line_no
FROM tsw_codes tc,
tsw_out_journal toj,
tsw_out_journal_batch tojb,
eim_stock_adjustment_items esai1
WHERE tc.code_id = toj.journal_category_id
AND tc.fk_code1 = toj.journal_code_id
AND toj.batch_no = tojb.batch_no
AND toj.journal_code_id = tojb.journal_code_id
AND toj.journal_category_id = esai1.object_type_id
AND toj.object_id = esai1.object_id
AND tc.code_type = 'OBJECT_TYPE_ID'
AND tc.fk_code2 = 1009
AND SUBSTR(RTRIM(tc.user_code,' '),1,7) != 'PPV_ST'
AND toj.journal_code_id = 10013
AND NVL( toj.batch_no,0 ) > 0
AND toj.eff_date BETWEEN TO_DATE(:p_beg_period_date,'DD-MON-YYYY')
AND TO_DATE(:p_end_period_date||' 23:59', 'DD-MON-YYYY HH24:MI')
AND toj.code_combo_display BETWEEN :p_beg_gl_code AND :p_end_gl_code
AND esai1.loc_code BETWEEN NVL( :p_beg_loc, esai1.loc_code )
AND NVL( :p_end_loc, esai1.loc_code )
ORDER BY 1,6,3
regards
san
Re: Optimize the query [message #243903 is a reply to message #243901] Sun, 10 June 2007 23:48 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You should read and FOLLOW the posting guidelines as enumerated in the STICKY posts at the top of this forum.
If you do not know how to improve the performance of the posted query, why do you expect anyone else to know more than you about this SQL statement.
Only YOU have all the necessary details required to solve this mystery.
Re: Optimize the query [message #243908 is a reply to message #243903] Mon, 11 June 2007 00:00 Go to previous messageGo to next message
san_bajpai75
Messages: 4
Registered: June 2007
Junior Member
Hi,

I have no idea about how to optimize that query its taking too much of time even i have created index on that.
regards
san
Re: Optimize the query [message #243919 is a reply to message #243901] Mon, 11 June 2007 01:02 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You should read and FOLLOW the posting guidelines as enumerated in the STICKY posts at the top of this forum.
Previous Topic: Materialised Views
Next Topic: NOT urgent. ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$"
Goto Forum:
  


Current Time: Fri Dec 09 04:07:20 CST 2016

Total time taken to generate the page: 0.16065 seconds