Home » RDBMS Server » Performance Tuning » Re Update index
Re Update index [message #167760] Sun, 16 April 2006 22:05 Go to next message
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 #167880 is a reply to message #167760] Mon, 17 April 2006 12:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Any way to improve this kind of query ? w/o changing the program logic ?
No, when any function is used on any indexed column the function prevents/precludes the index from being used.
Re: Re Update index [message #167917 is a reply to message #167880] Mon, 17 April 2006 21:58 Go to previous messageGo to next message
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 #167926 is a reply to message #167917] Mon, 17 April 2006 23:08 Go to previous messageGo to next message
mghong
Messages: 27
Registered: February 2006
Location: KL
Junior Member
We are running 9i on MVS.
I try that /*+ UNNEST */ but the explain plan still the same

i trying to build a function index on it with

create index t on cf70sbac (substr(acnum,1,5), substr(acnum,6,2)

but the result still the same..Sad Mad
Re: Re Update index [message #168121 is a reply to message #167926] Tue, 18 April 2006 22:16 Go to previous message
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
Previous Topic: High Disk Reads and FTS
Next Topic: Multiple index on Multiple tables
Goto Forum:
  


Current Time: Mon May 27 03:42:32 CDT 2024