Forum Stats

  • 3,839,356 Users
  • 2,262,486 Discussions
  • 7,900,947 Comments

Discussions

The optimizer does not use indexes in an application pdb

Arturo Gutierrez
Arturo Gutierrez Member Posts: 337 Bronze Badge

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