Home » RDBMS Server » Performance Tuning » how to use index on "ON in MERGE statement"? (10g, windows 2003 server.)
how to use index on "ON in MERGE statement"? [message #502393] Wed, 06 April 2011 03:33 Go to next message
kang
Messages: 89
Registered: November 2007
Member
mbr has 60,000 rows and member has 60,000 rows approx.

two tables have indexes on ssn, and citi_no on them.
PK of mbr : mbr_id
PK of member : mbr_id

other columns are not PK,
and have no index on it.

I'm wondering why the statment doesn't use index while ssn and citi_no have index.
Help me.

MERGE INTO mbr t
USING (SELECT mbr_id,citi_no
         FROM member) a
ON (t.ssn = a.citi_no)
WHEN MATCHED THEN
  UPDATE SET t.asis_mbr_id = a.mbr_id
  where t.ssn not in(select ssn from mbr group by ssn having count(*) > 1)
Re: how to use index on "ON in MERGE statement"? [message #502394 is a reply to message #502393] Wed, 06 April 2011 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
How many matches are there? Because if it's a lot a full table scan will be more effiecient than using an index anyway.
Re: how to use index on "ON in MERGE statement"? [message #502397 is a reply to message #502394] Wed, 06 April 2011 03:39 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
ssn and citi_no is allmost PK.
65376 out of 66939
Re: how to use index on "ON in MERGE statement"? [message #502398 is a reply to message #502397] Wed, 06 April 2011 03:42 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
So oracle is avoiding the index in this case as it would be less efficient.
Remember that for each match it has to access the table anyway to get the other relevant columns. If it needs to access most of the rows in each table then using the index would be counter-productive.
Re: how to use index on "ON in MERGE statement"? [message #502401 is a reply to message #502398] Wed, 06 April 2011 03:51 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
if indexes are used in the part, t.ssn = a.citi_no,
is this more efficient?
actually I don't understand your comment.
I 'd better study further.
Re: how to use index on "ON in MERGE statement"? [message #502402 is a reply to message #502397] Wed, 06 April 2011 03:54 Go to previous messageGo to next message
Roachcoach
Messages: 1221
Registered: May 2010
Location: UK
Senior Member
@CM: I read this part differently, I thought it was a cardinality reference:

kang wrote on Wed, 06 April 2011 09:39
ssn and citi_no is allmost PK.
65376 out of 66939



Edit:

@OP: Could probably do with an explain plan.

[Updated on: Wed, 06 April 2011 03:55]

Report message to a moderator

Re: how to use index on "ON in MERGE statement"? [message #502403 is a reply to message #502401] Wed, 06 April 2011 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Indexes are efficient for getting a small subset of data from a table.
You need to access most of the table as most of the records match.

If it used the index then for each of the 65 thousand matches it would have to access the two tables to get the columns used in the update part, as they are not on the indexes. Since that is most of the records in each table it would read about as much data as a full table scan anyway. Plus you're doing extra work by skipping back and forth between the index and table.
Consequently it is more efficient to skip the index altogether.

Re: how to use index on "ON in MERGE statement"? [message #502404 is a reply to message #502402] Wed, 06 April 2011 03:58 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Roachcoach wrote on Wed, 06 April 2011 09:54
@CM: I read this part differently, I thought it was a cardinality reference:

kang wrote on Wed, 06 April 2011 09:39
ssn and citi_no is allmost PK.
65376 out of 66939



Edit:

@OP: Could probably do with an explain plan.


I assumed number of matches.
Re: how to use index on "ON in MERGE statement"? [message #502406 is a reply to message #502404] Wed, 06 April 2011 04:10 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
cookiemonster, you're right.
Re: how to use index on "ON in MERGE statement"? [message #502409 is a reply to message #502406] Wed, 06 April 2011 04:13 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
i think, update mbr for each rows looping with procedure is more efficient because this way, one table is full scan and one table is index range scan.
Re: how to use index on "ON in MERGE statement"? [message #502410 is a reply to message #502406] Wed, 06 April 2011 04:15 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
So index would be unhelpful for the reasons stated above.

An analogy:
If you wanted to look up a small number of entries in an encyclopedia you'd use the index to find the page numbers of the entries you wanted.
If you wanted to look read up 90% of the entries in an encyclopedia you'd just start reading it from the begining and skip the entries you're not interested in. you wouldn't look each one up in the index.
Re: how to use index on "ON in MERGE statement"? [message #502411 is a reply to message #502409] Wed, 06 April 2011 04:16 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
kang wrote on Wed, 06 April 2011 10:13
i think, update mbr for each rows looping with procedure is more efficient because this way, one table is full scan and one table is index range scan.


I'm not sure what you mean.
Re: how to use index on "ON in MERGE statement"? [message #502412 is a reply to message #502409] Wed, 06 April 2011 04:16 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
how to force to use index ? set efficiency aside.
Re: how to use index on "ON in MERGE statement"? [message #502414 is a reply to message #502412] Wed, 06 April 2011 04:17 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use a hint.
Re: how to use index on "ON in MERGE statement"? [message #502415 is a reply to message #502412] Wed, 06 April 2011 04:20 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
loop for ssn in mbr
    update mbr t
       set mbr_id = (select mbr_id from rnd.member a where a.citi_no = t.ssn)
end loop

update statment in the loop use index range scan.
am i clear?
Re: how to use index on "ON in MERGE statement"? [message #502419 is a reply to message #502415] Wed, 06 April 2011 04:25 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
this gives table full scan too.
MERGE /*+ INDEX_ASC (ssn_on_mbr) */ INTO mbr t
USING (SELECT mbr_id,citi_no
         FROM rnd.member) a
ON (t.ssn = a.citi_no)
WHEN MATCHED THEN
  UPDATE SET t.asis_mbr_id = a.mbr_id
Re: how to use index on "ON in MERGE statement"? [message #502420 is a reply to message #502409] Wed, 06 April 2011 04:26 Go to previous messageGo to next message
Roachcoach
Messages: 1221
Registered: May 2010
Location: UK
Senior Member
kang wrote on Wed, 06 April 2011 10:13
i think, update mbr for each rows looping with procedure is more efficient because this way, one table is full scan and one table is index range scan.



If you mean write a pl/sql proc to do it row by row and think it'll be faster....it won't.

As a general rule, the optimizer is better than you, its only if you have information it does/cannot that hints are a good idea. Save the odd occasion it gets confused but in this instance, I believe it is correct.

Using indexes isn't a free operation - using them to return a high or rather, non-low percentage of rows often results in slower execution. Even a unique B-tree needs a minimum of 3 I/O operations to get a single row back (unless the query can be satisfied by the index alone but that's semantics).


Edit: An index hint needs to specify the table it is on

Edit2: Remember, full table scans are not always a bad thing.

[Updated on: Wed, 06 April 2011 04:30]

Report message to a moderator

Re: how to use index on "ON in MERGE statement"? [message #502421 is a reply to message #502420] Wed, 06 April 2011 04:32 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
like this?
MERGE /*+ INDEX_ASC (mbr ssn_on_mbr) */ INTO mbr t
USING (SELECT mbr_id,citi_no
         FROM rnd.member) a
ON (t.ssn = a.citi_no)
WHEN MATCHED THEN
  UPDATE SET t.asis_mbr_id = a.mbr_id


Yes thanks.

I think this takes over 30 min(now running), so I think there 's something wrong.
Re: how to use index on "ON in MERGE statement"? [message #502424 is a reply to message #502421] Wed, 06 April 2011 04:42 Go to previous messageGo to next message
cookiemonster
Messages: 11076
Registered: September 2008
Location: Rainy Manchester
Senior Member
Of course there's something wrong. You made it use the index when that's the wrong thing to do.
Re: how to use index on "ON in MERGE statement"? [message #502432 is a reply to message #502424] Wed, 06 April 2011 04:59 Go to previous message
Roachcoach
Messages: 1221
Registered: May 2010
Location: UK
Senior Member
This might help:

(Forced) Inappropriate index usage

SCOTT@ORA11GMK > select /*+ index(s TIDX)*/ * from t s;

72405 rows selected.

Elapsed: 00:00:00.90

Execution Plan
----------------------------------------------------------
Plan hash value: 4253753185

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 83352 |    16M|  1248   (1)| 00:00:15 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    | 83352 |    16M|  1248   (1)| 00:00:15 |
|   2 |   INDEX FULL SCAN           | TIDX | 83352 |       |   162   (1)| 00:00:02 |
------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
      10882  consistent gets
          0  physical reads
          0  redo size
    8032578  bytes sent via SQL*Net to client
      53501  bytes received via SQL*Net from client
       4828  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72405  rows processed

Note the consistent gets figure above and then look at this:

select * from t;


72405 rows selected.

Elapsed: 00:00:00.78

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 83352 |    16M|   290   (1)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| T    | 83352 |    16M|   290   (1)| 00:00:04 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       5858  consistent gets
          0  physical reads
          0  redo size
    8032578  bytes sent via SQL*Net to client
      53501  bytes received via SQL*Net from client
       4828  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      72405  rows processed


Note the consistent gets in the second block. It's doing half the logical I/O.

The "bad" full table scan is doing a lot less work.


An 'extreme' example, but illustrates the point I feel.
Previous Topic: Just clarification my mind about redo log file size
Next Topic: Performance tuning of query with self join
Goto Forum:
  


Current Time: Fri Oct 24 14:10:39 CDT 2014

Total time taken to generate the page: 0.10539 seconds