Query giving different results using server sqlplus and client sqlplus
- From: Zabair Ahmed <roon987@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Jan 2015 10:16:52 +0000 (UTC)
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 userUSER 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 ProductionPL/SQL
Release 11.2.0.2.0 - ProductionCORE 11.2.0.2.0 ProductionTNS for Linux:
Version 11.2.0.2.0 - ProductionNLSRTL 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 userUSER 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
ProductionPL/SQL Release 11.2.0.2.0 - ProductionCORE 11.2.0.2.0
ProductionTNS for Linux: Version 11.2.0.2.0 - ProductionNLSRTL 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 VALIDJOB
TABLE PARTITION 11-JUL-13 VALIDJOB
TABLE PARTITION 11-JUL-13 VALIDJOB TABLE
PARTITION 11-JUL-13 VALIDJOB TABLE PARTITION
11-JUL-13 VALIDJOB TABLE PARTITION 12-OCT-13
VALIDJOB TABLE PARTITION 13-JUN-14 VALIDJOB
TABLE PARTITION 13-JUN-14 VALIDJOB
TABLE PARTITION 20-NOV-14 VALIDJOB TABLE
PARTITION 20-NOV-14 VALIDJOB TABLE PARTITION
20-NOV-14 VALID
OBJECT_NAME OBJECT_TYPE CREATED
STATUS------------------------------ ------------------- --------- -------JOB
TABLE PARTITION 20-NOV-14 VALIDJOB
TABLE PARTITION 20-NOV-14 VALIDJOB
TABLE PARTITION 09-JAN-15 VALIDJOB TABLE
PARTITION 10-JAN-15 VALIDJOB TABLE SUBPARTITION
11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13
VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB
TABLE SUBPARTITION 11-JUL-13 VALIDJOB
TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE
SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION
11-JUL-13 VALID
OBJECT_NAME OBJECT_TYPE CREATED
STATUS------------------------------ ------------------- --------- -------JOB
TABLE SUBPARTITION 11-JUL-13 VALIDJOB
TABLE SUBPARTITION 11-JUL-13 VALIDJOB
TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE
SUBPARTITION 11-JUL-13 VALIDJOB TABLE SUBPARTITION
11-JUL-13 VALIDJOB TABLE SUBPARTITION 11-JUL-13
VALIDJOB TABLE SUBPARTITION 11-JUL-13 VALIDJOB
TABLE SUBPARTITION 11-JUL-13 VALIDJOB
TABLE SUBPARTITION 11-JUL-13 VALIDJOB TABLE
SUBPARTITION 12-OCT-13 VALIDJOB TABLE SUBPARTITION
12-OCT-13 VALID
OBJECT_NAME OBJECT_TYPE CREATED
STATUS------------------------------ ------------------- --------- -------JOB
TABLE SUBPARTITION 12-OCT-13 VALIDJOB
TABLE SUBPARTITION 12-OCT-13 VALIDJOB
TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE
SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION
13-JUN-14 VALIDJOB TABLE SUBPARTITION 13-JUN-14
VALIDJOB TABLE SUBPARTITION 13-JUN-14 VALIDJOB
TABLE SUBPARTITION 13-JUN-14 VALIDJOB
TABLE SUBPARTITION 13-JUN-14 VALIDJOB TABLE
SUBPARTITION 13-JUN-14 VALIDJOB TABLE SUBPARTITION
20-NOV-14 VALID
OBJECT_NAME OBJECT_TYPE CREATED
STATUS------------------------------ ------------------- --------- -------JOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE
SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION
20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14
VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE
SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION
20-NOV-14 VALID
OBJECT_NAME OBJECT_TYPE CREATED
STATUS------------------------------ ------------------- --------- -------JOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB TABLE
SUBPARTITION 20-NOV-14 VALIDJOB TABLE SUBPARTITION
20-NOV-14 VALIDJOB TABLE SUBPARTITION 20-NOV-14
VALIDJOB TABLE SUBPARTITION 20-NOV-14 VALIDJOB
TABLE SUBPARTITION 20-NOV-14 VALIDJOB
TABLE SUBPARTITION 09-JAN-15 VALIDJOB TABLE
SUBPARTITION 09-JAN-15 VALIDJOB TABLE SUBPARTITION
09-JAN-15 VALID
OBJECT_NAME OBJECT_TYPE CREATED
STATUS------------------------------ ------------------- --------- -------JOB
TABLE SUBPARTITION 09-JAN-15 VALIDJOB
TABLE SUBPARTITION 10-JAN-15 VALIDJOB
TABLE SUBPARTITION 10-JAN-15 VALIDJOB TABLE
SUBPARTITION 10-JAN-15 VALIDJOB TABLE SUBPARTITION
10-JAN-15 VALID
71 rows selected.
TIA
Other related posts:
