执行计划使用UK cost不正确的原因隐式转化

jnrjian

已于 2024-04-29 00:21:26 修改

阅读量716

点赞数 20

文章标签： oracle

于 2024-04-29 00:20:49 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/138295859

版权

隐式转化 RAW类型

正常用了UK cost为1

SELECT /*+ index(qb , TABA_uk1) */
QB.NO + 1
FROM XX.TABA QB
WHERE QB.A = 'H'
AND QB.B = :B5
AND QB.C = TO_DATE(:B4, 'YYYY-MM-DD')
AND QB.D = :B3
AND QB.E = NVL(:B2, QB.E)
AND QB.F = :B1
ORDER BY QB.NO DESC
FETCH FIRST 1 ROWS ONLY

Plan Hash Value : 3596379467

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 6578 | 00:00:01 |
| * 1 | VIEW | | 1 | 39 | 6578 | 00:00:01 |
| * 2 | WINDOW SORT PUSHED RANK | | 1 | 73 | 6578 | 00:00:01 |
| * 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | TABA | 1 | 73 | 6577 | 00:00:01 |
| * 4 | INDEX FULL SCAN | TABA_UK1 | 1 | | 6576 | 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=1)
* 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("QB"."NO") DESC )<=1)
* 3 - filter("QB"."A"='H')
* 4 - access("QB"."D"=:B3 AND "QB"."C"=TO_DATE(:B4,'YYYY-MM-DD'))
* 4 - filter(RAWTOHEX("QB"."B")=:B5 AND "QB"."E"=NVL(:B2,"QB"."E") AND "QB"."C"=TO_DATE(:B4,'YYYY-MM-DD') AND "QB"."D"=:B3 AND
RAWTOHEX("QB"."F")=:B1)





SELECT /*+ index(qb , TABA_fk4) */
QB.NO + 1
FROM XX.TABA QB
WHERE QB.A = 'H'
AND QB.B = :B5
AND QB.C = TO_DATE(:B4, 'YYYY-MM-DD')
AND QB.D = :B3
AND QB.E = NVL(:B2, QB.E)
AND QB.F = :B1
ORDER BY QB.NO DESC
FETCH FIRST 1 ROWS ONLY


Plan Hash Value : 1152405338

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 13312 | 00:00:01 |
| * 1 | VIEW | | 1 | 39 | 13312 | 00:00:01 |
| * 2 | WINDOW SORT PUSHED RANK | | 1 | 73 | 13312 | 00:00:01 |
| 3 | PARTITION LIST SINGLE | | 1 | 73 | 13311 | 00:00:01 |
| * 4 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | TABA | 1 | 73 | 13311 | 00:00:01 |
| * 5 | INDEX RANGE SCAN | TABA_FK4 | 489026 | | 1216 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------

SELECT /*+ index(qb , TABA_fk4) */
QB.NO + 1
FROM XX.TABA QB
WHERE QB.A = 'H'
AND QB.B = hextoraw(:B5)
AND QB.C = TO_DATE(:B4, 'YYYY-MM-DD')
AND QB.D = :B3
AND QB.E = NVL(:B2, QB.E)
AND QB.F = hextoraw(:B1)
ORDER BY QB.NO DESC
FETCH FIRST 1 ROWS ONLY

Plan Hash Value : 1152405338

SELECT /*+ index(qb , TABA_uk1) */
QB.NO + 1
FROM XX.TABA QB
WHERE QB.A = 'H'
AND QB.B = hextoraw(:B5)
AND QB.C = TO_DATE(:B4, 'YYYY-MM-DD')
AND QB.D = :B3
AND QB.E = NVL(:B2, QB.E)
AND QB.F = hextoraw(:B1)
ORDER BY QB.NO DESC
FETCH FIRST 1 ROWS ONLY

Plan Hash Value : 1252820133

------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 39 | 5 | 00:00:01 |
| * 1 | VIEW | | 1 | 39 | 5 | 00:00:01 |
| * 2 | WINDOW SORT PUSHED RANK | | 1 | 73 | 5 | 00:00:01 |
| * 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | TABA | 1 | 73 | 4 | 00:00:01 |
| * 4 | INDEX RANGE SCAN | TABA_UK1 | 1 | | 3 | 00:00:01 |
------------------------------------------------------------------------------------------------------------