oracle sql 自连接,分析函数优化自连接

用分析函数效率更好！

测试如下：

-- 用分析函数得到的consistent gets(4221)比用子查询得到的consistent gets(8442)， 少了一倍！

SQL> select count(*)

2    from t_obj t

3   where t.OBJECT_ID = (select min(t1.OBJECT_ID)

4                          from t_obj t1

5                         where t1.OBJECT_TYPE = t.OBJECT_TYPE);

Execution Plan

----------------------------------------------------------

Plan hash value: 876640030

---------------------------------------------------------------------------------

| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |         |     1 |    36 |  2358   (2)| 00:00:29 |

|   1 |  SORT AGGREGATE       |         |     1 |    36 |            |          |

|*  2 |   HASH JOIN           |         |     4 |   144 |  2358   (2)| 00:00:29 |

|   3 |    VIEW               | VW_SQ_1 |    40 |   960 |  1184   (2)| 00:00:15 |

|   4 |     HASH GROUP BY     |         |    40 |   480 |  1184   (2)| 00:00:15 |

|   5 |      TABLE ACCESS FULL| T_OBJ   |   292K|  3432K|  1172   (1)| 00:00:15 |

|   6 |    TABLE ACCESS FULL  | T_OBJ   |   292K|  3432K|  1172   (1)| 00:00:15 |

---------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("T"."OBJECT_ID"="MIN(T1.OBJECT_ID)" AND

"ITEM_1"="T"."OBJECT_TYPE")

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

8442  consistent gets

8428  physical reads

0  redo size

337  bytes sent via SQL*Net to client

337  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>

SQL>

SQL> select  count(*)

2  from

3  (select object_type,

4          object_id,

5          min(object_id) over(partition by object_type) min_id

6    from t_obj)

7  where object_id = min_id;

Execution Plan

----------------------------------------------------------

Plan hash value: 2567115077

--------------------------------------------------------------------------------------

| Id  | Operation            | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |       |     1 |    26 |       |  2514   (1)| 00:00:31 |

|   1 |  SORT AGGREGATE      |       |     1 |    26 |       |            |          |

|*  2 |   VIEW               |       |   292K|  7436K|       |  2514   (1)| 00:00:31 |

|   3 |    WINDOW SORT       |       |   292K|  3432K|  5752K|  2514   (1)| 00:00:31 |

|   4 |     TABLE ACCESS FULL| T_OBJ |   292K|  3432K|       |  1172   (1)| 00:00:15 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OBJECT_ID"="MIN_ID")

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

4221  consistent gets

4214  physical reads

0  redo size

337  bytes sent via SQL*Net to client

337  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

1  rows processed

SQL>