Explain Plan with - where field <> 'imposibleValue'

SELECT STATEMENT.GOAL=FIRST_ROWS
SORT UNIQUE NOSORT
INDEX FULL SCAN

and the cost has reduced from 22,089 to 344 However, it only helps my understanding, not the problem!

You are right - adding a 'null' where clause such as where field <> 'imposibleValue' reduces the execution time from 1 minute to 1 second, so that is obviously forcing the use of an index. Shame I can't change the sql :(

Explain Query in PLSQL Developer shows: SELECT STATEMENT.GOAL=ALL_ROWS SORT UNIQUE TABLE ACCESS FULL The TABLE ACCESS FULL has cardinality of 2.5 milion, the other two 50, so I guess that is a full table scan

Being a SQL Server developer, I am not familiar with Oracle. We have a third party product using Oracle 10g that uses a dropdown to show the unique values from a field in a table of 2.5 million rows, but with only 50 unique values. There is no index on the field and it takes a minute to run the query 'select distinct field from table order by 1' used to populate the dropdown. Adding an index on that field should allow the optimiser to scan the index rather than the table, and as the indexed field is 1/50th of the table’s row length I would expect to see a similar decrease in the time taken to run the query. However, it made no difference to the performance. I have been told that Oracle will ALWAYS do a table scan if there is no where clause in the query. If this is the case without access to the source to change the query, I seem to have no way to optimise this. Is what I heve been told true? If not how can I get the optimiser to use the index without being able to chnage the SQL in the query?

Our company code 'review' is little short of a joke. Some months after the code has gone live the code base receives an anonymous review. The reviewer divides the project into an arbitrary number of sections and reviews each section against 16 language independent 'rules', 9 of which relate to commenting. (Yes the same rules apply to batch files, sql statements, stored procedures/triggers, vbscript, VB6, Vb.Net, asp etc). Each rule is scored for each section and if the rule does not apply, is assumed to be success. A rule is violated by any section is scored as 0. A rule passed by all sections it is scored as 1. The rule scores are then summed to give an overall score for the project, between 0 and 16. The DBA always scores well as only 4 rules apply to him so even if he fails all the rules that apply he still gets a score of 12! The poor programmer who has his work in 16 source files, each evaluated separately, each failing a single, different rule, ends up with a score of zero, although each individual source file scored 15! To make matters worse the review scores contribute to ones annual review.