| how to use index on "ON in MERGE statement"? [message #502393] |
Wed, 06 April 2011 03:33  |
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 #502398 is a reply to message #502397] |
Wed, 06 April 2011 03:42  |
cookiemonster
Messages: 13958
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 #502403 is a reply to message #502401] |
Wed, 06 April 2011 03:57 |
cookiemonster
Messages: 13958
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 |
cookiemonster
Messages: 13958
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:39ssn 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 #502410 is a reply to message #502406] |
Wed, 06 April 2011 04:15 |
cookiemonster
Messages: 13958
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 #502420 is a reply to message #502409] |
Wed, 06 April 2011 04:26 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
kang wrote on Wed, 06 April 2011 10:13i 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 |
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 #502432 is a reply to message #502424] |
Wed, 06 April 2011 04:59 |
Roachcoach
Messages: 1576
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
