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