After executing a join over two tables, whith 4 million rows each and indexes in join columns, I realized that 4 minutes it's too much time waiting for results. So I decided to watch for the execution plan :
select columns from tableA join TableB on tableA.id = tableB.id
Execution Plan---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=RULE 
1 0 FILTER 
2 1 SORT (GROUP BY) 
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLEA' 
4 3 NESTED LOOPS 
5 4 TABLE ACCESS (FULL) OF 'TABLEB' 
6 4 INDEX (RANGE SCAN) OF 'TABLEA_U1' (UNIQUE)
All seems to be correct, after all I'm using Rule Base Optimizer. In order to reduce execution time I force the execution of an hash join instead of a Nested Loops.
Execution time droped to about 2 minutes and execution plan looks like this :
Execution Plan---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=RULE (Cost=624 Card=40000 Bytes=2 800000)
1 0 FILTER 
2 1 SORT (GROUP BY) (Cost=624 Card=40000 Bytes=2800000) 
3 2 HASH JOIN (Cost=172 Card=40000 Bytes=2800000) 
4 3 TABLE ACCESS (FULL) OF 'TABLEA' (Cost=35 Card=2000 Bytes=62000)
5 3 TABLE ACCESS (FULL) OF 'TABLEB' (Cost=136 Card=2000 Bytes=78000)
And all I've to do is change my query to :
select /*+ USE_HASH (tableA tableB ) */ columns from tableA join TableB on tableA.id = tableB.id
Why is this happening? why RBO this choose this plan? The answer is simple, RBO doesn't consider hash joins has a valid execution path, so you have to force it.
This specific join is used when a large amount of data needs to be joined or when a large fraction of the the table needs to be joined, however this is a very memory expensive operation and need to be carefully analised.
Wednesday, January 7, 2009
Subscribe to:
Post Comments (Atom)
Archive
- 
        ▼ 
      
2009
(21)
- 
        ▼ 
      
January
(21)
- How to deploy files in a remote server?
 - Oracle : Reduce your parsing times
 - Oracle : SQLCODE and SQLERRM in Inserts
 - SQL * Plus Tip - 1
 - Oracle : Reducing join execution time
 - PL/SQL Tip - 1
 - PL/SQL : Show locked objects
 - PL/SQL : How to update using select
 - EIM : Shell execution order
 - PL/SQL : Scripting for table drop
 - EIM : DUP_RECORD_EXISTS after Update
 - Localcooling . com
 - How to relate a contact whit their accounts
 - PL/SQL : Check script for object owners
 - SQL Navigator : Code Templates
 - S_POSTN_CON : Description
 - Joins : Please talk ANSI SQL with me.
 - WHy MINUS does NOT EXISTs in our queries?
 - Get last file version
 - Debugging in http://localhost:8082
 - Error 452: Match column X not found in Y
 
 
 - 
        ▼ 
      
January
(21)
 
Translation by Google
Subscription Free
Categories
- ANSI SQL
 - Back to Basics
 - Best Practices
 - Case Study
 - cfg File
 - Check In -Check Out
 - Configuration
 - Copy
 - Debug
 - Ecology
 - EIM
 - eScript
 - Files
 - Firewall
 - Harvest
 - How - To
 - I Wish I Knew
 - List of Values
 - Logging
 - Mapping
 - Oracle
 - PL/SQL
 - Remote Server
 - Siebel
 - Siebel CRM
 - Siebel Logs
 - Siebel Tools
 - Signals
 - SQL Plus
 - SQLNavigator
 - Tunning
 - Web Services
 - Workflows
 





No comments:
Post a Comment