| Merge join verse Filter. Same code, different database, different explain plan [message #292149] |
Tue, 08 January 2008 01:02  |
bickers
Messages: 1
Registered: October 2006
Location: Australia
|
Junior Member |
|
|
I have a view that performs differently on two separate databases and accordingly produces a different explain plan. The code is exactly the same on each database.
Database A is a test database with a relatively small amount of data
Oracle9i Release 9.2.0.7.0 - 64bit Production
JServer Release 9.2.0.7.0 - Production
Database B is a production database with a large amount of data
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 – Production
Without going into too much detail the view in question (I will call VIEW_A) is along the lines of:
SELECT various data
FROM various tables
WHERE various conditions
AND EXISTS (SELECT view_b.column_name
FROM view_b
WHERE view_b.column_name_a = table_name.column_name_a
AND view_b.column_name_b = table_name.column_name_b)
GROUP BY various_data
View_b is itself based on another view
When selecting a particular row from view_a the explain plan on the production database (B) shows a MERGE JOIN/SORT JOIN:
SORT (GROUP BY)
MERGE JOIN
SORT (JOIN)
And goes on to show a full table scan from a large table
The explain plan from the test database (A) shows
SORT GROUP BY
FILTER
and selects from the large table via an index
I have looked on the internet for sources that might tell me why the two databases are behaving differently (e.g. parameter settings) but have not been able to resolve this. Could someone please at least point me in the right direction as to what I should be looking for.
Thanks
|
|
|
|
|
|
| Re: Merge join verse Filter. Same code, different database, different explain plan [message #292391 is a reply to message #292386] |
Tue, 08 January 2008 20:23  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Search for Cost Based Optimiser. Oracle evaluates each query not only based on the SQL syntax, the table/index structures, the database parameter settings, but also the DATA.
If the data in the two databases is different - or if Oracle THINKS it is different because statistics have not been recently calculated, then it can come up with a different plan.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
