20 SQL Access Using RESOURCE_VIEW and PATH_VIEW

Oracle XML DB RESOURCE_VIEW and PATH_VIEW

Figure 20-1 shows how Oracle XML DB RESOURCE_VIEW and PATH_VIEW provide a mechanism for using SQL to access data stored in Oracle XML DB repository. Data stored in Oracle XML DB repository through protocols such as FTP, WebDAV, or application program interfaces (APIs), can be accessed in SQL using RESOURCE_VIEW values and PATH_VIEW values, and vice versa.

RESOURCE_VIEW consists of a resource, itself an XMLType, that contains the name of the resource that can be queried, its ACLs, and its properties, static or extensible.

• If the content comprising the resource is XML, stored somewhere in an XMLType table or view, then the RESOURCE_VIEW points to the XMLType row that stores the content.

• If the content is not XML, then the RESOURCE_VIEW stores it as a LOB.

Parent-child relationships between folders (necessary to construct the hierarchy) are maintained and traversed efficiently using the hierarchical index. Text indexes are available to search the properties of a resource, and internal B*Tree indexes over Names and ACLs speed up access to these attributes of the Resource XMLType.

RESOURCE_VIEW and PATH_VIEW together, along with PL/SQL package, DBMS_XDB, provide all query-based access to Oracle XML DB and DML functionality that is available through the programming API.

The base table for RESOURCE_VIEW is XDB.XDB$RESOURCE and should only be accessed through RESOURCE_VIEW or the DBMS_XDB API.

Figure 20-1 Accessing Repository Resources Using RESOURCE_VIEW and PATH_VIEW

Description of the illustration adxdb034.gif

Column      Datatype     Description 
------      --------     -------------------------------------------------------
RES         XMLType      A resource in Oracle XML repository 
ANY_PATH    VARCHAR2     A path that can be used to access the resource in the
                         repository 
RESID       RAW          Resource OID which is a unique handle to the resource

PATH_VIEW Definition and Structure

The PATH_VIEW contains one row for each unique path to access a resource in the repository. The following describes its structure:

Column      Datatype     Description 
------      --------     -----------------------------
PATH        VARCHAR2     Path name of a resource 
RES         XMLType      The resource referred by PATH 
LINK        XMLType      Link property 
RESID       RAW          Resource OID

See Also: Appendix H, " Oracle XML DB-Supplied XML Schemas and Additional Examples"

Figure 20-2 illustrates the structure of Resource and PATH_VIEWs.

Note: Each resource may have multiple paths called links.

The path in the RESOURCE_VIEW is an arbitrary one and one of the accessible paths that can be used to access that resource. Oracle XML DB provides operator UNDER_PATH that enables applications to search for resources contained (recursively) within a particular folder, get the resource depth, and so on. Each row in the PATH_VIEW and RESOURCE_VIEW columns is of XMLType. DML on Oracle XML DB repository views can be used to insert, rename, delete, and update resource properties and contents. Programmatic APIs must be used for some operations, such as creating links to existing resources.

Path names in the ANY_PATH column of the RESOURCE_VIEW and the PATH column in the PATH_VIEW are absolute paths.

Path names from the PATH operator are relative paths under the path name specified by the UNDER_PATH operator. Suppose there are two resources pointed to by path names '/a/b/c' and '/a/d' respectively, a PATH operator that retrieves paths under the folder '/a' will return relative paths 'b/c' and 'd'.

When there are multiple links to the same resource, only paths under the path name specified by the UNDER_PATH operator are returned. Suppose '/a/b/c', '/a/b/d' and '/a/e' are links to the same resource, a query on the PATH_VIEW that retrieves all the paths under '/a/b' return only '/a/b/c' and '/a/b/d', not '/a/e'.

Figure 20-2 RESOURCE_VIEW and PATH_VIEW Structure

Description of the illustration adxdb032.gif

Understanding the Difference Between RESOURCE_VIEW and PATH_VIEW

The major difference between the RESOURCE_VIEW and PATH_VIEW is:

• PATH_VIEW displays all the path names to a particular resource whereas RESOURCE_VIEW displays one of the possible path names to the resource

• PATH_VIEW also displays the properties of the link

Figure 20-3 illustrates the difference between RESOURCE_VIEW and PATH_VIEW.

Because many Internet applications only need one URL to access a resource, RESOURCE_VIEW is widely applicable.

PATH_VIEW contains the link properties as well as resource properties, whereas the RESOURCE_VIEW only contains resource properties.

The RESOURCE_VIEW benefit is generally optimization. If the database knows that only one path is needed, then the index does not have to do as much work to determine all the possible paths.

Note: When using the RESOURCE_VIEW, if you are specifying a path with the UNDER_PATH or EQUALS_PATH operators, then they will find the resource regardless of whether or not that path is the arbitrary one chosen to normally appear with that resource using RESOURCE_VIEW.

Figure 20-3 RESOURCE_VIEW and PATH_VIEW Explained

Description of the illustration adxdb018.gif

Resource_View and Path_View APIs

This section describes the RESOURCE_VIEW and PATH_VIEW operators.

UNDER_PATH

The UNDER_PATH operator uses the Oracle XML DB repository hierarchical index to return the paths under a particular path. The hierarchical index is designed to speed access walking down a path name (the normal usage).

If the other parts of the query predicate are very selective, however, then a functional implementation of UNDER_PATH can be chosen that walks back up the repository. This can be more efficient, because a much smaller number of links are required to be traversed. Figure 20-4 shows the UNDER_PATH syntax.

Figure 20-4 UNDER_PATH Syntax

Description of the illustration under_path_condition.gif

Table 20-1 describes the UNDER_PATH syntax.

EQUALS_PATH

The EQUALS_PATH operator is used to find the resource with the specified path name. It is functionally equivalent to UNDER_PATH with a depth restriction of zero.

EQUALS_PATH INTEGER EQUALS_PATH( resource_column,pathname);

where:

• resource_column is the column name or column alias of the 'resource' column in the path_view or resource_view.

• pathname is the path name to resolve.

Figure 20-5 illustrates the EQUALS_PATH syntax.

Figure 20-5 EQUALS_PATH Syntax

Description of the illustration equals_path_condition.gif

PATH

PATH is an ancillary operator that returns the relative path name of the resource under the specified pathname argument. Note that the path column in the RESOURCE_VIEW always contains the absolute path of the resource. The PATH syntax is:

PATH VARCHAR2 PATH( correlation);

where:

• correlation is an integer that can be used to correlate the UNDER_PATH operator (a primary operator) with ancillary operators (PATH and DEPTH).

  
  

  Note: If a path is not under the specified pathname argument, a NULL value is returned as the output of the current path.

  
  

Figure 20-6 illustrates the PATH syntax.

Figure 20-6 PATH Syntax

Description of the illustration path.gif

Here are some examples of a RESOURCE_VIEW that include resources specified by paths:

'/a/b/c'
'/a/b/c/d'
'/a/e/c'
'/a/e/c/d'

SELECT path(1) FROM resource_view 
   WHERE UNDER_PATH(res, '/a/b', 1) = 1;

PATH(1)
-------
c
c/d
2 rows returned

SELECT path(1) FROM resource_view 
   WHERE UNDER_PATH(res, '/a/b', 1)!=1

PATH(1)
-------


2 rows returned

SELECT ANY_PATH
   FROM resource_view 
   WHERE UNDER_PATH(res, '/a/b')=1;

ANY_PATH
--------
/a/e/c
/a/e/c/d
2 rows returned

SELECT ANY_PATH, path(1), path(2) 
   FROM resource_view
   WHERE UNDER_PATH(res, '/a/b', 1) = 1 or UNDER_PATH(res, '/a/e', 2) = 1;

ANY_PATH           PATH(1)                  PATH(2)
----------------------------------------------------
/a/b/c             c
/a/b/c/d           c/d
/a/e/c                                      c
/a/e/c/d                                    c/d
4 rows returned

DEPTH

DEPTH is an ancillary operator that returns the folder depth of the resource under the specified starting path.

DEPTH  INTEGER DEPTH( correlation);

where:

correlation is an integer that can be used to correlate the UNDER_PATH operator (a primary operator) with ancillary operators (PATH and DEPTH).

Using the Resource View and Path View API

The following RESOURCE_VIEW and PATH_VIEW examples use operators UNDER_PATH, EQUALS_PATH, PATH, and DEPTH.

SELECT any_path FROM resource_view WHERE any_path like '/sys%';

SELECT any_path, extract(res, '/Resource') FROM resource_view
   WHERE under_path(res, '/sys') = 1;

SELECT path(1) FROM resource_view
   WHERE under_path (res, 3, '/sys',1)=1;

SELECT path(1) PATH,depth(1) depth 
  FROM path_view 
  WHERE under_path(RES, 3,'/sys',1)=1;

SELECT path, extract(link, '/LINK/Name/text()').getstringval(), 
               extract(link, '/LINK/ParentName/text()').getstringval(), 
               extract(link, '/LINK/ChildName/text()').getstringval(), 
               extract(res, '/Resource/DisplayName/text()').getstringval() 
   FROM path_view
   WHERE path LIKE '/sys%';

SELECT path(1) FROM path_view 
   WHERE under_path(res, 3,'/sys', 1) > 0 ;

SELECT any_path FROM resource_view
   WHERE equals_path(res, '/sys') > 0;

select resid
from resource_view
where extract(res, '/Resource/Dispname') = 'example';

select any_path
from resource_view
where resid = :1;

select any_path from resource_view
where under_path(res, 1, '/parent_folder') = 1
and existsNode(res, '/Resource[@Container="true"]') = 1;

SELECT Extract(value(p), '/PurchaseOrder/Item').getClobval()
FROM MYPOs p, RESOURCE_VIEW
WHERE ExistsNode(value(p), 'PurchaseOrder[PONum=1001 and Company = "Oracle
 Corp"]') = 1 and any_path like '/public/po1%';

INSERT INTO resource_view VALUES(sys.xmltype.createxml('
  <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResource.xsd
http://xmlns.oracle.com/xdb/XDBResource.xsd">
  <Author>John Doe</Author>
  <DisplayName>example</DisplayName>
  <Comment>This resource was contrived for resource view demo</Comment>
  <Language>en</Language>
  <CharacterSet>ASCII</CharacterSet>
  <ContentType>text/plain</ContentType>
  </Resource>'), '/home/SCOTT/example', NULL);

INSERT INTO resource_view VALUES(sys.xmltype.createxml(' 
  <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResource.xsd http://xmlns.oracle.com/xdb/XDBResource.xsd"> 
  <Author>John Doe</Author> 
  <DisplayName>example</DisplayName> 
  <Comment>This resource was contrived for resource view demo</Comment> 
  <Language>en</Language> 
  <CharacterSet>ASCII</CharacterSet> 
  <ContentType>text/plain</ContentType> 
  </Resource>'), '/home/SCOTT/example', NULL);

DELETE FROM resource_view WHERE any_path = '/home/SCOTT/example';

DELETE FROM resource_view WHERE equals_path(res, '/home/file1')=1;

DELETE FROM path_view WHERE equals_path(res, '/home/file1')=1;

DELETE FROM resource_view WHERE under_path(res, '/public/test/doc.xml') = 1;

DELETE FROM
(SELECT 1 FROM resource_view
 WHERE UNDER_PATH(res, '/public', 1) = 1
 order by depth(1) desc);

UPDATE resource_view set res = sys.xmltype.createxml(' 
  <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResource.xsd http://xmlns.oracle.com/xdb/XDBResource.xsd"> 
  <Author>John Doe</Author> 
  <DisplayName>example</DisplayName> 
  <Comment>Has this got updated or not ? </Comment> 
  <Language>en</Language> 
  <CharacterSet>ASCII</CharacterSet> 
  <ContentType>text/plain</ContentType> 
  </Resource>') 
    WHERE any_path = '/home/SCOTT/example';

UPDATE path_view set path = '/home/XDB' 
  WHERE path = '/home/SCOTT/example';

UPDATE resource_view set res = sys.xmltype.createxml(' 
            <Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" 
          xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://xmlns.oracle.com/xdb/XDBResource.xsd 
          http://xmlns.oracle.com/xdb/XDBResource.xsd"> 
            <DisplayName>bearprob_new.txt</DisplayName>
            <Language>en</Language>
            <CharacterSet>utf-8</CharacterSet>
            <ContentType>text/plain</ContentType>
           <RefCount>1</RefCount>
         </Resource>') 
             WHERE any_path = '/bearprob.txt';

UPDATE RESOURCE_VIEW r SET r.res = updatexml(r.res,   '/Resource/DisplayName/text()', 'bear_prob_new.txt') WHERE
   any_path='/bear_prob.txt';

Working with Multiple Oracle XML DB Resources Simultaneously

Operations listed in Table 18-3 typically apply to only one resource at a time. To perform the same operation on multiple Oracle XML DB resources, or to find one or more Oracle XML DB resources that meet a certain set of criteria, use RESOURCE_VIEW and PATH_VIEW in SQL.

For example, you can perform the following operations with these resource_view and PATH_VIEW SQL clauses:

• Updating based on attributes

  UPDATE RESOURCE_VIEW
    SET resource = updateXML(res, '/Resource/Contents', lob)
      WHERE extractValue(resource, '/Resource/DisplayName') = 'My stuff';

• Finding recursively in a folder

  SELECT FROM RESOURCE_VIEW WHERE UNDER_PATH(resource, '/public') ...

• Copying a set of Oracle XML DB resources

  INSERT INTO PATH_VIEW
    SELECT '/newlocation' || path, res, link, NULL FROM PATH_VIEW
      WHERE UNDER_PATH(resource, '/public', 1) = 1
      ORDER BY path;

Performance Tuning of XML DB

XML DB uses the xdbconfig.xml file for configuring the system and protocol environment. It includes an element resource-view-cache-size parameter that defines the in-memory size of the RESOURCE_VIEW cache. The default value is 1048576.

Some queries on RESOURCE_VIEW and PATH_VIEW can be sped up by tuning resource-view-cache-size. In general, the bigger the cache size, the faster the query. The default resource-view-cache-size is appropriate for most cases. However, you may want to enlarge your resource-view-cache-size element when querying a sizable RESOURCE_VIEW.

The extensible optimizer decides whether the UNDER_PATH or the EQUALS_PATH operator is evaluated by domain index scan or functional implementation. The optimizer needs statistics for XML DB to achieve the optimal query plan. Statistics can be collected by analyzing the XML DB tables and hierarchical index under XDB using the ANALYZE command or the DBMS_STATS package. The following is an example of using the ANALYZE command:

analyze table xdb$h_link compute statistics;
analyze table xdb$resource compute statistics;
analyze index xdbhi_idx delete statistics;

Two new performance elements are included in 10g release 1 (10.1). The default limits for these elements are soft limits. The system automatically adapts when these limits are exceeded. These elements are:

• xdbcore-loadableunit-size - This element indicates the maximum size to which a loadable unit (partition) can grow in Kilobytes. When a partition is read into memory or a partition is built while consuming a new document, the partition is built until it reaches the maximum size. The default value is 16 Kb.

• xdbcore-xobmem-bound - This element indicates the maximum memory in kilobytes that a document is allowed to occupy. The default value is 1024 Kb. Once the document exceeds this number, some loadable units (partitions) are swapped out.

Searching for Resources Using Oracle Text

The XDB$RESOURCE table in Oracle XML DB user schema stores in Oracle XML DB the metadata and data corresponding to resources, such as files and folders. You can search for resources containing a specific keyword by using the CONTAINS operator in RESOURCE_VIEW or PATH_VIEW.

SELECT path
   FROM path_view
   WHERE contains(res, 'Oracle AND Unix') > 0;

SELECT any_path
   FROM resource_view
   WHERE contains(res, 'Oracle') > 0
   AND under_path(res, '/myDocuments') > 0;

• If Oracle XML DB contains only XML documents, that is, no binary data, a regular Context Index can be created on the XDB$RESOURCE table.

  
  

  See Also: Chapter 4, " XMLType Operations " and Chapter 9, " Full Text Search Over XML"

  
  

  CREATE INDEX xdb$resource_ctx_i
     ON xdb.xdb$resource x (value(x))
     INDEXTYPE IS ctxsys.context;

• If Oracle XML DB contains binary data, for example Microsoft Word documents, a user filter is required to filter such documents prior to indexing. It is recommended that you use the DBMS_XDBT package (dbmsxdbt.sql) to create and configure the Context Index.

  
  

  See Also: Oracle XML API Reference, the chapter on DBMS_XDBT for information on installing and using DBMS_XDBT. Appendix F, " SQL and PL/SQL APIs: Quick Reference ", DBMS_XDBT

  
  

  REM Install the package - connected as SYS
  @dbmsxdbt
  REM Create the preferences
  exec dbms_xdbt.createPreferences;
  REM Create the index
  exec dbms_xdbt.createIndex;

DBMS_XDBT package also includes procedures to sync and optimize the index. You can use the configureAutoSync() procedure to configure automatic sync of the index by using job queues.