Oracle EBS R12 - 查询同一个表在不同用户下的同义词(XLA_TRANSACTION_ENTITIES)时返回的结果不一样

本文主要是介绍Oracle EBS R12 - 查询同一个表在不同用户下的同义词(XLA_TRANSACTION_ENTITIES)时返回的结果不一样，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

Oracle EBS R12 - 查询同一个表在不同用户下的同义词(XLA_TRANSACTION_ENTITIES)时返回的结果不一样

在EBS R12系统中有张表"XLA"."XLA_TRANSACTION_ENTITIES",并且同时存在APPS和APPSREAD针对该表的同义词,
定义如下:
CREATE OR REPLACE SYNONYM "APPS"."XLA_TRANSACTION_ENTITIES" FOR "XLA"."XLA_TRANSACTION_ENTITIES";
CREATE OR REPLACE SYNONYM "APPSREAD"."XLA_TRANSACTION_ENTITIES" FOR "XLA"."XLA_TRANSACTION_ENTITIES";

但是针对同义词相同条件的查询返回的结果却不一样:
SQL> select count(*) from  apps.XLA_TRANSACTION_ENTITIES where application_id=222;

  COUNT(*)
----------
         1

SQL> select count(*) from  appsread.XLA_TRANSACTION_ENTITIES where application_id=222;

  COUNT(*)
----------
    112884

SQL>

看到这个问题首先想到就是用trace跟踪一下:
SQL> set autotrace traceonly explain
SQL> select count(*) from  apps.XLA_TRANSACTION_ENTITIES where application_id=222;

Execution Plan
----------------------------------------------------------
Plan hash value: 2139858008

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                          |     1 |     8 |   481   (2)| 00:00:06 |    |          |
|   1 |  SORT AGGREGATE        |                          |     1 |     8 |            |          |    |          |
|   2 |   PARTITION LIST SINGLE|                          |     1 |     8 |   481   (2)| 00:00:06 |   KEY |   KEY |
|*  3 |    TABLE ACCESS FULL   | XLA_TRANSACTION_ENTITIES |     1 |     8 |   481   (2)| 00:00:06 |  2 |        2 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("SECURITY_ID_INT_1" IS NULL)

SQL> select count(*) from  appsread.XLA_TRANSACTION_ENTITIES where application_id=222;

Execution Plan
----------------------------------------------------------
Plan hash value: 1648015674

----------------------------------------------------------------------------------------------------------------------

这篇关于Oracle EBS R12 - 查询同一个表在不同用户下的同义词(XLA_TRANSACTION_ENTITIES)时返回的结果不一样的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---