| Re Update index [message #167760] |
Sun, 16 April 2006 22:05  |
mghong
Messages: 27
Registered: February 2006
Location: KL
|
Junior Member |
|
|
UPDATE cf72ab a
set status = 'C'
WHERE EXISTS (
SELECT brncd
FROM cf70sbac b
WHERE a.brncd = SUBSTR (b.acnum, 1, 5)
AND a.acno = SUBSTR (b.acnum, 6, 2)
There is an index on this acnum and index for cf72ab
with brncd,acno.
CREATE INDEX CF70SBAC_ACNUM ON CF70SBAC
(ACNUM)
LOGGING
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 8K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;
And here is the explain plan
Execution Plan
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=CHOOSE
1 0 UPDATE OF 'CF72SAC'
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'CF72ab'
4 2 TABLE ACCESS (FULL) OF 'CF70SBAC'
Any way to improve this kind of query ? w/o changing the program logic ?
|
|
|
|
|
|
| Re: Re Update index [message #167917 is a reply to message #167880] |
Mon, 17 April 2006 21:58  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
What version of Oracle?
For v8i+, try:UPDATE cf72ab a
set status = 'C'
WHERE EXISTS (
SELECT /*+ UNNEST*/ brncd
FROM cf70sbac b
WHERE a.brncd = SUBSTR (b.acnum, 1, 5)
AND a.acno = SUBSTR (b.acnum, 6, 2)
This should permit a HASH-SEMI-JOIN. Check you plan to see if it works.
Ross Leishman
|
|
|
|
|
|
| Re: Re Update index [message #168121 is a reply to message #167926] |
Tue, 18 April 2006 22:16 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Try this:
UPDATE cf72ab a
set status = 'C'
WHERE (SUBSTR (b.acnum, 1, 5), SUBSTR (b.acnum, 6, 2)) IN (
SELECT /*+ UNNEST*/ brncd, acno
FROM cf70sbac
)
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
