global index 还是partition index

最新推荐文章于 2024-05-11 11:00:05 发布

jnrjian

最新推荐文章于 2024-05-11 11:00:05 发布

阅读量504

点赞数 22

文章标签：数据库

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

版权

select max(COLA) from a WHERE COLB =

index 建local 还是partition，建cola 还是 colbb,cola还是 colba,colb

SELECT
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER'


TAB TABA_IDX5 Normal COLA glocal
TAB TABA_IDX6 Normal COLA, 'NULL' N N N Y N local
TAB TABA_IDX7 Normal COLA, COLB glocal


SELECT /*+ index(a , TABA_IDX6) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER'

[3:36:10 AM] 1 row selected in 0.019 seconds

Plan Hash Value : 4211172150

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 339934443 | 03:41:19 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
| 2 | PARTITION LIST SINGLE | | 1224937726 | 17149128164 | 4628123 | 00:03:01 |
| 3 | FIRST ROW | | 1224937726 | 17149128164 | 4628123 | 00:03:01 |
| 4 | INDEX FULL SCAN (MIN/MAX) | TABA_IDX6 | 1224937726 | 17149128164 | 4628123 | 00:03:01 |
----------------------------------------------------------------------------------------------------------------

SELECT /*+ index(a , TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER'

[3:35:54 AM] 1 row selected in 0.020 seconds

Plan Hash Value : 3695121415

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 4 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
| 2 | FIRST ROW | | 1 | 14 | 4 | 00:00:01 |
| * 3 | INDEX FULL SCAN (MIN/MAX) | TABA_IDX7 | 1 | 14 | 4 | 00:00:01 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("COLB"='LITER')

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
-------------------------------------------------------------------------------

3 - SEL$1 / A@SEL$1
U - index(a , TABA_IDX5)

SELECT /*+ full(a) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER'

--3XX second
Plan Hash Value : 3037139145

SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a


[3:45:02 AM] 1 row selected in 0.023 seconds



Plan Hash Value : 2375961132

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 85 | 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | PARTITION LIST ALL | | 1 | 11 | 85 | 00:00:01 |
| 3 | INDEX FULL SCAN (MIN/MAX) | TABA_IDX6 | 1 | 11 | 85 | 00:00:01 |
----------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
-------------------------------------------------------------------------------

3 - SEL$1 / A@SEL$1
U - index (a,TABA_IDX5)
3 - SEL$1 / A@SEL$1
U - index (a,TABA_IDX7)


----why
TAB TABA_IDX5 Normal COLA glocal
TAB TABA_IDX6 Normal COLA, 'NULL' N N N Y N local
TAB TABA_IDX7 Normal COLA, COLB glocal

drop index TAB.TABA_IDX6

SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a


--[3:45:39 AM] 1 row selected in 0.023 seconds

Plan Hash Value : 1550242172

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 14298156 | 00:09:19 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | INDEX FULL SCAN (MIN/MAX) | TABA_IDX7 | 1959812328 | 21557935608 | | |
-------------------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
-------------------------------------------------------------------------------

2 - SEL$1 / A@SEL$1
U - index (a,TABA_IDX5)

drop index TAB.TABA_IDX7

Plan Hash Value : 964782392 cost big time <1s

-------------- XXX second

TAB TABA_IDX5 Normal COLA global

SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER'





---- TAB TABA_IDX5 Normal COLA local


SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER' and COLA is not null


SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLA is not null

----slow BUG???

SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER'


Plan Hash Value : 3037139145

----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 10251075 | 00:06:41 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
| 2 | PARTITION LIST SINGLE | | 1224937726 | 17149128164 | 10251075 | 00:06:41 |
| 3 | TABLE ACCESS FULL | TABA | 1224937726 | 17149128164 | 10251075 | 00:06:41 |
----------------------------------------------------------------------------------------------------
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
-------------------------------------------------------------------------------

3 - SEL$1 / A@SEL$1
U - index (a,TABA_IDX5)





----works

SELECT
to_date(MAX(null), 'YYYY-MM-DD') + 2 COLA
FROM
dual




----fast

SELECT /*+ index (a,TABA_IDX5) */
MAX(COLA)
FROM
TAB.TABA a
WHERE
COLB = 'LITER'

---[4:24:12 AM] 1 row selected in 0.026 seconds

Plan Hash Value : 1481902224

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 339069406 | 03:40:45 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
| 2 | PARTITION LIST SINGLE | | 1224937726 | 17149128164 | 3763076 | 00:02:27 |
| 3 | FIRST ROW | | 1224937726 | 17149128164 | 3763076 | 00:02:27 |
| 4 | INDEX FULL SCAN (MIN/MAX) | TABA_IDX5 | 1224937726 | 17149128164 | 3763076 | 00:02:27 |
----------------------------------------------------------------------------------------------------------------

SELECT /*+ index (a,TABA_IDX5) */
MAX(COLA)
FROM
TAB.TABA a
--[4:23:51 AM] 1 row selected in 0.088 seconds
Plan Hash Value : 1098234909

---slow null column

SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(null), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a

---slow null column

SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER' and COLA is null

Plan Hash Value : 3037139145

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 10250319 | 00:06:41 |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
| 2 | PARTITION LIST SINGLE | | 1 | 14 | 10250319 | 00:06:41 |
| * 3 | TABLE ACCESS FULL | TABA | 1 | 14 | 10250319 | 00:06:41 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 3 - filter("COLA" IS NULL)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
-------------------------------------------------------------------------------

3 - SEL$1 / A@SEL$1
U - index (a,TABA_IDX5)

---fast
SELECT /*+ index (a,TABA_IDX5) */
to_date(MAX(COLA), 'YYYY-MM-DD') + 2 COLA
FROM
TAB.TABA a
WHERE
COLB = 'LITER' and COLA is not null