Oracle SQL Query different results -

we running oracle database 11g release 11.2.0.2.0 - 64bit production/

we have following query join between user, user_learning , user_group table.

select u.user_id, u.first_name, u.surname, u.client_company_id, u.username,           ul.completion_status  user u, user_learning ul         u.user_id = ul.user_id (+)    , (ul.enrolment_status = 'e'   or ul.enrolment_status null)    , upper(u.surname) 'cart%'   , ((u.user_id not null  , (u.client_company_id in ('abcdef') )          , exists (select 1                     user_group g                    g.user_id = u.user_id                       , g.group_id in                            (215479,215480,221934,39901,45709,45710,45712,                          45713,45714,45715,45716,45717,45718)                    )         )         or (u.user_id = 1209289 or u.manager_id = 1209289)       )   order u.client_company_id, u.surname, u.first_name, u.user_id; 

this query gives 0 results , 198 each time runs alternating , returning 0 etc..

some background info on tables , have tried far,

-- gives 1184415 records select count(1) user;   -- gives 7789332 records select count(1) user_learning;   -- gives 3278032 select count(1) user_group;  

there index on user table column surname follows, create index idx_user_upper on user (upper("surname")) user_group contains group_id , user_id primary key user_id primary key in user table

what have noticed when drop idx_user_upper index , run query results consistent always. having index means after time results not same. had doubt on index , droping , running query seemed produce consistent result little time again query stoped giving consistent result. change upper(u.surname) 'cart%' 'car%' or 'carte%' etc... starts behaving strange giving 0 or more records etc..

the explain plan indicates when index used @ times , @ times not used.. maybe index not issue...??

i have tried gathering stats on index , rebuilding index

create index idx_user_upper on user (upper("surname"))  alter index idx_user_upper compute statistics alter index idx_user_upper rebuild  

have run following still same effect except when index dropped seems work while.

exec dbms_stats.gather_table_stats('schema', 'user'); exec dbms_stats.gather_table_stats('schema', 'user_learning'); exec dbms_stats.gather_table_stats('schema', 'user_group');  exec dbms_stats.gather_index_stats ( 'schema', 'idx_user_upper'); -- note schema our schema name  -- indicates last_analysed updated ... select table_name, owner, to_char(last_analyzed, 'dd-mon-yyyy hh24:mi:ss')    dba_tables  table_name in ('user', 'user_learning', 'user_group') 

another option tried setting "_no_or_expansion" flag follows,

alter session set "_no_or_expansion"=true; -- no impact query though 

(sometimes looks when index dropped query keeps returning consistent data when 'car%', "cart%', 'carte%', 'carter%' ever queried - strange )

but still strange behavior exists thoughts or questions or solutions, if of have come across similar please.

---

running select * dba_autotask_client shows
auto optimizer stats collection enabled

sounds buggy behaviour, careful not using views/functions nondeterministic behaviour in them (i.e. stateful in way, don't return same answer same inputs).

i'd have @ execution plans queries using: possibility different execution plans being used, , plans give different results. shouldn't do, unless have messed , queries nondeterministic, if so, able pin down , complain oracle.

one way run query , run

select * table(dbms_xplan.display_cursor(format=>'advanced')); 

which should show execution plan last thing run in session, sadly won't work in pl/sql developer or other tools silently run queries either side of yours fetch dbms_output , on (the 'last' query run in session won't 1 gave it)

otherwise

select * v$sql lower(sql_fulltext) '%client_company_id%'; 

... put discriminating query text in clause - chose client_company_id example i've no idea how common in queries. either way, bit of effort should able see row or rows in v$sql correspond query, , find sql_id. hash of sql text same query text (identical down whitespace , on) produce same sql_id.

then can select * table(dbms_xplan.display_cursor(, 0, format=>'advanced');

if have multiple rows in v$sql same sql_id, strong indicator multiple execution plans have been spawned query. cursors have same sql_id different 'child_number's.

select * table(dbms_xplan.display_cursor(<sql id>, <child number>, format=>'advanced'); 

look see if different execution plans being used. in particular out 'cardinality feedback used statement' or 'statistics feedback used statement' in 'notes' section.

cardinality feedback when optimizer knows making suspect estimate in generating execution plan, , observes run-time behaviour generate correction factor, , plugs in next execution of query generate whole new plan.

it's clever when works, has caused fair few headaches too.

either way, if have hit bug whereby different execution plans produce different results, 1 way in execution plan (and results) change execution execution.

if 'bad' plans involved using index on upper(...), dropping index prevent plans being generated, , change behaviour again.