select para_id
from dwf.f_Savc_Buscode b
where b.Para_Id = (SELECT MIN(Para_Id)
FROM Dwf.f_Savc_Buscode
WHERE Bus_Code = b.Bus_Code);
SQL> set linesize 200
SQL> set pagesize 200
SQL> explain plan for select para_id
from dwf.f_Savc_Buscode b
where b.Para_Id = (SELECT MIN(Para_Id)
FROM Dwf.f_Savc_Buscode
WHERE Bus_Code = b.Bus_Code); 2 3 4 5
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1952012934
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 86 | 2924 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 86 | 2924 | 7 (15)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 86 | 2236 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 86 | 688 | 4 (25)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| SYS_C0036092 | 435 | 3480 | 3 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN | SYS_C0036092 | 435 | 3480 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."PARA_ID"="MIN(PARA_ID)" AND "ITEM_1"="B"."BUS_CODE")
17 rows selected.
改写为with as 后:
with A as (select para_id,min(Para_Id) over(partition by Bus_Code) min_para_id
from Dwf.f_Savc_Buscode)
select para_id from A where A.para_id=a.min_para_id
SQL> explain plan for with A as (select para_id,min(Para_Id) over(partition by Bus_Code) min_para_id
from Dwf.f_Savc_Buscode)
select para_id from A where A.para_id=a.min_para_id 2 3 ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1562643515
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 435 | 11310 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 435 | 11310 | 4 (25)| 00:00:01 |
| 2 | WINDOW SORT | | 435 | 3480 | 4 (25)| 00:00:01 |
| 3 | INDEX FAST FULL SCAN| SYS_C0036092 | 435 | 3480 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"."PARA_ID"="A"."MIN_PARA_ID")
15 rows selected.
SQL> select * from (with A as (select para_id,min(Para_Id) over(partition by Bus_Code) min_para_id
from Dwf.f_Savc_Buscode)
select para_id from A where A.para_id=a.min_para_id)
minus
select para_id
from dwf.f_Savc_Buscode b
where b.Para_Id = (SELECT MIN(Para_Id)
FROM Dwf.f_Savc_Buscode
WHERE Bus_Code = b.Bus_Code) 2 3 4 5 6 7 8 9 ;
no rows selected