ORA-01555补充

SQL> set linesize 1000
SQL> set pagesize 1000
SQL> explain plan for SELECT "A1"."EMPL_CLASS",
       "A1"."DESCR1",
       "A1"."DEPTID",
       "A1"."DEPT_DESCR",
       "A1"."JOBCODE",
       "A1"."JOBCODE_DESCR",
       "A1"."BUSINESS_UNIT",
       "A1"."EMPLID",
       "A1"."HR_STATUS",
       "A1"."JOB_INDICATOR",
       "A1"."EMPL_CLASS",
       "A1"."COMPANY",
       "A1"."DEPTID",
       "A1"."DEPTID",
       "A1"."JOBCODE",
       "A2"."EMPLID",
       "A2"."LAST_NAME",
       "A2"."FIRST_NAME",
       "A2"."EMAILID",
       "A2"."PHONE",
       "A2"."MOBILE_PHONE",
       "A2"."EMPLID"
  FROM "PS_SGC_JOB" "A1", "PS_SGC_PERSON" "A2"
   WHERE "A2"."EMPLID" = "A1"."EMPLID"
   AND "A1"."BUSINESS_UNIT" = 'BU004'
   AND "A1"."HR_STATUS" = 'A'
   AND "A1"."JOB_INDICATOR" = 'P'
   AND "A1"."COMPANY" = '147'
   AND "A1"."EMPL_CLASS" <> '10'   ;


Explained.


SQL>  select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2410736127


---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                      | Rows  | Bytes   | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |  1175 | 378K| 124   (0)| 00:00:02 |
|*  1 |  HASH JOIN                            |                      |  1175 | 378K| 124   (0)| 00:00:02 |
|   2 |   JOIN FILTER CREATE                    | :BF0000              |  1175 | 323K| 102   (0)| 00:00:02 |
|*  3 |    TABLE ACCESS STORAGE FULL  | PS_SGC_JOB        |  1175 | 323K| 102   (0)| 00:00:02 |
|   4 |   JOIN FILTER USE                            | :BF0000              |  6364 | 298K| 22   (0) | 00:00:01 |
|*  5 |    TABLE ACCESS STORAGE FULL  | PS_SGC_PERSON |  6364 | 298K|   22   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------


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


   1 - access("A2"."EMPLID"="A1"."EMPLID")
   3 - storage("A1"."BUSINESS_UNIT"='BU004' AND "A1"."HR_STATUS"='A' AND
     "A1"."JOB_INDICATOR"='P' AND "A1"."COMPANY"='147' AND "A1"."EMPL_CLASS"<>'10')
       filter("A1"."BUSINESS_UNIT"='BU004' AND "A1"."HR_STATUS"='A' AND
     "A1"."JOB_INDICATOR"='P' AND "A1"."COMPANY"='147' AND "A1"."EMPL_CLASS"<>'10')
   5 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"A2"."EMPLID"))
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"A2"."EMPLID"))


Note
-----
   - dynamic sampling used for this statement (level=2)


27 rows selected.

SQL> 

SQL> select owner ,OBJECT_NAME from dba_objects where OBJECT_NAME like '%PS_SGC_PERSON%';


OWNER       OBJECT_NAME
------------------------------ --------------------------------------------------
SYSADM       PS_SGC_PERSON0_BAK
PUBLIC       PS_SGC_PERSON
SYSADM       PS_SGC_PERSON
SYSADM       PS_SGC_PERSON
CIS_PS       PS_SGC_PERSON
SYSADM       PS_SGC_PERSON0
SRLPG_PS       PS_SGC_PERSON
AD_PS       PS_SGC_PERSON


8 rows selected.


SQL> select owner ,OBJECT_NAME from dba_objects where OBJECT_NAME like '%PS_SGC_JOB%';


OWNER       OBJECT_NAME
------------------------------ --------------------------------------------------
SYSADM       PS_SGC_JOB0_BAK
SYSADM       PS_SGC_JOB_FUN_VW
SYSADM       PS_SGC_JOBCODE_VW2
SYSADM       PS_SGC_JOB_POSN
SYSADM       PS_SGC_JOB_POSN
SYSADM       PS_SGC_JOB_POSN0
SYSADM       PS_SGC_JOB0
SYSADM       PS_SGC_JOBCODE0
SYSADM       PS_SGC_JOB_LVL_VW
SYSADM       PS_SGC_JOBCODE_VW
SYSADM       PS_SGC_JOBCODE
SYSADM       PS_SGC_JOBCODE
PUBLIC       PS_SGC_JOB
SYSADM       PS_SGC_JOBDES_VW
CIS_PS       PS_SGC_JOB
SRLPG_PS       PS_SGC_JOB
AD_PS       PS_SGC_JOB
AD_PS       PS_SGC_JOB_POSN
AD_PS       PS_SGC_JOBCODE
SYSADM       PS_SGC_JOB_COST_VW
EBS_PS       PS_SGC_JOB
SYSADM       PS_SGC_JOB
SYSADM       PS_SGC_JOB


23 rows selected.


SQL> 

--查看表的stats
select * from user_tab_statistics where table_name = 'STUDENT'


--查看列的stats
select * from user_tab_col_statistics where table_name = 'STUDENT'


--查看索引的stats
select * from user_ind_statistics where table_name = 'STUDENT' 

SQL> select owner, object_name, object_type from dba_objects where OBJECT_NAME in ('PS_SGC_JOB' , 'PS_SGC_PERSON');


OWNER       OBJECT_NAME   OBJECT_TYPE
------------------------------ -------------------- -------------------
APPS       PS_SGC_JOB   SYNONYM
APPS       PS_SGC_PERSON   SYNONYM
QAPPS       PS_SGC_JOB   SYNONYM
QAPPS       PS_SGC_PERSON   SYNONYM


SQL> select TABLE_NAME, PARTITIONING_TYPE from dba_part_tables where TABLE_NAME in ('PS_SGC_JOB' , 'PS_SGC_PERSON'); 


no rows selected


SQL> 

SQL> SELECT * FROM SYS.ALL_SYNONYMS WHERE SYNONYM_NAME IN ('PS_SGC_JOB' , 'PS_SGC_PERSON');


OWNER       SYNONYM_NAME
------------------------------ ------------------------------
TABLE_OWNER       TABLE_NAME
------------------------------ ------------------------------
DB_LINK
--------------------------------------------------------------------------------
AD_PS       PS_SGC_JOB
SYSADM       PS_SGC_JOB




AD_PS       PS_SGC_PERSON
SYSADM       PS_SGC_PERSON




CIS_PS       PS_SGC_JOB
SYSADM       PS_SGC_JOB




CIS_PS       PS_SGC_PERSON
SYSADM       PS_SGC_PERSON




EBS_PS       PS_SGC_JOB
SYSADM       PS_SGC_JOB




PUBLIC       PS_SGC_JOB
SYSADM       PS_SGC_JOB




PUBLIC       PS_SGC_PERSON
SYSADM       PS_SGC_PERSON




SRLPG_PS       PS_SGC_JOB
SYSADM       PS_SGC_JOB




SRLPG_PS       PS_SGC_PERSON
SYSADM       PS_SGC_PERSON






9 rows selected.


SQL> analyze table SYSADM.PS_SGC_JOB validate structure cascade;


Table analyzed.


SQL> analyze table SYSADM.PS_SGC_PERSON  validate structure cascade;


Table analyzed.


SQL>