1 Reply Latest reply: Feb 12, 2009 8:18 PM by 454466 RSS

    Slow query

    454466
      The following queries run slow and performance seems to degrade linearly with table size:

      SELECT XMLQuery('for $v in /Quote
      where fn:contains($v/euid,"euid1")
      return <Row>{$v}</Row>'
      PASSING OBJECT_VALUE
      RETURNING CONTENT)
      FROM test_table1;

      select X.*
      from Test_Table1,
      xmltable(
      'for $v in /Quote
      where fn:contains($v/euid,"teuid253")
      return <Row>{$v}</Row>'
      PASSING OBJECT_VALUE
      ) X
      ;

      This query is slow and seems to offer linear performance:
      Total Recs Query Time
      ----------- -------------
      15k 1.2 sec
      25k 2.0 sec
      35k 2.8 sec
      45k 3.6 sec
      55k 4.3 sec

      Each record is 317 bytes.

      The table is Binary XML:
      CREATE TABLE test_table1 of XMLType XMLType Store as SECUREFILE Binary XML;

      Creates an index:
      Create index test_table1_euid_idx1 on test_table1
      (extractValue(object_value, '/Quote/euid'));

      The explain plan:
      Select Statement
      Filter
      Nested Loops
      Table access full test_table1
      xpath evaluation
      xpath evaluation

      Does "Table access full test_table1" mean it is doing a linear scan against all rows and programmatically evaluating the xpath expression? The linear performance suggests this.

      Here is sample insert:
      -- Remove first line: xml declaration
      INSERT INTO test_table1 VALUES (
      XMLType('

      euid1
      2008-08-15T18:59:59-05:00
      2.22
      ONE
      None
      true

      ' ));

      Is the index created right? Why doesn't the query hit the index?

      Using XMLQuery returned nulls for all unmatched rows in the table. Using XMLTABLE creates a view. Is this necessary? Is there a more straight forward way to query with XQuery?
        • 1. Re: Slow query
          454466
          One combination of index and query that works is:

          create index test_table1_euid_idx2 on test_table1(OBJECT_VALUE)
          indextype is xdb.xmlindex parameters ( 'PATHS (INCLUDE (/Quote/euid))' );

          SELECT extractValue(OBJECT_VALUE, '/Quote/euid')
          FROM Test_Table1
          WHERE extractValue(OBJECT_VALUE, '/Quote/euid') = 'teuid263f3aa7-6c2d-4c23-ba0f-17fe0aec361d';

          The explain plan and response time show that the above query uses the index.

          What XQuery would utilize the index?

          For example, the following query does not use the index:
          select X.*
          from Test_Table1,
          xmltable(
          'for $v in /Quote
          where $v/euid = "teuid123a20fd-4d7b-47cc-9f96-480ea8a8f8bc"
          return <Row>{$v}</Row>'
          PASSING OBJECT_VALUE
          ) X
          ;