I was experiencing FTS on SELECT for 1 record.
SQL> select A.vk_accn_test_seq_id, count(*) FROM accn_phys A group by A.vk_accn_test_seq_id; VK_ACCN_TEST_SEQ_ID COUNT(*) ------------------- ---------- 2268482 1 3765572 1 1038777 1 2053978 1 3639618 1 3940603 1 628110 1 +++ 2639137 1 0 469892 9 rows selected. SQL> SELECT pk_seq_id FROM accn_phys A WHERE A.vk_accn_test_seq_id=628110; PK_SEQ_ID ---------- 95100 Elapsed: 00:00:00.05 Execution Plan ---------------------------------------------------------- Plan hash value: 3167157579 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 51408 | 401K| 704 (0)| 07:27:00 | |* 1 | TABLE ACCESS FULL| ACCN_PHYS | 51408 | 401K| 704 (0)| 07:27:00 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"."VK_ACCN_TEST_SEQ_ID"=628110) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4662 consistent gets 0 physical reads 0 redo size 528 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select column_name, num_distinct, num_nulls, num_buckets, density, histogram from user_tab_col_statistics where table_name = 'ACCN_PHYS' and column_name='VK_ACCN_TEST_SEQ_ID' order by column_name; 2 3 4 5 6 7 8 9 COLUMN_NAME NUM_DISTINCT NUM_NULLS NUM_BUCKETS DENSITY HISTOGRAM ------------------------------ ------------ ---------- ----------- ---------- --------------- VK_ACCN_TEST_SEQ_ID 9 0 1 .111111111 NONE SQL> select index_name from user_ind_columns where table_name='ACCN_PHYS' and column_name='VK_ACCN_TEST_SEQ_ID'; INDEX_NAME ------------------------------ I_ACCN_PHYS_ACCNTESTSEQID SQL> analyze index I_ACCN_PHYS_ACCNTESTSEQID validate structure; Index analyzed. SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows, del_lf_rows from index_stats; HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS ---------- ---------- ---------- ---------- ---------- ---------- ----------- 3 2176 2100 471150 8 2099 1248 SQL>
NOTICE: the large number of DEL_LF_ROWS
On QA system: REBUILD Index
SQL> alter index I_ACCN_PHYS_ACCNTESTSEQID rebuild; Index altered. SQL> analyze index I_ACCN_PHYS_ACCNTESTSEQID validate structure; Index analyzed. SQL> select height, blocks, lf_blks, lf_rows, br_blks, br_rows, del_lf_rows from index_stats; HEIGHT BLOCKS LF_BLKS LF_ROWS BR_BLKS BR_ROWS DEL_LF_ROWS ---------- ---------- ---------- ---------- ---------- ---------- ----------- 3 832 805 444305 3 804 0 SQL> SELECT pk_seq_id FROM accn_phys A WHERE A.vk_accn_test_seq_id=628110; PK_SEQ_ID ---------- 95100 Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 234933029 --------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 63472 | 495K| 735 (1)| 00:00:09 | | 1 | TABLE ACCESS BY INDEX ROWID| ACCN_PHYS | 63472 | 495K| 735 (1)| 00:00:09 | |* 2 | INDEX RANGE SCAN | I_ACCN_PHYS_ACCNTESTSEQID | 63472 | | 120 (2)| 00:00:02 | --------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("A"."VK_ACCN_TEST_SEQ_ID"=628110) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 2 physical reads 0 redo size 528 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL>