| Which Explain Plan is better? [message #56614] |
Fri, 11 April 2003 14:48  |
Lisa M
Messages: 1
Registered: April 2003
|
Junior Member |
|
|
I'm a newby so please be gentle!
By rearranging the order of the two tables in the FROM and WHERE clauses, I get two different Explain Plans.
In Query2, I placed the smallest table (TableA) last in the FROM clause and first in the WHERE clause.
TableA will always have fewer rows than TableB. TableB will grow faster than TableA.
Both queries take the same amount of time to run right now, but I expect this to change as the data grows.
Which plan will provide better performance as the tables become larger?
Is there enough info here to determine that, or do I need to look at something else?
Is there a good online reference which would explain how to read these plans?
Thanks,
Lisa
=====
Query1:
SELECT A.field1, A.field2, A.field3, A.field4, B.field2, B.field3, B.field4
FROM TableA A, TableB B
WHERE B.field1 = A.field1;
Plan:
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | TABLE ACCESS BY INDEX ROWID| TableB | | |
| 2 | NESTED LOOPS | | | |
| 3 | TABLE ACCESS FULL | TableA | | |
|* 4 | INDEX RANGE SCAN | FK_TableB_1 | | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."Field1"="B"."Field1")
Note: rule based optimization
=====
Query2:
SELECT A.field1, A.field2, A.field3, A.field4, B.field2, B.field3, B.field4
FROM TableB B, TableA A
WHERE A.field1 = B.field1;
Plan:
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | |
| 1 | NESTED LOOPS | | | |
| 2 | TABLE ACCESS FULL | TableB | | |
| 3 | TABLE ACCESS BY INDEX ROWID| TableA | | |
|* 4 | INDEX UNIQUE SCAN | TableA_PK | | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("B"."Field1"="A"."Field1")
Note: rule based optimization
=====
|
|
|
|
| Re: Which Explain Plan is better? [message #56616 is a reply to message #56614] |
Fri, 11 April 2003 16:04  |
Anand
Messages: 161
Registered: August 1999
|
Senior Member |
|
|
Hi Lisa,
1. It's always better to have a 'driving' table preferably the table with less # of rows. So, TableA would be the ideal choice in your case. The driving table should appear first in your FROM clause.
2. Is 'field1' indexed in both the tables? Indexing would help speed up the query.
3. Why don't you consider using CBO ( Cost-based optimizer)? That will definitely help. You need to ANALYZE your tables periodically in order for Oracle optimizer to use CBO.
Tables can go to a FTS (Full table scan) but it depends on the size of the table. You can also consider using optimizer hints to speed up queries.
Try any of the following sites :
metalink.oracle.com
otn.oracle.com
Good luck !
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
