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

The use of DISTINCT has always been known to DBA, which is used to filter weight. There is no need for us to question the accuracy of DISTINCT weight filter.

But today, I suddenly don't know what to think, so I just want to question it. Does DISTINCT really guarantee that filtered objects do not have duplicate records?

SQL > create table test_distinct as select rownum id from all_objects where rownum

< 50000;insert into test_distinct select rownum id from all_objects where rownum < 50000; 这表中有重复的数据，数据插入顺序1~4999，然后再重复一次 SQL>

Select count (id) from (select distinct id from test_distinct) group by id having count (id) > 1 terno rows selected

As you can see, through this statement, it is found that there are really no duplicate lines. So think further about how to achieve this effect without distinct. I think of ROWID, and the following two sentences have the same effect.

Select count (distinct phoneno) from CUSTPHONEselect count (phoneno) from CUSTPHONE where rowid in (select min (rowid) from CUSTPHONE group by phoneno)

I want to know how DISTINCT statements actually operate in Oracle, getting trace information through 10046 events and tkprof tools.

SQL ID: 8vtyapcbqkbwfPlan Hash: 2372476266select distinct idfrom test_distinct where rownum

< 100call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 1 0.02 0.02 0 138 0 0Execute 1 0.00 0.00 0 0 0 0Fetch 8 0.00 0.00 0 4 0 99------- ------ -------- ---------- ---------- ---------- ---------- ----------total 10 0.02 0.02 0 142 0 99Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: SYSRows Row Source Operation------- --------------------------------------------------- 99 HASH UNIQUE (cr=4 pr=0 pw=0 time=0 us cost=528 size=1287 card=99) 99 COUNT STOPKEY (cr=4 pr=0 pw=0 time=196 us) 99 TABLE ACCESS FULL TEST_DISTINCT (cr=4 pr=0 pw=0 time=98 us cost=44 size=1318174 card=101398)Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ SQL*Net message to client 8 0.00 0.00 asynch descriptor resize 2 0.00 0.00 SQL*Net message from client 8 59.43 59.43 可以看到执行计划中DISINCT是通过HASH UNIQUE算法来实现的。同时ROWNUM虚列使用的是COUNT算法，STOPKEY说明我给ROWNUM虚列加了限定条件100，当到达这个限定条件时，该语句查询结束。 那么到这我该怎么理解HASH UNIQUE算法的目的呢？我在网上查看了相关信息，发现真有人做了实验实验帮助我们加上对该算法的印象。在10G2R以前，Oracle对DISTINCT使用的是sort unique这种操作方式因为涉及到排序，是非常影响语句的执行效率的。因此10G2R之后的版本，Oracle改进了算法。 SQL>

Select distinct id from test_distinct where rownum

< 100;99 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 2372476266---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 99 | 1287 | | 528 (1)| 00:00:07 || 1 | HASH UNIQUE | | 99 | 1287 | 2000K| 528 (1)| 00:00:07 ||* 2 | COUNT STOPKEY | | | | | | || 3 | TABLE ACCESS FULL| TEST_DISTINCT | 101K| 1287K| | 44 (3)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - filter(ROWNUM alter system flush buffer_cacheSQL>

Select id from test_distinct where rownum < 100 group by id 99 rows selected.Execution Plan---Plan hash value: 521476922 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 99 | 1287 | 47 (9) | 00:00:01 | | 1 | HASH GROUP BY | | 99 | 1287 | 47 (9) | 00:00:01 | | * 2 | COUNT STOPKEY | | | 3 | TABLE ACCESS FULL | TEST_DISTINCT | 101k | 1287k | 44 (3) | 00:00:01 |- -Predicate Information (identified by operation id):-2-filter (ROWNUM select / * + rule*/ distinct id from test_distinct where rownum < 100) 99 rows selected.Execution Plan---Plan hash value: 3449293992-| Id | Operation | | Name |-| 0 | SELECT STATEMENT | | 1 | SORT UNIQUE | | * 2 | COUNT STOPKEY | 3 | TABLE ACCESS FULL | TEST_DISTINCT |-- | -- Predicate Information (identified by operation id):-2-filter (ROWNUM select / * + rule*/ id from test_distinct where rownum < 100group by id 99 rows selected.Execution Plan---Plan hash value: 351786816-| Id | Operation | | Name |-| 0 | SELECT STATEMENT | | 1 | SORT GROUP BY | | * 2 | COUNT STOPKEY | 3 | TABLE ACCESS FULL | TEST_DISTINCT |-- | -Predicate Information (identified by operation id):-2-filter (ROWNUM

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.