Document Type | Troubleshooting
Category | App Development
Applicable Product Version | 7FS02
Document Number | TDETS003
Issue
In Tibero, when querying a specific table using SELECT, even though the column in the WHERE clause is indexed, the query is executed as a FULL SCAN.
DROP INDEX TIBERO.TB_STOR_IX01;
CREATE INDEX "TIBERO"."TB_STOR_IX01" ON TIBERO."TB_STOR" (
"RECORD_CENTER_ID" ASC,
"ORIGN_ID" ASC
)
/
EXEC DBMS_STATS.GATHER_TABLE_STATS('TIBERO', 'TB_STOR', estimate_percent=>3, cascade=>TRUE, degree=>8);
SELECT * FROM TIBERO.TB_STOR
WHERE RECORD_CENTER_ID = '0000001'
AND ORIGN_ID = 'OR20210506145231470'
AND VIRUS_FLAG = '1'
;
Execution Plan
--------------------------------------------------------------------------------
1 TABLE ACCESS (FULL): TB_STOR (Cost:1185460, %%CPU:0, Rows:1)
Predicate Information
--------------------------------------------------------------------------------
1 - filter: (""TB_STOR"".""VIRUS_FLAG"" = '1') AND (""TB_STOR"".""RECO
RD_CENTER_ID"" = '0000001') AND (""TB_STOR"".""ORIGN_ID"" = 'OR20210506145231470') (0.000 * 1.000 * 1.000)0') (0.000 * 1.000 * 1.000)
NAME VALUE
------------------------------ ----------
db block gets 6349
consistent gets 2782280
physical reads 2785431
redo size 0
sorts (disk) 0
sorts (memory) 3
rows processed 0
Cause
From Tibero version 7, the default value of _opt_adjust_table_pred_selectivity was changed to 0.
From Tibero 7, a specific patch (245376) has been applied, and depending on whether this patch is applied, the optimizer behaves differently.
Solutions
Set _opt_adjust_table_pred_selectivity = 100 to enable the query to use Index Scan when an index exists for the condition clause.