| pora |
Posted: Apr 29, 2008 09:20:34 AM |
Total Post: 7
Joined: Mar, 2007
|
I am trying to run updaye on a daily basis but it explain plan shows very costly and it takes alsmot 7-9 minutes.
Could you please guide what should be wrong with table or index needed?
My target table has records 200350 and source table has around 1500 records.
My update is
update PRICE p
SET p.L_PRICE =
(SELECT S.L_PRICE
FROM LOAD S
WHERE '0000006666'||S.D_NAME||S.S_ID = p.p_id
AND S. L_PRICE != p.L_PRICE
AND S. L_PRICE != 0)
WHERE EXISTS(SELECT S.L_PRICE
FROM LOAD S
WHERE p.p_id = '0000006666'||S.D_NAME||S.S_ID
AND S.L_PRICE != p.L_PRICE
AND S.L_PRICE != 0)
[U]My indexes are[/U]
INDEX P_IDX2 ON PRICE
(P_LIST, S_ID)
INDEX P_IDX3 ON PRICE
(P_ID, L_PRICE)
[U]Explain plan[/U]
Execution Plan
----------------------------------------------------------
0 update STATEMENT Optimizer=CHOOSE (Cost=100416 Card=10017 By
tes=250425)
1 0 update OF 'PRICE'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'PRICE' (Cost=246 Card=1001
7 Bytes=250425)
4 2 TABLE ACCESS (FULL) OF 'LOAD'
(Cost=10 Card=13 Bytes=143)
5 1 TABLE ACCESS (FULL) OF 'LOAD' (C
ost=10 Card=13 Bytes=195)
Statistics
----------------------------------------------------------
0 recursive calls
613 db block gets
11040073 consistent gets
1593 physical reads
137700 redo size
366 bytes sent via SQL*Net to client
756 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
544 rows processed
Thanks,
Pora |
|
|
 Gitesh |
| Posted: Apr 29, 2008 10:34:01 PM |  |
|
Total Post: 322
Joined: May, 2005
|
Use /*+index (table_name index_name)*/ hint in inner query and check explain plan.
|
|
|
|
|
| pora |
| Posted: Apr 29, 2008 11:37:56 PM | |
|
Total Post: 7
Joined: Mar, 2007
|
Thanks, i will try and lwt you know.
|
|
|
|
|
| http://shaharear.blogspot.com |
| Posted: May 01, 2008 01:48:11 AM | |
|
Total Post: 79
Joined: Apr, 2008
|
hi
first try to stop the full table scan (use index). you have 2 sub quarry on load table but u have no index on then ( create single/composite index on it using those column which are in "WHERE" cluase)
rewrite the conditions..
S.L_PRICE != p.L_PRICE TO AND S.L_PRICE > p.L_PRICE AND AND S.L_PRICE < p.L_PRICE
AND S.L_PRICE != 0 TO S.L_PRICE > 0
if business requirement permit , try to build your existing & new index UNIQUE TYPE (like create unique index "name" on test(c1,c2);)
AND FINALLY PLEASE create single column index on foreign key columns on both table
hope the tips are work..
|
|
|
|
|
| http://shaharear.blogspot.com |
| Posted: May 01, 2008 07:17:11 AM | |
|
Total Post: 79
Joined: Apr, 2008
|
please go through the article it will help you to understand what i try to mean
http://sharear.blogspot.com/2008/05/eliminate-full-table-access.html
|
|
|
|
|
| pora |
| Posted: May 02, 2008 07:29:35 AM | |
|
Total Post: 7
Joined: Mar, 2007
|
Thanks much!
Load table i might be converting into Rxtrnal table so i can't create Index on External table, right?
Thanks,
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
