| I cant understand this behavior [message #301431] |
Wed, 20 February 2008 10:35  |
iblazquez
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
Hi,
I have this Query
SELECT a.name1
FROM table1 a, table2 b
WHERE a.c1=t.b.c1 and b.c2=12043
Table1 has a pk by c1
There is a foreing key by c1 field
table2 has a index by c2 field
The explain plan is this
SELECT STATEMENT, GOAL = CHOOSE Cost=2 Cardinality=2266 Bytes=70246
NESTED LOOPS Cost=2 Cardinality=2266 Bytes=70246
TABLE ACCESS BY INDEX ROWID Object name=Table2 Cost=2 Cardinality=2266 Bytes=58916
INDEX RANGE SCAN Object name=Table2_IDX Cost=1 Cardinality=906
INDEX UNIQUE SCAN Object name=table1_PK Cardinality=1 Bytes=5
The result is Immediate
If I include a new field in the query result
SELECT a.name1, a.name2
FROM table1 a, table2 b
WHERE a.c1=t.b.c1 and b.c2=12043
The explain plan changes (I don't know why)
SELECT STATEMENT, GOAL = CHOOSE Cost=855 Cardinality=2266 Bytes=95172
MERGE JOIN Cost=855 Cardinality=2266 Bytes=95172
TABLE ACCESS BY INDEX ROWID Object owner=REGISTRO Object name=table1 Cost=826 Cardinality=571691 Bytes=9147056
INDEX FULL SCAN Object owner=REGISTRO Object name=table1_PK Cost=26 Cardinality=571691
SORT JOIN Cost=29 Cardinality=2266 Bytes=58916
TABLE ACCESS BY INDEX ROWID Object owner=LIGA2001 Object name=table2 Cost=2 Cardinality=2266 Bytes=58916
INDEX RANGE SCAN Object owner=LIGA2001 Object name=Table2_IDX Cost=1 Cardinality=906
The query needs 30 seconds
Can anybody explain it me?
|
|
|
|
|
|
|
|
| Re: I cant understand this behavior [message #301632 is a reply to message #301433] |
Thu, 21 February 2008 03:37  |
iblazquez
Messages: 26
Registered: June 2007
|
Junior Member |
|
|
First, sorry for post format.
To anacedent "why is table2 in the FROM clause when it returns no data to the SELECT clause?"
Because I need data from table2.
The question is why the plan is diferent if the query returns fields of the two tables.
The query needs a lot of time.
To Michel Cadot,
I dont understand what do you want to say
"name2 is not in the PK so table access is mandatory and so... 2 different queries"
|
|
|
|
| Re: I cant understand this behavior [message #301637 is a reply to message #301632] |
Thu, 21 February 2008 03:48 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
In your first query, you only display name1 which is part of the primary key, so Oracle does not need to access table1.
In your second query, you also display name2 that is not in the primary key, so Oracle has to acesss to table1 to get the values.
Regarding Anacedent's answer, you don't display any value from table2, so you should put it in a subquery. Something like: a.c1 in (select b.c1 from table2 where ...).
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
