i want join 1 table using sort-merge join algorithm (for test purposes). so, i'm using use_merge hint, when add hint in query, execution plan shows hash_join.
this query, hint.
select /*+ use_merge(remote locale)*/ distinct remote.nom ( select cnf.nom, dt.idb schema.supermarket@linkbd cnf inner join schema.detail@linkbd dt on dt.idc = cnf.idc inner join schema.commerce@linkbd bt1 on dt.idb = bt1.idb cnf.type = 'option1' ) remote, ( select substr(ag.cp, 0, 2) code, bt2.numb schema.h_commerce bt2 inner join schema.h_vente vn on vn.numb = bt2.numb inner join schema.h_agency ag on ag.codeag = vn.codeag ) locale, ( select pl.code schema.places@linkbd2 pl pl.zone = 'paris' ) placedpt remote.idb = locale.numb , placedpt.code = locale.code; the query works don't know why hint ignored. thanks!
the execution plan this:
---------------------------------------------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (cpu)| time | inst |in-out| ---------------------------------------------------------------------------------------------------------------------- | 0 | select statement | | 33 | 4653 | 23 (14)| 00:00:01 | | | | 1 | hash unique | | 33 | 4653 | 23 (14)| 00:00:01 | | | |* 2 | hash join | | 385 | 54285 | 22 (10)| 00:00:01 | | | | 3 | remote | supermarket | 33 | 1848 | 3 (0)| 00:00:01 | remote | r->s | | 4 | nested loops | | 1154 | 98090 | 19 (11)| 00:00:01 | | | |* 5 | hash join | | 1154 | 83088 | 18 (6)| 00:00:01 | | | | 6 | nested loops | | 23 | 1196 | 9 (12)| 00:00:01 | | | |* 7 | hash join | | 23 | 1127 | 9 (12)| 00:00:01 | | | | 8 | nested loops | | 5 | 195 | 5 (0)| 00:00:01 | | | | 9 | remote | places | 2 | 52 | 3 (0)| 00:00:01 | placedpt | r->s | | 10 | table access index rowid| h_agency | 3 | 39 | 1 (0)| 00:00:01 | | | |* 11 | index range scan | codeag | 3 | | 0 (0)| 00:00:01 | | | | 12 | table access full | h_vente | 100 | 1000 | 3 (0)| 00:00:01 | | | |* 13 | index unique scan | pk_boutique | 1 | 3 | 0 (0)| 00:00:01 | | | | 14 | remote | detail | 10098 | 197k| 9 (0)| 00:00:01 | remote | r->s | | 15 | remote | commerce | 1 | 13 | 0 (0)| 00:00:01 | remote | r->s | ---------------------------------------------------------------------------------------------------------------------- predicate information (identified operation id): --------------------------------------------------- " 2 - access(""dt"".""idc""=""cnf"".""idc"")" " 5 - access(""dt"".""idb""=""bt2"".""numb"")" " 7 - access(""ag"".""codeag""=""vn"".""codeag"")" " 11 - access(""zn"".""code""=substr(""cp"",0,2))" " 13 - access(""vn"".""numb""=""bt2"".""numb"")" remote sql information (identified operation id): ---------------------------------------------------- " 3 - select ""idc"",""nom"",""typec"" ""schema"".""supermarket"" ""cnf"" ""type""='option1' (accessing " 'linkbd' ) " 9 - select ""zone"",""code"" ""schema"".""places"" ""zn"" ""zone""='paris' (accessing " 'linkbd2' ) " 14 - select ""idb"",""idc"" ""schema"".""detail"" ""dt"" (accessing 'linkbd' )" " 15 - select ""idb"" ""schema"".""commerce"" ""bt1"" :1=""idb"" (accessing 'linkbd' )"
Comments
Post a Comment