| Hash join hint not being used [message #191239] |
Tue, 05 September 2006 05:32  |
speaker
Messages: 30
Registered: April 2006
|
Member |
|
|
Hi pls find my SQL and the explain plan in 9i db
SELECT *
FROM (SELECT DISTINCT /*+ USE_HASH( MI INST ) */
NULL AS ctrack_instance_col_id,
NULL AS ctrack_title_narration,
NULL AS ctrack_performer_list,
NULL AS ctrack_is_default_lid,
mi.lid AS ctrack_title_language,
NULL AS ctrack_prt_narration,
NULL AS ctrack_is_published,
NULL AS ctrack_created_date,
NULL AS ctrack_instance_id,
NULL AS ctrack_genres_list,
NULL AS ctrack_short_title,
NULL AS ctrack_owner_name, NULL AS ctrack_price_desc,
NULL AS ctrack_filestatus, NULL AS ctrack_dtmf_title,
NULL AS ctrack_is_sample, NULL AS ctrack_origin_id,
NULL AS ctrack_position, NULL AS ctrack_prt_list,
NULL AS ctrack_is_avail, NULL AS ctrack_filesize,
NULL AS ctrack_filename, NULL AS ctrack_owner_id,
NULL AS ctrack_encoding, NULL AS ctrack_urlpath,
NULL AS ctrack_uncpath, NULL AS ctrack_length,
NULL AS ctrack_ext_id, NULL AS ctrack_price,
NULL AS ctrack_begin, mi.title AS ctrack_title,
NULL AS ctrack_ml_id, NULL AS ctrack_desc,
NULL AS ctrack_type, NULL AS ctrack_rank,
NULL AS ctrack_end, mi.ID AS ctrack_id,
NULL AS fe_id
FROM MI_FULL_PLAIN MI,INST,PDL, PGROUP
WHERE mi.ID = inst.mi_id
AND inst.ID = pdl.inst_id
AND pdl.pgroup_id = pgroup.ID
AND pdl.is_ready = 'Y'
AND pdl.feglist_id = 7111
AND inst.enctype IN (61)
AND pdl.is_published = 'Y'
AND UPPER (mi.title) LIKE UPPER ('%Narration%')
AND mi.TYPE <> '9'
ORDER BY UPPER (mi.title))
WHERE ROWNUM <= 10
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=15 Card=1 Bytes=489)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=15 Card=1 Bytes=489)
3 2 SORT (ORDER BY STOPKEY) (Cost=15 Card=1 Bytes=57)
4 3 SORT (UNIQUE) (Cost=10 Card=1 Bytes=57)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'MI_FULL_PLAIN' (
Cost=2 Card=1 Bytes=25)
6 5 NESTED LOOPS (Cost=5 Card=1 Bytes=57)
7 6 NESTED LOOPS (Cost=3 Card=1 Bytes=32)
8 7 NESTED LOOPS (Cost=2 Card=1 Bytes=20)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'PDL' (Co
st=2 Card=1 Bytes=15)
10 9 INDEX (RANGE SCAN) OF 'IX_PDL_FEGLIST_ID
' (NON-UNIQUE) (Cost=1 Card=1)
11 8 INDEX (UNIQUE SCAN) OF 'PK_PGROUP_ID' (UNI
QUE)
12 7 TABLE ACCESS (BY INDEX ROWID) OF 'INST' (Cos
t=1 Card=1 Bytes=12)
13 12 INDEX (UNIQUE SCAN) OF 'PK_INST_ID' (UNIQU
E)
14 6 INDEX (RANGE SCAN) OF 'IX_MI_FP_ID' (NON-UNIQU
E) (Cost=1 Card=2)
The init.ora parameters are as follows
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string CHOOSE
hash_area_size integer 10485760
hash_join_enabled boolean TRUE
Why the hint is being ignored.
The same query in 8i uses hash join and with good performance
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=FIRST_ROWS 13 17
COUNT STOPKEY
VIEW 13 6 K 17
SORT ORDER BY STOPKEY 13 871 17
SORT UNIQUE 13 871 12
NESTED LOOPS 13 871 6
HASH JOIN 13 819 6
HASH JOIN 13 624 4
TABLE ACCESS FULL ACRI.INST 69 828 2
TABLE ACCESS FULL ACRI.MI_FULL_PLAIN 12 432 1
TABLE ACCESS FULL ACRI.PDL 122 1 K 1
INDEX UNIQUE SCAN AMI.PK_PGROUP_ID 2 8
Pls help
|
|
|
|
|
|
|
|
| Re: Hash join hint not being used [message #191275 is a reply to message #191239] |
Tue, 05 September 2006 07:56  |
kimant
Messages: 201
Registered: August 2006
Location: Denmark
|
Senior Member |
|
|
Hi
Have You tried using the FULL hint on all three tables?
Maybe both needs to be applied?
And off course - have You gathered statistics on the tables?
Br
Kim
|
|
|
|
| Re: Hash join hint not being used [message #191373 is a reply to message #191275] |
Wed, 06 September 2006 01:18 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Ah yes, clearly I am going mad. Indulge my madness for a few moments longer then...
You have placed the hint in the wrong place - move it between the SELECT and the DISTINCT.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
