Databases
StackOverflow2013
Postsdbo
POWhy would an Oracle synonym return a different number of rows to the underlying table?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhy would an Oracle synonym return a different number of rows to the underlying table?
    primarykey
    data
    text
    <p>I have a very unusual situation that I am hoping someone will be able to shed some light onto. My understanding of an oracle synonym is that it is basically an alias to a table in another schema.</p> <p>When I do a count from the synonym, it returns zero rows. When I do the same from the underlying table, it returns 12 thousand rows.</p> <p>I cannot explain this discrepancy. Can anyone help?</p> <pre><code>select * from dba_synonyms where synonym_name = 'CS_INCIDENTS_B_SEC'; OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ------ ------------------- ------------ ------------------- ------- APPS CS_INCIDENTS_B_SEC CS CS_INCIDENTS_ALL_B select count(*) from CS.CS_INCIDENTS_ALL_B; COUNT(*) ---------------------- 12549 select count(*) from APPS.CS_INCIDENTS_B_SEC; COUNT(*) ---------------------- 0 </code></pre> <p>Explain plans:</p> <p>Directly on the table...</p> <pre><code>EXPLAIN PLAN FOR SELECT * FROM CS.CS_INCIDENTS_ALL_B PLAN_TABLE_OUTPUT -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes| Cost(%CPU)| -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 6056 | 1549K| 122 (3)| | 1 | TABLE ACCESS FULL| CS_INCIDENTS_ALL_B | 6056 | 1549K| 122 (3)| -------------------------------------------------------------------------- </code></pre> <p>Through the synonym...</p> <pre><code>EXPLAIN PLAN FOR SELECT * FROM APPS.CS_INCIDENTS_B_SEC PLAN_TABLE_OUTPUT --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes| Cost(%CPU)| --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 262 | 0 (0)| |* 1 | FILTER | | | | | | 2 | TABLE ACCESS FULL| CS_INCIDENTS_ALL_B | 6056 | 1549K| 122 (3)| --------------------------------------------------------------------------- 1 - filter(NULL IS NOT NULL) </code></pre> <p>Synonym chain...</p> <pre><code>SQL&gt; SELECT * 2 FROM dba_synonyms 3 START WITH 4 owner = 'CS' 5 AND synonym_name = 'CS_INCIDENTS_ALL_B' 6 CONNECT BY 7 owner = PRIOR table_owner 8 AND synonym_name = PRIOR table_name 9 / no rows selected SQL&gt; SELECT * 2 FROM dba_synonyms 3 START WITH 4 owner = 'APPS' 5 AND synonym_name = 'CS_INCIDENTS_B_SEC' 6 CONNECT BY 7 owner = PRIOR table_owner 8 AND synonym_name = PRIOR table_name 9 / </code></pre> <p>Checking Policies on database...</p> <pre><code>SQL&gt; SELECT * 2 FROM dba_policies 3 WHERE OBJECT_NAME = 'CS_INCIDENTS_B_SEC' 4 / OBJECT_OWNER OBJECT_NAME POLICY_GROUP POLICY_NAME ------------- ------------------- ------------- -------------------- APPS CS_INCIDENTS_B_SEC SYS_DEFAULT CS_SR_SEC_SR_ACCESS PF_OWNER PACKAGE FUNCTION SEL INS UPD DEL IDX CHK --------- ------------------ -------------- --- --- --- --- --- --- APPS FND_GENERIC_POLICY GET_PREDICATE YES NO NO NO NO NO ENABLE STATIC_POLICY POLICY_TYPE LONG_PREDICATE ------ ------------- ------------ -------------- YES NO DYNAMIC YES </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJonathan
    1. USJonathan
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POWhy would an Oracle synonym return a different number of rows to the underlying table?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POWhy would an Oracle synonym return a different number of rows to the underlying table?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POWhy would an Oracle synonym return a different number of rows to the underlying table?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWow... It's very weird. AFAIK, the should not return different rows or row counts...
      singulars
      1. POWhy would an Oracle synonym return a different number of rows to the underlying table?
      1. USPablo Santa Cruz
    2. COLooking forward to see an answer to this though... :-)
      singulars
      1. POWhy would an Oracle synonym return a different number of rows to the underlying table?
      1. USPablo Santa Cruz
    3. COCould you please post the query plan for both queries?
      singulars
      1. POWhy would an Oracle synonym return a different number of rows to the underlying table?
      1. USQuassnoi
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload