Re: 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:37:34 +0000 (UTC)
Sorry, I should have described the structure of the table.
SQL> desc report.job Name Null? Type
----------------------------------------- -------- ----------------------------
IDENTIFIER NOT NULL NUMBER(19) CREATED
NOT NULL DATE LASTUPDATED
DATE VERSION NOT NULL NUMBER(10)
AUDIOLENGTHMS NUMBER(10) AUTHORID
VARCHAR2(255) CONFIDENCE
FLOAT(126) EFFECTIVEDATE
DATE DICTATIONDATE DATE EDITEDDATE
TIMESTAMP(6) EDITEDDOCUMENTID
VARCHAR2(255) EDITINGDATE
TIMESTAMP(6) ERRORRATE FLOAT(126)
ERRORRATEFULL FLOAT(126)
ERRORRATEWORDSONLY FLOAT(126) FINALIZEDDATE
TIMESTAMP(6) HYPCHARACTERS
NUMBER(10) INITIALDOCUMENTID
VARCHAR2(255) ISTEST NUMBER(1)
JOBEXTERNALID VARCHAR2(255) JOBFAILED
NUMBER(1) JOBID
NOT NULL VARCHAR2(255) LISTITEMSCORRECT
NUMBER(10) LISTITEMSDELETED NUMBER(10)
LISTITEMSINREF NUMBER(10) LISTITEMSINSERTED
NUMBER(10) LISTSCORRECT
NUMBER(10) LISTSDELETED
NUMBER(10) LISTSINREF NUMBER(10)
LISTSINSERTED NUMBER(10) MEDIAID
VARCHAR2(255) NUMBOGUSEDITSESSIONS
NUMBER(10) NUMEDITSESSIONS
NUMBER(10) REFCHARACTERS NUMBER(10)
REPORTINGFAILED NUMBER(1)
REPORTINGFAILURECODE NUMBER(10) SCHEDULEDDATE
DATE SECTIONSCORRECT
NUMBER(10) SECTIONSDELETED
NUMBER(10) SECTIONSINREF NUMBER(10)
SECTIONSINSERTED NUMBER(10)
SECTIONSSUBSTITUTED NUMBER(10)
SUBSECTIONSCORRECT NUMBER(10)
SUBSECTIONSDELETED NUMBER(10) SUBSECTIONSINREF
NUMBER(10) SUBSECTIONSINSERTED
NUMBER(10) SUBSECTIONSSUBSTITUTED
NUMBER(10) TRANSCRIBEDDATE TIMESTAMP(6)
TRANSCRIBEDDOCUMENTID VARCHAR2(255)
TRANSCRIPTIONPROFILEID VARCHAR2(255) WASEDITED
NUMBER(1) WASPICKEDUP
NUMBER(1) WASCANCELLED
NUMBER(1) WASTRANSCRIBED NUMBER(1)
WASFINALIZED NUMBER(1) CUSTOMER
NOT NULL VARCHAR2(255) DEPARTMENT
VARCHAR2(255) MTSOCUSTOMER
VARCHAR2(255) WORKTYPE
VARCHAR2(255) AUTHORQSCORE FLOAT(126)
ISSPLIT NUMBER(1) PEAKLOAD
FLOAT(126) PREDICTEDQSCORE
FLOAT(126) PRIORITY
FLOAT(126) DOMAIN VARCHAR2(255)
FROMLEGACYDB NUMBER(1) ENVIRONMENTID
NOT NULL NUMBER(10) ISLIVE
NUMBER(1) DICTATIONDURATIONMS
NUMBER(19) DRAFTWAITTIMEMS NUMBER(19)
DRAFTGENERATIONTIMEMS NUMBER(19) SPLITCHILD
NUMBER(1) IGNORE
NUMBER(1) FACILITYID
VARCHAR2(255) UPDATEDATE TIMESTAMP(6)
GROUPID VARCHAR2(255)
SERVERDRAFTGENERATIONTIMEMS NUMBER(19) RECOGNIZERID
VARCHAR2(255) TRANSCRIPTIONDURATION
NUMBER(19) MEDIAQUALITYCODE
VARCHAR2(255) FORMATTED_EFFECTIVEDATE DATE
FORMATTED_EDITINGDATE DATE
FORMATTED_TRANSCRIBEDDATE DATE FORMATTED_EDITEDDATE
DATE
SQL>
On Thursday, 22 January 2015, 10:18, Zabair Ahmed <roon987@xxxxxxxxxxx>
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 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:
