Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

XMLINDEX not considered for XML type field

3230404May 2 2017 — edited May 7 2017

i have oracle database 12.1.0.2.0 enterprise edition.

i have the following table with two columns, one of them is XMLTYPE column, the script of the table is:

ALTER TABLE T24.FBNK_ACCOUNT

DROP PRIMARY KEY CASCADE;

DROP TABLE T24.FBNK_ACCOUNT CASCADE CONSTRAINTS;

CREATE TABLE T24.FBNK_ACCOUNT

(

RECID VARCHAR2(255 BYTE) NOT NULL,

XMLRECORD SYS.XMLTYPE,

ARRANGEMENT_ID VARCHAR2(30 BYTE) GENERATED ALWAYS AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY('/row/c181' PASSING BY VALUE SYS_MAKEXML(0,"SYS_NC00003$") RETURNING CONTENT ),0,0,16777216,1073741824),50,1,2) AS varchar2(30))),

OFFICER VARCHAR2(30 BYTE) GENERATED ALWAYS AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY('/row/c11' PASSING BY VALUE SYS_MAKEXML(0,"SYS_NC00003$") RETURNING CONTENT ),0,0,16777216,1073741824),50,1,2) AS varchar2(30))),

CATEGORY NUMBER(10) GENERATED ALWAYS AS (TO_NUMBER(CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY('/row/c2' PASSING BY VALUE SYS_MAKEXML(0,"SYS_NC00003$") RETURNING CONTENT ),0,0,16777216,1073741824),50,1,2) AS varchar2(30)))),

ACCOUNT_TITLE_1 VARCHAR2(70 BYTE) GENERATED ALWAYS AS (CAST(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(XMLQUERY('/row/c3' PASSING BY VALUE SYS_MAKEXML(0,"SYS_NC00003$") RETURNING CONTENT ),0,0,16777216,1073741824),50,1,2) AS varchar2(30)))

)

XMLTYPE XMLRECORD STORE AS BINARY XML (

TABLESPACE T24INDX

ENABLE STORAGE IN ROW

CHUNK 8192

RETENTION

CACHE

LOGGING

INDEX (

    TABLESPACE T24INDX

    STORAGE    (

                INITIAL          64K

                NEXT             1M

                MINEXTENTS       1

                MAXEXTENTS       UNLIMITED

                PCTINCREASE      0

                BUFFER\_POOL      DEFAULT

               ))

  STORAGE    (

              INITIAL          104K

              NEXT             1M

              MINEXTENTS       1

              MAXEXTENTS       UNLIMITED

              PCTINCREASE      0

              BUFFER\_POOL      DEFAULT

             ))

ALLOW NONSCHEMA

DISALLOW ANYSCHEMA

TABLESPACE T24DATA

PCTUSED 0

PCTFREE 10

INITRANS 1

MAXTRANS 255

STORAGE (

        INITIAL          64K

        NEXT             1M

        MINEXTENTS       1

        MAXEXTENTS       UNLIMITED

        PCTINCREASE      0

        BUFFER\_POOL      DEFAULT

       )

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

CREATE INDEX T24.FBNK_ACCOUNT_IX1 ON T24.FBNK_ACCOUNT

(ARRANGEMENT_ID)

LOGGING

TABLESPACE T24INDX

PCTFREE 10

INITRANS 2

MAXTRANS 255

STORAGE (

        INITIAL          64K

        NEXT             1M

        MINEXTENTS       1

        MAXEXTENTS       UNLIMITED

        PCTINCREASE      0

        BUFFER\_POOL      DEFAULT

       )

NOPARALLEL;

CREATE INDEX T24.IX_FBNK_ACCOUNT ON T24.FBNK_ACCOUNT

(SYS_MAKEXML(0,"SYS_NC00003$"))

INDEXTYPE IS XDB.XMLINDEX

PARAMETERS(' PATH TABLE "SYS4753500_IX_FBNK__PATH_TABLE" PATHS ( INCLUDE ( /row/c181 /row/c100 )) TABLESPACE T24INDX')

NOPARALLEL;

-- There is no statement for index T24.SYS_C002081564.

-- The object is created automatically by Oracle when the parent object is created.

ALTER TABLE T24.FBNK_ACCOUNT ADD (

PRIMARY KEY

(RECID)

USING INDEX

TABLESPACE T24INDX

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

           ));

as u see in the above script, i have created a xmlindex for the nodes c181 and c100:

CREATE INDEX T24.IX_FBNK_ACCOUNT ON T24.FBNK_ACCOUNT

(SYS_MAKEXML(0,"SYS_NC00003$"))

INDEXTYPE IS XDB.XMLINDEX

PARAMETERS(' PATH TABLE "SYS4753500_IX_FBNK__PATH_TABLE" PATHS ( INCLUDE ( /row/c181 /row/c100 )) TABLESPACE T24INDX')

NOPARALLEL;

the problem is that when do a SQL statmenet over the indexed node oracle does not consider the index at all:

for example:

SELECT ax.arr

FROM T24.FBNK_ACCOUNT a

   cross apply xmltable('/row'

                 passing a.xmlrecord   

                 columns  arr        varchar2(50)   path 'c181'

               ) ax

WHERE ax.arr='AA17001L2GV6';

it always give me a FULL TABLE SCAN.

Note: all the tables in system that im working on have the same structure and has the same issue.

usually on the system we use (select) from views over xmltype tables, for example for the above FBNK.ACCOUNT table there is a view called TAFJV_FBNK_ACCOUNT with the attached script, the following SQL is another example of the issue where the xmlindex is not considered at all:

SELECT RECID FROM "TAFJV_FBNK_ACCOUNT" WHERE ARRANGEMENT_ID=:1