Hello,
I have a scenario using 19.11 on Linux, where I have an application container (CRM_ROOT) and several application PDBS.
I have created an application with certain tables and forms of sharing.
I have this table with the DATA sharing option.
CREATE TABLE crm_owner.EMP2 sharing=data
(EMPNO NUMBER(10) ,
ENAMEVARCHAR2(10),
JOBVARCHAR2(9),
MGR NUMBER(4),
HIRED DATE,
SALT NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) constraint EMP2_deptno_fk references crm_owner.dept2(deptno));
create index crm_owner.x_emp2_empno on crm_owner.emp2 (empno);
ALTER TABLE crm_owner.emp2
ADD CONSTRAINT emp2_empno_pk PRIMARY KEY (empno)
USING INDEX crm_owner.x_emp2_empno;
When I do this query in the root app.
SQL> show con_name
CON_NAME
------------------------------
CRM_ROOT
SQL> select * from emp2 where empno=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ------------------ ---------- ---------- ----------
1 SMITH CLERK 7902 17/Dec/1980 00:00:00 800 20
The execution plan is:
select *
from table (dbms_xplan.display_cursor(format=>'TYPICAL'));
Plan hash value: 1204849588
-------------------------------------------------- ---------------------------------------
| ID | operations | Name | rows | Bytes | Cost (%CPU)|
-------------------------------------------------- ---------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP2 | 1 | 87 | 0 (0)|
|* 2 | INDEX RANGE SCAN | X_EMP2_EMPNO | 1 | | 0 (0)|
-------------------------------------------------- ---------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------- -
2 - access("EMPNO"=1)
The optimizer pick the index on empno column.
However when I do it in a pdb app.
SQL> show con_name
CON_NAME
------------------------------
CRM_SPAIN
SQL> select * from emp2 where empno=1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ------------------ ---------- ---------- ----------
1 SMITH CLERK 7902 17/Dec/1980 00:00:00 800 20
The plan is:
Plan hash value: 1129240642
-------------------------------------------------- ------------
| ID | operations | Name | rows | Bytes | Cost (%CPU)|
-------------------------------------------------- ------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | DATA LINK FULL | EMP2 | 4 | 348 | 0 (0)|
-------------------------------------------------- ------------
Predicate Information (identified by operation id):
-------------------------------------------------- -
1 - filter("EMPNO"=1)
Here the optimizer does a DATA LINK FULL operation not Index
Querying info about indexes state on app pdb.
SQL> select table_name, index_name, status,VISIBILITY,SEGMENT_CREATED from user_indexes;
TABLE INDEX_NAME STATUS VISIBILIT SEG
----- -------------------- -------- --------- ---
DEPT2 X_DEPT2_DEPTNO VALID VISIBLE YES
EMP1 EMP1_EMPNO_PK VALID VISIBLE YES
EMP2 X_EMP2_EMPNO VALID VISIBLE YES
EMP3 EMP3_EMPNO_PK VALID VISIBLE NO
When sharing a table with the DATA option, the query is supposed to be executed in the root app, so the index should be used.
Any ideas on this interpretation? And why the execution plan in the pdb app does not use the index on the empno column.
Many Thanks
Arturo