Home » RDBMS Server » Performance Tuning » I cant understand this behavior
I cant understand this behavior [message #301431] Wed, 20 February 2008 10:35 Go to next message
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 #301433 is a reply to message #301431] Wed, 20 February 2008 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>FROM table1 a, table2 b
why is table2 in the FROM clause when it returns no data to the SELECT clause?
>SELECT a.name

Which version of Oracle to 4 decimal places?
Re: I cant understand this behavior [message #301437 is a reply to message #301431] Wed, 20 February 2008 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
name2 is not in the PK so table access is mandatory and so... 2 different queries.

Also please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and align the columns in result.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Regards
Michel
Re: I cant understand this behavior [message #301632 is a reply to message #301433] Thu, 21 February 2008 03:37 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: wrong execution plan
Next Topic: Optimization for Large-Volume Data Streams
Goto Forum:
  


Current Time: Sat Dec 10 16:53:09 CST 2016

Total time taken to generate the page: 0.06939 seconds