반응형
invalid-file
invalid-file
invalid-file
invalid-file..오라클 sql 튜닝...포켓 가이드
뭐 대략 간단한거 같네요..
간단한 예제...
DELETE FROM
FROM ps_pf_ledger_f00
WHERE EXISTS
(SELECT 'x'
FROM ps_pf_led_pst2_t1 b
WHERE b.business_unit = ps_pf_ledger_f00.business_unit
AND b.fiscal_year = ps_pf_ledger_f00.fiscal_year
AND b.accounting_period= ps_pf_ledger_f00.accounting_period
AND b.pf_scenario_id = ps_pf_ledger_f00.pf_scenario_id
AND b.source = ps_pf_ledger_f00.source
AND b.account = ps_pf_ledger_f00.account
AND b.deptid = ps_pf_ledger_f00.deptid
AND b.cust_id = ps_pf_ledger_f00.cust_id
AND b.product_id = ps_pf_ledger_f00.product_id
AND b.channel_id = ps_pf_ledger_f00.channel_id
AND b.obj_id = ps_pf_ledger_f00.obj_id
AND b.currency_cd = ps_pf_ledger_f00.currency_cd);
Elapsed: 00:08:160.51
이건 8초 걸린다...
DELETE FROM ps_pf_ledger_f00
WHERE( business_unit,fiscal_year,accounting_period,
pf_scenario_id ,account,deptid ,cust_id ,
product_id,channel_id,obj_id,currency_cd)
IN
(SELECT business_unit,fiscal_year,accounting_period,
pf_scenario_id ,account,deptid ,cust_id ,
product_id,channel_id,obj_id,currency_cd
FROM ps_pf_led_pst2_t1 );
Elapsed: 00:00:00.30
이건 0.8 초 ...
----------------------
SELECT COUNT(*)
FROM trans
WHERE cost_center = 'MASS'
AND bmark_id = 9;
Response Time = 4.255 seconds
The index that has the column that is listed first in the WHERE CLAUSE will drive the query. In this statement, the indexed entries for COST_CENTER = `MASS' will return significantly more rows than those for BMARK_ID=9, which will return at most only one or two rows.
The following query reverses the order of the conditions in the WHERE clause, resulting in a much faster execution time.
SELECT COUNT(*)
FROM trans
WHERE bmark_id = 9
AND cost_center = 'MASS';
Response Time = 1.044 seconds
