Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> index hint on update
jmurphy
post Aug 30 2017, 01:17 PM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 30-August 17
Member No.: 52,138



Hi I HAVE A TABLE WITH 900,000 ROWS EACH HAS A UNIQUE MAC ADDRESS (TABLE1) I HAVE A SECOND TABLE WITH UNIQUE MAC ADDESS,KEYNAME AND KEYVALUE (TABLE2)
I HAVE AN INDEX ON MAC ADDRESS ON BOTH TABLES AND I HAVE GATHERED STATS ON BOTH
I WANT TO UPDATE TABLE1 USING THE KEYVALUE FROM TABLE2 JOIN ON MAC ADDRESS. FOR SOME REASON THE QUERY WILL NOT USE THE INDEX EVEN THOUGH I USE THE HINT.
COULD SOMEONE EXPLAIN WHY.
update /*+ index (br brrccm_mac) */ bacc_records_mr_cm br set class_of_service =
(select
bra.keyvalue
from bacc_records_key bra
where bra.macaddress = br.mac_address
and UPPER(bra.keyname) = 'DOWNSTREAMUPSTREAMQOS');


Go to the top of the page
 
+Quote Post
burleson
post Aug 31 2017, 07:05 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,529
Joined: 26-January 04
Member No.: 13



Hi Mr. Murphy, and welcome to the forum!

It looks you need to include the table alias in the hint!

See example here:

http://www.dba-oracle.com/t_oracle_index_hint_syntax.htm

Add be to the hint: (br idx_name)

Hints are never ignored! Please read:

http://www.dba-oracle.com/t_hint_ignored.htm

Have you tried placing the hint in the sub-select?

Please read my notes on tuning updates:

http://www.dba-oracle.com/t_efficient_upda...ql_dml_tips.htm


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
jmurphy
post Aug 31 2017, 07:51 AM
Post #3


Newbie
*

Group: Members
Posts: 3
Joined: 30-August 17
Member No.: 52,138



QUOTE (burleson @ Aug 31 2017, 08:05 AM) *
Hi Mr. Murphy, and welcome to the forum!

It looks you need to include the table alias in the hint!

See example here:

http://www.dba-oracle.com/t_oracle_index_hint_syntax.htm

Add be to the hint: (br idx_name)

Hints are never ignored! Please read:

http://www.dba-oracle.com/t_hint_ignored.htm

Have you tried placing the hint in the sub-select?

Please read my notes on tuning updates:

http://www.dba-oracle.com/t_efficient_upda...ql_dml_tips.htm

I think I do have the table alias included /*+ index (br brrccm_mac) */ ?

Go to the top of the page
 
+Quote Post
jmurphy
post Aug 31 2017, 07:57 AM
Post #4


Newbie
*

Group: Members
Posts: 3
Joined: 30-August 17
Member No.: 52,138



QUOTE (jmurphy @ Aug 31 2017, 08:51 AM) *
I think I do have the table alias included /*+ index (br brrccm_mac) */ ? Also I did place the hint in the sub query but that did not see to work either attached update_query doc

Go to the top of the page
 
+Quote Post
burleson
post Aug 31 2017, 11:27 AM
Post #5


Advanced Member
***

Group: Members
Posts: 13,529
Joined: 26-January 04
Member No.: 13



Hi Murphy,

First, what is your compliant?

What was the number of rows updated as a percentage of the 900k rows?

As we know from my article, valid hints are never ignored!

You subquery creates an in _list of values to update, and the only choice the optimizer has is either to use the PK index, or perform a full-table scan against the target table.

Please post the execution plan, that will give us clues!

Also, what percentage of the total rows in the table (dba_tables.num_rows) are being changed?

What release of Oracle are you running?

We can figure this out, please respond with details.

Do you have dynamic sampling enabled?


--------------------
Hope this helps . . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd September 2017 - 05:28 PM