- Are the table stats up to date?
- Has anyone changed system settings such as OPTIMIZER_INDEX_COST_ADJ?
- Are there SQL profiles or plan baselines for the query?


In addition to the above, few more to check,
-How about the Clustering factor of the index? (is that close to the num_rows or blocks? - aka Organization of data in disk)
-How about the data distribution in this column "ID_FILIAL", do we have some massively skewed data values in it?

Hello, Chris.

Could you please elaborate why 53,373 rows is a lot for a 6 million rows table? I'm failing to see how <1% of total data volume can be considered a lot in this scenario.

That's an interesting explanation, but I still find hard to see such situation in real life.

Somebody would have to put much effort to have about 53k rows evenly split on 53k differents blocks, like almost a manual job. Although not really impossible, to combine this with the limit per read one would have to manually change it to about 1meg as stated on the doc for the parameter DB_FILE_MULTIBLOCK_READ_COUNT :

"Even though the default value may be a large value, the optimizer will not favor large plans if you do not set this parameter. It would do so only if you explicitly set this parameter to a large value"

I'm not questioning the explanation, in fact I'm just delving into the subject because I'm experiencing a similar situation as the original question: a 18 million rows table that refuses to use an optimal index when returning more than 500K rows.

When hinting the index (for a more than 600k rows predicate) I see the cost higher than a FTS. But it's curious to see it using the index as expected if the query returns less than or about 500k rows.

I just tried copying the table to another schema (with a CTAS) and adding the index; a fresh table to avoid the "one row per block" situation, updated statistics and all. Still getting the same problem, when running the query for more than 600K rows the index is skipped in favor of a FTS.

What else could explain the bad performance for what is, in theory, an optimal index for my query?

Somebody would have to put much effort to have about 53k rows evenly split on 53k differents blocks,


think about it, an index on the employee_name column, the values would come randomly over the period of time ( and employee_name is not something monotonically increasing like a sequence values )

the first row to the emp table could have employee_name as ADAM.
the second row could have employee_name as CHRIS
so these two values would be sitting next to each other on the same leaf block.
upon third row insert could have employee_name as BOB, then this value should lie between ADAM and CHRIS in the leaf block, as over the period of time more values come in and lies between the existing values, could lead to leaf block split and the values could easily spread across the different leaf blocks.

that could be the case here.


I'm experiencing a similar situation as the original question: a 18 million rows table that refuses to use an optimal index when returning more than 500K rows.


Perhaps that could be due to Clustering Factor of the index.

demo@ORA12C> create table t1
  2  parallel 4
  3  as
  4  select *
  5  from big_table
  6  order by id ;

Table created.

demo@ORA12C>
demo@ORA12C> create table t2
  2  parallel 4
  3  as
  4  select *
  5  from big_table
  6  order by dbms_random.random;

Table created.

demo@ORA12C> alter table t1 noparallel;

Table altered.

demo@ORA12C> alter table t2 noparallel;

Table altered.

demo@ORA12C>
demo@ORA12C> create index t1_idx on t1(id) parallel 4 nologging;

Index created.

demo@ORA12C> create index t2_idx on t2(id) parallel 4 nologging;

Index created.

demo@ORA12C> alter index t1_idx noparallel;

Index altered.

demo@ORA12C> alter index t2_idx noparallel;

Index altered.

demo@ORA12C> select i.index_name,i.clustering_factor,t.blocks,t.num_rows,i.degree
  2  from user_indexes i,
  3       user_tables t
  4  where i.table_name = t.table_name
  5  and t.table_name in ('T1','T2');

INDEX_NAME CLUSTERING_FACTOR     BLOCKS   NUM_ROWS DEGREE
---------- ----------------- ---------- ---------- -----------------------------------
T1_IDX                187987     188656   10000000 1
T2_IDX               9999962     188640   10000000 1

demo@ORA12C>

Same set of data exists in both T1 and T2 but their data organization is different.

T1_IDX has the clustering factor close to the number of blocks
T2_IDX has the clustering factor close to the number of rows.

When retrieving 1% of rows from both the table, T1 uses index where as T2 uses Full table scan.

demo@ORA12C> set serveroutput off
demo@ORA12C> set feedback only
demo@ORA12C> select /*+ gather_plan_statistics */ * from t1 where id <=100000;

100000 rows selected.

demo@ORA12C> set feedback on
demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID  4yatghqu001ph, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t1 where id <=100000

Plan hash value: 1775246573

--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |      1 |        |    100K|00:00:01.49 |    3546 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1     |      1 |    100K|    100K|00:00:01.49 |    3546 |
|*  2 |   INDEX RANGE SCAN                  | T1_IDX |      1 |    100K|    100K|00:00:00.49 |     888 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<=100000)


19 rows selected.

demo@ORA12C> set feedback only
demo@ORA12C> select /*+ gather_plan_statistics */ * from t2 where id <=100000;

100000 rows selected.

demo@ORA12C> set feedback on
demo@ORA12C> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
SQL_ID  0n41wz4nwc6p1, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from t2 where id <=100000

Plan hash value: 1513984157

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |    100K|00:00:02.46 |     188K|    187K|
|*  1 |  TABLE ACCESS FULL| T2   |      1 |    100K|    100K|00:00:02.46 |     188K|    187K|
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"<=100000)


18 rows selected.

demo@ORA12C> 

Yeah, it's awful to accept defeat but you guys were right, we have a bad case of high clustering factor.

Although I got my answer and found the root of the problem thinking about this situation made me ask if its an evolution of the old days of fragmentation: rebuilding a table when it grows too big. I also found this entry here on AskTOM which states the same technique to solve it:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9524251800346726054#9524263800346897661

So, rebuilding the table in a better order is the only approach? And it isn't a very useful one since this may help some specifics indexes while impacting negatively others.