Why the filesystem scanner may perform very slow when scanning folder with long names?

Problem description:

When an object is scanned, the filesystem scanner checks in the MC database if the object was already scanned by a previous scan. This check is done based on the file path (id_on_source_column). This column is indexed so checking the file existence shouldn't take more than a couple of milliseconds. 

But this is not always simple with Oracle optimizer. If you have for example 100.000 files located under a root path whose length is greater than 32 characters (or 64 bytes in 12c and later) Oracle optimizer may decide NOT to use the index but to use full table scan.

This is mainly because the selectivity for a query is calculated based on the index histogram, but in the histogram Oracle keeps only the first 32/64 characters of each value. In this case the optimizer thinks there are more than 100.000 rows in the table that meet the path criteria and decide to perform a full table scan. 

More technical details can be found in this nice article: http://hemantoracledba.blogspot.ro/2009/08/histograms-on-larger-columns.html

Solution:

For case sensitive search 
(MC version < 3.17 and between 25.2 and 26.1 with the caseSensitive parameter checked)

Run the following commands in SQLPLUS connected as an user that have execute privileges on "SYS.dbms_stats" package. User SYSTEM or SYS should work but FMEMC may not always work. This will force Oracle to always use the index when checking if an object was already scanned.

BEGIN
dbms_stats.gather_table_stats( 'FMEMC', 'SOURCE_OBJECTS', method_opt=>'for columns size 1 ID_IN_SOURCE_SYSTEM' );
END;
/

In order to make this fix permanent, the following commands need to be executed:

BEGIN
dbms_stats.set_table_prefs('FMEMC', 'SOURCE_OBJECTS','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 ID_IN_SOURCE_SYSTEM');
END;
/

For case insensitive search 
(MC version between 25.2 and 26.1 with caseSensitive parameter unchecked)

Run the following query to find the name of the generated column: 

SELECT
    index_name,
    table_name,
    column_position,
    column_name,
    descend
FROM all_ind_columns
WHERE index_owner = UPPER('FMEMC')
  AND table_name = UPPER('SOURCE_OBJECTS')
  AND index_name = UPPER('source_objects_idx7');

Then run the same SQLs as above but for this new column SYS_NC00031$: 
(Name will be different so use the one from the query result instead of SYS_NC00031$): 

BEGIN
 dbms_stats.gather_table_stats('FMEMC','SOURCE_OBJECTS', method_opt=>'for columns size 1 SYS_NC00031$ ID_IN_SOURCE_SYSTEM', no_invalidate=>FALSE);
END;
/

And then the following SQL to make changes permanent:

BEGIN
 dbms_stats.set_table_prefs('FMEMC','SOURCE_OBJECTS','METHOD_OPT', 'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 1 ID_IN_SOURCE_SYSTEM SYS_NC00031$');
END;
/