2 Replies Latest reply: Dec 13, 2012 4:24 AM by AlbertoFaenza RSS

    Query executes with Index..View does Full Scan..

    user8941550
      We have table like:

      drop table xml_tbl;

      create table xml_tbl (
      xml_msg_id integer,
      xml_msg_text xmltype
      );

      insert into xml_tbl values
      (1, '<main><id>1</id></main>') ;

      insert into xml_tbl values --(xml_msg_id,xml_msg_text)
      (1, '<main><id>2</id></main>') ;

      There is an XMLType Index on a XPATH for the column xml_msg_text .

      Another table like:

      create Table Table1
      ( id1 int);

      Insert into Table1 values(2);
      Insert into Table1 values(3);


      View as follows:

      Create or Replace view V1 as
      select t.xml_msg_text
      from xml_tbl t
      where xmlexists (
      'fn:collection("oradb:/PUBLIC/TABLE1")/ROW[ID1=$d/main/id]'
      passing t.xml_msg_text as "d"
      );

      If I execute the view query only i.e.
      select t.xml_msg_text
      from xml_tbl t
      where xmlexists (
      'fn:collection("oradb:/PUBLIC/TABLE1")/ROW[ID1=$d/main/id]'
      passing t.xml_msg_text as "d"
      );

      XMLIndex is shown to be used in EXPLAIN PLAN and I get output in seconds.

      WHEREAS

      If I execute Select * from V1 when view V1 has the same underlying query.
      The execution goes into indefinite period.
      EXPLAIN PLAN also show that XML Index is not being used and that it's a FTS.
      Please suggest.