Re: Query giving different results using server sqlplus and client sqlplus

Sent from my iPhone

> On 22 Jan 2015, at 10:18, "Zabair Ahmed" <roon987_at_yahoo.co.uk> wrote:
>
> Hello All,
>
> Can somebody help with this, I think am going mad.
>
> Am running some simple select queries 'select count(*) from report.job where transcribeddate < '01-OCT-2014';' and 'select count(*) from report.job;'.
>
> When I run these queries within sqlplus logged directly onto the server where the database is running, I get the following results. Am logged in as system in both cases:-
>
>
>
> SQL> show user
> USER is "SYSTEM"
> SQL> select name from v$database;
>
> NAME
> ---------
> DV1DW
>
> SQL> select * from v$version;
>
> BANNER
> --------------------------------------------------------------------------------
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
> PL/SQL Release 11.2.0.2.0 - Production
> CORE 11.2.0.2.0 Production
> TNS for Linux: Version 11.2.0.2.0 - Production
> NLSRTL Version 11.2.0.2.0 - Production
>
> SQL> select count(*) from report.job where transcribeddate < '01-OCT-2014';
>
> COUNT(*)
> ----------
> 6758430
>
> SQL> select count(*) from report.job;
>
> COUNT(*)
> ----------
> 19626224
>
> SQL>
>
>
> And when I run the same queries within a client sqlplus I get the following results.
>
> SQL> show user
> USER is "SYSTEM"
> SQL> select name from v$database;
>
> NAME
> ---------
> DV1DW
>
> SQL> select * from v$version;
>
> BANNER
> --------------------------------------------------------------------------------
>
> Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
> PL/SQL Release 11.2.0.2.0 - Production
> CORE 11.2.0.2.0 Production
> TNS for Linux: Version 11.2.0.2.0 - Production
> NLSRTL Version 11.2.0.2.0 - Production
>
> SQL> select count(*) from report.job where transcribeddate < '01-OCT-2014';
>
> COUNT(*)
> ----------
> 18185097
>
> SQL> select count(*) from report.job;
>
> COUNT(*)
> ----------
> 19626224
>
> SQL>
>
>
> Why would i get different number of rows returned for the first query in each case?
>
> Here is the structure of the table and it's the same irrespective of where the sql is executed.
>
> SQL> select object_name,object_type,created,status from dba_objects where object_name = 'JOB' and owner = 'REPORT';
>
> OBJECT_NAME OBJECT_TYPE CREATED STATUS
> ------------------------------ ------------------- --------- -------
> JOB TABLE 11-JUL-13 VALID
> JOB TABLE PARTITION 11-JUL-13 VALID
> JOB TABLE PARTITION 11-JUL-13 VALID
> JOB TABLE PARTITION 11-JUL-13 VALID
> JOB TABLE PARTITION 11-JUL-13 VALID
> JOB TABLE PARTITION 12-OCT-13 VALID
> JOB TABLE PARTITION 13-JUN-14 VALID
> JOB TABLE PARTITION 13-JUN-14 VALID
> JOB TABLE PARTITION 20-NOV-14 VALID
> JOB TABLE PARTITION 20-NOV-14 VALID
> JOB TABLE PARTITION 20-NOV-14 VALID
>
> OBJECT_NAME OBJECT_TYPE CREATED STATUS
> ------------------------------ ------------------- --------- -------
> JOB TABLE PARTITION 20-NOV-14 VALID
> JOB TABLE PARTITION 20-NOV-14 VALID
> JOB TABLE PARTITION 09-JAN-15 VALID
> JOB TABLE PARTITION 10-JAN-15 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
>
> OBJECT_NAME OBJECT_TYPE CREATED STATUS
> ------------------------------ ------------------- --------- -------
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 11-JUL-13 VALID
> JOB TABLE SUBPARTITION 12-OCT-13 VALID
> JOB TABLE SUBPARTITION 12-OCT-13 VALID
>
> OBJECT_NAME OBJECT_TYPE CREATED STATUS
> ------------------------------ ------------------- --------- -------
> JOB TABLE SUBPARTITION 12-OCT-13 VALID
> JOB TABLE SUBPARTITION 12-OCT-13 VALID
> JOB TABLE SUBPARTITION 13-JUN-14 VALID
> JOB TABLE SUBPARTITION 13-JUN-14 VALID
> JOB TABLE SUBPARTITION 13-JUN-14 VALID
> JOB TABLE SUBPARTITION 13-JUN-14 VALID
> JOB TABLE SUBPARTITION 13-JUN-14 VALID
> JOB TABLE SUBPARTITION 13-JUN-14 VALID
> JOB TABLE SUBPARTITION 13-JUN-14 VALID
> JOB TABLE SUBPARTITION 13-JUN-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
>
> OBJECT_NAME OBJECT_TYPE CREATED STATUS
> ------------------------------ ------------------- --------- -------
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
>
> OBJECT_NAME OBJECT_TYPE CREATED STATUS
> ------------------------------ ------------------- --------- -------
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 20-NOV-14 VALID
> JOB TABLE SUBPARTITION 09-JAN-15 VALID
> JOB TABLE SUBPARTITION 09-JAN-15 VALID
> JOB TABLE SUBPARTITION 09-JAN-15 VALID
>
> OBJECT_NAME OBJECT_TYPE CREATED STATUS
> ------------------------------ ------------------- --------- -------
> JOB TABLE SUBPARTITION 09-JAN-15 VALID
> JOB TABLE SUBPARTITION 10-JAN-15 VALID
> JOB TABLE SUBPARTITION 10-JAN-15 VALID
> JOB TABLE SUBPARTITION 10-JAN-15 VALID
> JOB TABLE SUBPARTITION 10-JAN-15 VALID
>
> 71 rows selected.
>
> TIA
>

--
http://www.freelists.org/webpage/oracle-l