Document Type | Troubleshooting
Category | Migration
Applicable Product Versions | 6FS07, 7FS02PS
Document Number | TDETS002
Issue
This explains the phenomenon where the results of INDEX SCAN and TABLE FULL SCAN differ.
-- 1. TABLE FULL SCAN result (normal)
set linesize 150
set autot on exp stat planstat
SELECT /*+ FULL(A) */ DISTINCT LGV_CD
FROM TLGSBT003A A
WHERE MNG_YM BETWEEN '202303' AND '202412'
AND EPD_BGT_DAT_CL_CD = '2'
ORDER BY 1 ;
LGV_CD
------
1
2
3
4
5
6
7
7 rows selected.
SQL ID: dddtk8s1w4wkr
Child number: 18014398509486028
Plan hash value: 2188397784
Execution Plan
----------------------------------------------------------------------------
1 DISTINCT (SORT) (Cost:207, %%CPU:0, Rows:3)
2 PARTITION RANGE (SUBSET PART) (Cost:207, %%CPU:0, Rows:3) (PS:2, PE:3)
3 TABLE ACCESS (FULL): TLGSBT003A (Cost:207, %%CPU:0, Rows:3)
Predicate Information
---------------------------------------------------------------------------
3 - filter: ("A"."MNG_YM" >= '202303') AND ("A"."EPD_BGT_DAT_CL_CD" = '2')
AND ("A"."MNG_YM" <= '202412') (0.266 * 0.375 * 1.000)
Execution Stat
---------------------------------------------------------------------------
1 DISTINCT (SORT) (Time:.11 ms, Rows:7, Starts:1)
2 PARTITION RANGE (SUBSET PART) (Time:0. ms, Rows:12, Starts:1)
3 TABLE ACCESS (FULL): TLGSBT003A (Time:.19 ms, Rows:12, Starts:2)
-- 2. INDEX SCAN result (abnormal, same result for RANGE SCAN and SKIP SCAN)
set linesize 150
set autot on exp stat planstat
SELECT DISTINCT LGV_CD
FROM TLGSBT003A A
WHERE MNG_YM BETWEEN '202303' AND '202412'
AND EPD_BGT_DAT_CL_CD = '2'
ORDER BY 1 ;
LGV_CD
------
1
2
3
4
5
6
7
1
2
3
4
5
12 rows selected.
SQL ID: 28z3haqk8g6r3
Child number: 18014398509486029
Plan hash value: 2782779433
Execution Plan
---------------------------------------------------------------------------------------
1 PARTITION RANGE (SUBSET PART) (Cost:2, %%CPU:0, Rows:3) (PS:2, PE:3)
2 DISTINCT (Cost:2, %%CPU:0, Rows:3)
3 FILTER (Cost:2, %%CPU:0, Rows:3)
4 INDEX (RANGE SCAN): TLGSBT003A_PK (Cost:2, %%CPU:0, Rows:12)
Predicate Information
-------------------------------------------------------------------------------------
3 - filter: ("A"."MNG_YM" >= '202303') AND ("A"."MNG_YM" <= '202412') (0.266 * 1.000)
4 - access: ("A"."EPD_BGT_DAT_CL_CD" = '2') (0.375)
Execution Stat
-------------------------------------------------------------------------------------
1 PARTITION RANGE (SUBSET PART) (Time:.01 ms, Rows:12, Starts:1)
2 DISTINCT (Time:.01 ms, Rows:12, Starts:2)
3 FILTER (Time:.01 ms, Rows:12, Starts:2)
4 INDEX (RANGE SCAN): TLGSBT003A_PK (Time:.05 ms, Rows:12, Starts:2)
Cause
In a normal plan, before performing DISTINCT, the data should be sorted by the distinct key LGV_CD.
In fact, when the plan is executed as a table scan, you can confirm that DISTINCT is performed after sorting, represented as DISTINCT(SORT).
However, when the plan is executed as an index scan, it was either executed as DISTINCT(SORT) or did not add ORDER BY before DISTINCT, causing incorrect result values.
Solutions
Apply the patch to resolve the issue. (Applied patch: 293764b)
Caution
Apply the patch through technical support provided by Tmax Tibero.
Note
It is also possible to work around this issue by using the index_desc hint.
SELECT /*+ INDEX_DESC (A) */ DISTINCT LGV_CD FROM TLGSBT003A A WHERE MNG_YM BETWEEN '202303' AND '202412' AND EPD_BGT_DAT_CL_CD = '2' ORDER BY 1 ;