<pre name="code" class="sql">SQL> explain plan for SELECT DISTINCT d.sender_id,
2 d.misc_notes,
3 TO_CHAR(d.creation_date, :"SYS_B_0") creation_date,
4 d.message
5 FROM lead l,
6 lead_document d
7 WHERE l.email = :1
8 AND l.lead_id = d.lead_id
9 AND d.document_type =:"SYS_B_1"
10 AND d.creation_date > (sysdate - :"SYS_B_2")
11 ORDER BY d.misc_notes
12 ;
Explained.
SQL> select * from table(dbms_xplan.display)
2 ;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
Plan hash value: 1890182354
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 | 9 (23)| 00:00:01 |
| 1 | SORT UNIQUE | | 1 | 161 | 8 (13)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 161 | 7 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 161 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LEAD | 1 | 46 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | XIE1_LD | 1 | | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | XIE2_LD_DCMNT | 1 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | LEAD_DOCUMENT | 1 | 115 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("L"."EMAIL"=:1)
6 - access("L"."LEAD_ID"="D"."LEAD_ID" AND "D"."CREATION_DATE">SYSDATE@!-:SYS_B_2
AND "D"."CREATION_DATE" IS NOT NULL)
7 - filter("D"."DOCUMENT_TYPE"=:SYS_B_1)
22 rows selected.
关联后带distinct的 如果只从一个表返回记录,那么就可以尝试的改为in,如果从多个表返回记录那就无法改了。
当你看到SQL是inner join，并且用了distinct，并且只从一个表中取数据，可以将这类SQL改写为半连接，避免distinct排序，提升性能
SELECT DISTINCT d.sender_id,
d.misc_notes,
TO_CHAR(d.creation_date, :"SYS_B_0") creation_date,
d.message
FROM lead l, lead_document d
WHERE l.email = :1
AND l.lead_id = d.lead_id
AND d.document_type = :"SYS_B_1"
AND d.creation_date > (sysdate - :"SYS_B_2")
l.email = :1
ORDER BY d.misc_notes
select d.sender_id,
d.misc_notes,
TO_CHAR(d.creation_date, :"SYS_B_0") creation_date,
d.message
lead_document d
where d.document_type = :"SYS_B_1"
AND d.creation_date > (sysdate - :"SYS_B_2")
and d.lead_id in (select l.lead_id from lead l
where l.email = :1)
l.email = :1
ORDER BY d.misc_notes
SQL> explain plan for select d.sender_id,
2 d.misc_notes,
3 TO_CHAR(d.creation_date, :"SYS_B_0") creation_date,
4 d.message
5 from lead_document d
6 where d.document_type = :"SYS_B_1"
7 AND d.creation_date > (sysdate - :"SYS_B_2")
8 and d.lead_id in (select l.lead_id from lead l
9 where l.email = :1)
ORDER BY d.misc_notes
10 11 ;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------
Plan hash value: 232104937
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 161 | 8 (13)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 161 | 8 (13)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 161 | 7 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 161 | 7 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| LEAD | 1 | 46 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | XIE1_LD | 1 | | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | XIE2_LD_DCMNT | 1 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | LEAD_DOCUMENT | 1 | 115 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("L"."EMAIL"=:1)
6 - access("D"."LEAD_ID"="L"."LEAD_ID" AND "D"."CREATION_DATE">SYSDATE@!-:SYS_B_2
AND "D"."CREATION_DATE" IS NOT NULL)
7 - filter("D"."DOCUMENT_TYPE"=:SYS_B_1)
22 rows selected.