Wht is the purpose of ANALYZE command. if we ANALYZE the table/index wht happens to the object and how it improves the performance? Because why i am asking is, I have table which is contain fuction based index like UPPER(column_name). When i execute the below query, it is getting full table access.
select * from table_name where upper(column_name) = upper('ABC');
At the same time, when i analyze the table it is getting index based scan in the explain plan output.
ANALYZE TABLE table_name COMPUTE STATISTICS;
One more thing is when i execute DBMS_STATS package also the table is getting full table access. See my execute command below.
EXEC DBMS_STATS.gather_index_stats('schema_name', 'index_name', estimate_percent => 30);
Can anyone share with me the solution of this? Thanks in advance.