| Insert Oddly Alters Explain Plan [message #355947] |
Tue, 28 October 2008 12:13  |
annagel
Messages: 220
Registered: April 2006
|
Senior Member |
|
|
I am running into an odd problem in which the optimizer seems seems to make some odd decisions about how best to retrieve a set of data when the selection is part of an insert vs simply being a select statement.
Setup for my example:
CREATE OR REPLACE FUNCTION foo (p_var IN NUMBER)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
RETURN p_var - 10;
END;
CREATE TABLE src (grp NOT NULL, val NOT NULL)
AS
SELECT TRUNC (DBMS_RANDOM.VALUE (1, 100)) grp,
TRUNC (DBMS_RANDOM.VALUE (1, 1000)) val
FROM DUAL
CONNECT BY ROWNUM <= 100000;
CREATE INDEX src_idx
ON src(foo(val), grp);
CREATE TABLE dest
(val NUMBER,
val_count NUMBER,
gro_count NUMBER);
Now this is only an example to illustrate my question so you have to take a leap and pretend that the function foo actually has a cost significant enough to merit use of the index rather than the table if that is possible.
Now from this src table I want to get all the foo return values, a count of the number of times each value occurs and a count of the distinct number of grp values associated with each. The optimaizer would not normally choose to use the index for such a request but we can point it in that direction with an explicit index hint like so:
SELECT --+INDEX(src src_idx)
foo (val),
COUNT (*),
COUNT (DISTINCT grp)
FROM src
WHERE foo (val) IS NOT NULL
GROUP BY foo (val);
The explain plans looks like I would expect it does a full scan of the index and then a sort group by for the counts
SELECT STATEMENT ALL_ROWS Cost: 99,626 Bytes: 6,979 Cardinality: 997
2 SORT GROUP BY Cost: 99,626 Bytes: 6,979 Cardinality: 997
1 INDEX FULL SCAN INDEX SRC_IDX Cost: 273 Cardinality: 97,691
Now if I change this statement from a simple select to an insert based on the select like so:
INSERT INTO dest
SELECT --+INDEX(src src_idx)
foo (val),
COUNT (*),
COUNT (DISTINCT grp)
FROM src
WHERE foo (val) IS NOT NULL
GROUP BY foo (val);
My explain plan changes to:
INSERT STATEMENT ALL_ROWS Cost: 99,626 Bytes: 6,979 Cardinality: 997
3 SORT GROUP BY Cost: 99,626 Bytes: 6,979 Cardinality: 997
2 TABLE ACCESS BY INDEX ROWID TABLE SRC Cost: 99,620 Bytes: 683,837 Cardinality: 97,691
1 INDEX FULL SCAN INDEX SRC_IDX Cost: 273 Cardinality: 97,691
It is the addition of the access by rowid that is confusing me. What is the point of this access? And why does Oracle only seem to want to do it when I am doing an insert?
|
|
|
|
| Re: Insert Oddly Alters Explain Plan [message #355949 is a reply to message #355947] |
Tue, 28 October 2008 12:33  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
I tried it in oracle apex website and for me both the plan looks the same as your first one. Oracle website apex version is running oracle 11.1.0.6.0.
Having said that never trust the plan you are getting from the explain plan. When optimiser kicks in it can choose a different plan. So try running both the queries but turning on the trace and do a tkprof on the trace file. Let us know with your observation.
Regards
Raj
[Edit:] I didn't notice the oracle version mentioned
[Updated on: Tue, 28 October 2008 12:35] Report message to a moderator |
|
|
|
| Re: Insert Oddly Alters Explain Plan [message #355956 is a reply to message #355949] |
Tue, 28 October 2008 13:32 |
annagel
Messages: 220
Registered: April 2006
|
Senior Member |
|
|
Checked it out on APEX...never knew Oracle offered this and yeah this is getting the results I would expect....waiting on some permissions to be able to get the trace.
Based only on execution time it would seem that some extra work is going on during the insert (obviously)...the question is whether a ROWID look-up is contributing to this or not.
|
|
|
|
| Re: Insert Oddly Alters Explain Plan [message #355974 is a reply to message #355956] |
Tue, 28 October 2008 15:45 |
annagel
Messages: 220
Registered: April 2006
|
Senior Member |
|
|
|
Explain Plan confirmed with Autotrace, must be an issue with 10.2 or at leas my version of 10.2 or some kind of set-up option though I have no idea of any configuration option which would lead to this behavior....
|
|
|
|
| Re: Insert Oddly Alters Explain Plan [message #356377 is a reply to message #355974] |
Thu, 30 October 2008 09:43 |
annagel
Messages: 220
Registered: April 2006
|
Senior Member |
|
|
I posted this same question to AskTom the other day when I noticed the submit question option was enabled. Got a response saying this looks like a bug since it does not happen in 11g and a workaround. Posting the workaround here in case any one else happens to run into a similar situation:
INSERT INTO dest
SELECT f,
COUNT (*),
COUNT (DISTINCT grp)
FROM (SELECT --+NO_MERGE INDEX(src src_idx)
foo (val) f,
grp
FROM src
WHERE foo (val) IS NOT NULL)
GROUP BY f;
Which produces the following explain plan:
INSERT STATEMENT ALL_ROWSCost: 30 Bytes: 16,983 Cardinality: 999
3 SORT GROUP BY Cost: 30 Bytes: 16,983 Cardinality: 999
2 VIEW NAGEL. Cost: 28 Bytes: 170,000 Cardinality: 10,000
1 INDEX FULL SCAN INDEX NAGEL.SRC_IDX Cost: 28 Bytes: 70,000 Cardinality: 10,000
The rowid lookup is gone.
[Updated on: Thu, 30 October 2008 09:45] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
