Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

11g sort merge join

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Test sort merge join

When the Optimizer Considers Sort Merge JoinsA hash join requires one hash table and one probe of this table, whereas a sort merge join requires two sorts.The optimizer may choose a sort merge join over a hash join for joining large amounts of data when any of the following conditions is true:The join condition between two tables is not an equijoin, that is, uses an inequality condition such as = .In contrast to sort merges, hash joins require an equality condition.Because of sorts required by other operations, the optimizer finds it cheaper to use a sort merge.If an index exists, then the database can avoid sorting the first data set. However, the database always sorts the second data set, regardless of indexes.SQL > create table T1 (id number); Table created.SQL > create table T2 (id number); Table created.SQL > insert into T1 values (2); 1 row created.SQL > insert into T1 values (1); 1 row created.SQL > insert into T1 values (5); 1 row created.SQL > insert into T1 values (3); 1 row created.SQL > commit;SQL > select * from T1 ID- 215 3SQL > insert into T2 values (11); 1 row created.SQL > insert into T2 values (9); 1 row created.SQL > insert into T2 values (1); 1 row created.SQL > commit;SQL > select * from T2 ID- 11 9 1SQL > SQL > exec dbms_stats.gather_table_stats ('SYS','T1'); PL/SQL procedure successfully completed.SQL > exec dbms_stats.gather_table_stats (' SYS','T2'); PL/SQL procedure successfully completed.SQL > set autot offSQL > create index ind_id on T1 (Id); Index created.SQL > set autot traceonlySQL > select * from T1 Id T2 where t1.id > t2.id Execution Plan---Plan hash value: 1335671014 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 2 | 12 | 4 (25) | 00:00:01 | | 1 | MERGE JOIN | | 2 | 12 | 4 (25) | 00:00:01 | | 2 | SORT JOIN | | 4 | 12 | 1 (0) | 00:00:01 | 3 | INDEX FULL SCAN | IND_ID | 4 | 12 | 1 (0) | 00:00:01 | | * 4 | SORT JOIN | | 3 | 9 | 3 (34) | 00:00:01 | 5 | TABLE ACCESS FULL | T2 | 3 | 9 | 2 (0) | 00:00:01 |- -Predicate Information (identified by operation id):-4-access (INTERNAL _ FUNCTION ("T1". "ID") > INTERNAL_FUNCTION ("T2". "ID") filter (INTERNAL_FUNCTION ("T1". "ID") > INTERNAL_FUNCTION ("T2". "ID") Statistics--- 1 recursive Calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 652 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 3 rows processed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 0

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report