| Bookmark Name | Actions |
|---|
Index Creation
This section provides the details for creating an index on an RDBMS stub file such as the syntaxes, left and right justification differences and index samples for the Oracle, DB2, and SQL servers.
Creating an index on an RDBMS stub file is similar to creating an index on a j4 file except the command to use is CREATE-EXTINDEX instead of CREATE-INDEX.
$ create-extindex CUSTOMER MNEMONIC
$ is the index generated on MNEMONIC. Like CREATE-VIEW, you can use the -v option for an on screen trace the happenings. Refer Appendix D for more examples.
Create-extindex Syntax and Options
Due to the nature of SQL Server’s indexing of XML, it may seem confusing. However, there are only two syntaxes—one for SQL and the other for Oracle and DB2.
Server Syntaxes
This section provides the details of the two types of indexes.
SQL
The syntax for SQL is as follows.
create-extindex {options} filename {index_method} {indexname}
In the above syntax, index_method can be any or all the following.
|
Value |
Description |
|---|---|
|
PRIMARY |
Indicates the primary method. This value is mandatory and will be created along with other index types, if not defined. |
|
PATH, PROPERTY and VALUE |
Indicates the additional index method of the xml tag/value. |
|
ALL |
Indicates all of the above methods. |
NOTE: There is no column argument, as SQL Server’s XML index methodology is to index all the elements in the XML document.
jsh -->create-extindex CUSTOMER
The following is the index generated on CUSTOMER.
jsh -->
Refer Appendix E for more examples.
Oracle and DB2
The syntax for Oracle and DB2 is as follows.
create-extindex {options} filename column {indexname}
In both servers, options can be of any combination (though -n and -a options are not relevant on SQL Server at this point).
The following table lists the verboses and their descriptions.
|
Verbose |
Description |
|---|---|
|
-v |
Verbose mode, display execution of index generation |
|
-d |
Debug mode, display debug info of index generation |
|
-n |
Apply numcast function (default for R-just) |
|
-a |
Apply asciicast function (currently under review and should not be used) |
|
-r |
Use regular extract index |
If you do not define the index name, it will be computed based on the table name, suffixed by c followed by the attribute number and prefixed with a literal to help describe the type of index. The following table lists a couple of examples for the same.
|
Syntax |
Description |
|---|---|
|
$ create-extindex CUSTOMER MNEMONIC |
Creates the ix_CUSTOMER_C1 (MNEMONIC) index |
|
$ create-extindex CUSTOMER ACCOUNT.OFFICER |
Creates the ix_CUSTOMER_C11 (ACCOUNT.OFFICER) index |
The ix prefix is just the naming convention chosen to identify this entity as an index. The n at the start indicates that it is a numeric index.
Left and Right Justification Differences
There are two types of indexes—Numeric and Alphanumeric, which translates to the following types of queries.
-
A query involving a right justified dictionary with an operand implying numeric comparison. For example, EQ 100 (i.e. the value on file could be 0100, 100.00, 100) LT/LE/GT/GE operations.
-
A query involving a left justified dictionary or a query involving wild card values. For example, BANK.
You need to ensure that the correct type of index is created to satisfy the query. A numeric type query requires a numeric index, that is, an index casting the value as NUMBER/INTEGER/DOUBLE and alphanumeric index requires a character based index, that is, VARCHAR.
When you issue a CREATE-EXTINDEX with no options (other than verbose/debug), the following occurs.
-
Driver is interrogated to know whether it supports NUMCAST
NOTE: NUMCAST is the general term, which means whether the driver can cast a value as a number without causing a run-time RDBMS error.
-
Dictionary/XMLREF-field-definition is parsed to determine if it is a right-justified or numeric field
Example
If you have an ACCOUNT.OFFICER dictionary and issue a CREATE-EXTINDEX command, it generates an equivalent.
ACCOUNT.OFFICER dictionary
001 D 002 11 003 004 ACCOUNT.OFFICER 005 4R 006 S
CREATE-EXTINDEX command
create-extindex CUSTOMER ACCOUNT.OFFICER
Equivalent
create-extindex -n CUSTOMER ACCOUNT.OFFICER
Similarly, CREATE-EXTINDEX on a left justified dictionary is the same as using the -r option.
You can specify -r or -n (or both, which will generate two indexes) to override the default operation. Refer Appendix D for examples.
NOTE: This section is not relevant to SQL Server due to its XML index methodology.
XSD Schema vs Short Tag
It makes little or no difference whether the underlying table is schema based or not, similar to view creation. Unlike views, however, the field specified in the CREATE-EXTINDEX command must exist in the dictionary of the filename, although XMLREF is still used to produce the resulting SQL command.
I-types specifying Positional Values (LOCAL.REF)
Although the jQL to SQL query translation supports I-type dictionaries, which refer to a multi-value/sub-value position, the support for indexes on these specific XML nodes may not be accepted. DB2, for example, does not support an index involving a predicate.
The following example, shows how an index is created on CUS.DEAL.SLIP for Oracle and DB2 databases.
Oracle
In this server, the following SQL will be created.
CREATE INDEX nix_CUSTOMER_c40_6 ON CUSTOMER x NVL(NUMCAST(extractValue(x.xmlrecord,'/row/c40[@m=6]')),0)
DB2
On DB2, the equivalent syntax with the m attribute will be as follows.
CREATE INDEX nix_CUSTOMER_c40_6 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c40[@m=6]' as sql DOUBLE
This, however, produces an error due to the predicate @m=6, which is illegal on DB2. The only possible solution is to index the c40 nodes as shown below.
CREATE INDEX nix_CUSTOMER_c40_6 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/c40/@m' as sql DOUBLE
This will produce a numeric index on all values with an element of c40 as well as an index on the m attribute (this will aid in getting to the specific values). When a query is performed on CUS.DEAL.SLIP, the @m=6 predicate is included and above index will be used. However, if you want to index other positional fields on c40 (for example, SEC.TYPE.SAFEKP) you will be indexing the same values though, possibly as VARCHAR, which could be required for certain types of queries.
For Schema based tables, the @m attribute is not used at present, but positional xpath expressions. In this case, the index creation is similar to the above case, but without the @m.
CREATE INDEX nix_CUSTOMER_c40_6 ON CUSTOMER(xmlrecord) generate key using xmlpattern '/row/gLocalRef/Localref' as sql DOUBLE
NOTE: This section is not relevant to SQL Server due to its XML index methodology.
Index Examples
This section provides the CREATE-EXTINDEX example for SQL server.
SQL
SQL Server does not offer indexing on individual elements in an XML document. Instead, it indexes all elements. This needs to be taken into account on a volatile file or table, as numerous updates will cause numerous index updates, which may outweigh the benefits of faster queries.
jsh -->create-extindex -v CUSTOMER Script name = XMLView_CUSTOMER.sql Script = IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER') CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD) / Now processing ============== IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER') CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD) Error: Maximum size of primary index of table 'CUSTOMER' is 200 bytes. CREATE XML INDEX requires that such size should be limited to 128 bytes Error generating index on CUSTOMER (MSSQL -1)
As shown in the above example, there is an error due to a limitation on the size of the key column used in the index, which is RECID in this example. The current default size of RECID is 200. To resolve this the table must be created with a KEY qualifier.
jsh -->CREATE-FILE DATA CUSTOMER TYPE=XMLMSSQL KEY=VARCHAR[128]
Now, the index can be created.
jsh -->create-extindex -v CUSTOMER Script name = XMLView_CUSTOMER.sql Script = IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER') CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD) / Now processing ============== IF NOT EXISTS (select distinct i.name from sys.indexes i where i.name = 'ix_CUSTOMER') CREATE PRIMARY XML INDEX ix_CUSTOMER ON CUSTOMER(XMLRECORD) Index on CUSTOMER generated
NOTE: Currently for SQL Server, it is not required to define the dictionary column.
Listing and Deleting Indices
This section provides details of the LIST-EXTINDEX and DELETE-EXTINDEX commands, syntax, and corresponding examples for Oracle, DB2, and SQL servers.
List-extindex
The syntax for LIST-EXTINDEX is as follows.
list-extindex {-v} filename
The -v (verbose) option is used mainly for debugging and not required by the end users. In there is no resulting output the -v option can be used to determine the problem, which may be an RDBMS error.
Delete-extindex
Due to the nature of SQL Server’s indexing of XML the syntax is slightly different to Oracle and DB2.
SQL
The syntax for SQL is as follows.
delete-extindex {options} filename {index_method(2)}
In the above syntax, index_method can be any or all the following.
|
Value |
Description |
|---|---|
|
PRIMARY |
Indicates the primary method. This value is mandatory and will be created along with other index types, if not defined. |
|
PATH, PROPERTY and VALUE |
Indicates the additional index method of the xml tag/value. |
|
ALL |
Indicates all of the above methods. |
NOTE: There is no column argument, as SQL Server’s XML index methodology is to index all the elements in the XML document.
Alternatively you can use the index name, which is typically ix_tablename optionally followed by one or more of the above options.
jsh -->delete-extindex QARUNSTATS_FILE * Indices on QARUNSTATS_FILE deleted jsh -->
Refer Appendix E for more examples.
Oracle and DB2
The syntax for Oracle and DB2 is as follows.
delete-extindex {options} filename column|indexname(s)
In both servers, options can be of any combination. The following table lists the verboses and their descriptions.
|
Verbose |
Description |
|---|---|
|
-v |
Verbose mode, display execution of the index drop |
|
-d |
Debug mode, display debug info of the index drop |
|
-n |
Apply numcast function (default for R-just) |
|
-a |
Apply asciicast function (currently under review and should not be used) |
|
-r |
Use regular index name |
If you do not define the index name, it will be computed based on the table name, suffixed by c followed by the attribute number and prefixed with a literal to help describe the type of index. The following table lists a couple of examples for the same.
|
Syntax |
Description |
|---|---|
|
$ delete-extindex CUSTOMER MNEMONIC |
Deletes the ix_CUSTOMER_C1 (MNEMONIC) index |
|
$ delete-extindex CUSTOMER ACCOUNT.OFFICER |
Deletes the ix_CUSTOMER_C11 (ACCOUNT.OFFICER) index |
The ix prefix is just the naming convention chosen to identify this entity as an index. The n at the start indicates that it is a numeric index.
Left and Right Justification Differences
When you issue a DELETE-EXTINDEX with no options (other than verbose/debug) the dictionary/XMLREF-field-definition is parsed to determine if it is a right-justified or numeric field. If it is right justified then a -n option is used.
Example
If you have an ACCOUNT.OFFICER dictionary and issue a DELETE-EXTINDEX command, it generates an equivalent.
ACCOUNT.OFFICER dictionary
001 D 002 11 003 004 ACCOUNT.OFFICER 005 4R 006 S
DELETE-EXTINDEX command
delete-extindex CUSTOMER ACCOUNT.OFFICER
Equivalent
delete -extindex -n CUSTOMER ACCOUNT.OFFICER
Similarly, DELETE-EXTINDEX on a left justified dictionary is the same as using the -r option.
You can specify -r or -n (or both, which will delete two indexes) to override the default operation. Refer Appendix D for examples.
You can also any do the following when deleting the indexes.
-
Define multiple dictionary names or index names
-
Mix index names and dictionary names
Example
The command is as follows.
delete-extindex QARUNSTATS_FILE ix_QARUNSTATS_FILE_C1 ATT3
The indices deleted are as follows.
ix_QARUNSTATS_FILE_C1, ix_QARUNSTATS_FILE_C3 (ATT3)
NOTE: This section is not relevant to SQL Server due to its XML index methodology.
Index Examples
This section provides the examples for Oracle, DB2 and SQL servers.
Oracle
The following are the index examples for Oracle.
List-extindex
The following example use QARUNSTATS_FILE.
External index list for QARUNSTATS_FILE (QARUNSTATS_FILE) 14:42:50 01 JUL 2007 Dict/Column Index Name Index Function ATT1 IX_QARUNSTATS_FILE_C1 SUBSTR(EXTRACTVALUE(X.XMLRECORD,'/row/c1'),1,50) ATT2 NIX_QARUNSTATS_FILE_C2 NUMCAST(EXTRACTVALUE(X.XMLRECORD,'/row/c2')) ATT3 IX_QARUNSTATS_FILE_Cl3 SUBSTR(EXTRACTVALUE(X.XMLRECORD,'/row/c3'),1,50)
The dict or column is the derived dictionary based on the V_QARUNSTATSFILE view. The Index Function column shows the relevant SQL expression used in the index. This determines whether a query will be using an index.
If a view is unavailable in the database the LIST-EXTINDEX still produces an index report, only with the dictionary column removed.
Index Name Index Function IX_QARUNSTATS_FILE_C1 SUBSTR(EXTRACTVALUE(X.XMLRECORD,'/row/c1'),1,50) NIX_QARUNSTATS_FILE_C2 NUMCAST(EXTRACTVALUE(X.XMLRECORD,'/row/c2')) IX_QARUNSTATS_FILE_C3 SUBSTR(EXTRACTVALUE(X.XMLRECORD,'/row/c3'),1,50)
delete-extindex -v CUSTOMER ACCOUNT.OFFICER
$ delete-extindex -v CUSTOMER ACCOUNT.OFFICER Script name = XMLView_CUSTOMER.sql Index nix_CUSTOMER_C11 (ACCOUNT.OFFICER) deleted Script = DROP INDEX nix_CUSTOMER_C11
delete-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER
$ delete-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER Script name = XMLView_CUSTOMER.sql Index ix_CUSTOMER_C11 deleted Script = DROP INDEX ix_CUSTOMER_C11 Script = DROP INDEX nix_CUSTOMER_C11
The following is the code when attempting the same delete function again.
$ delete-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER Script name = XMLView_CUSTOMER.sql Error deleting index on CUSTOMER (ORA 1418) Script = DROP INDEX ix_CUSTOMER_C11 Error - ORA-01418: specified index does not exist Script = DROP INDEX nix_CUSTOMER_C11 Error - ORA-01418: specified index does not exist
DB2
The following are the index examples for DB2.
List-extindex
The following example use QARUNSTATS_FILE.
External index list for QARUNSTATS_FILE (QARUNSTATS_FILE) 14:47:35 01 JUL 2007 Column Name Index Name Data Type XPath ATT1 IX_QARUNSTATS_FILE_C1 VARCHAR /row/c1 ATT2 NIX_QARUNSTATS_FILE_C2 DOUBLE /row/c2 ATT3 IX_QARUNSTATS_FILE_C3 VARCHAR /row/c3
The output shows the dictionary name followed by the index name, similar to Oracle. The next two columns show the SQL data type and XML path. In this way, a value of DOUBLE is similar to a NUMCAST in Oracle. Similarly, if there is no view the following codes are generated.
External index list for QARUNSTATS_FILE (QARUNSTATS_FILE) 14:55:15 01 JUL 2007 Index Name Data Type XPath IX_QARUNSTATS_FILE_C1 VARCHAR /row/c1 NIX_QARUNSTATS_FILE_C2 DOUBLE /row/c2 IX_QARUNSTATS_FILE_C3 VARCHAR /row/c3
delete-extindex -v CUSTOMER ACCOUNT.OFFICER
$ delete-extindex -v CUSTOMER ACCOUNT.OFFICER Script name = XMLView_CUSTOMER.sql Script = DROP INDEX nix_CUSTOMER_C11 / Now processing ============== DROP INDEX nix_CUSTOMER_C11 Index nix_CUSTOMER_C11 (ACCOUNT.OFFICER) deleted
You can see that the index on ACCOUNT.OFFICER has deleted a numeric index by default. This is because it is a right-justified dictionary.
delete-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER
$ delete-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER Script name = XMLView_CUSTOMER.sql Script = DROP INDEX ix_CUSTOMER_C11 / DROP INDEX nix_CUSTOMER_C11 / Now processing ============== DROP INDEX ix_CUSTOMER_C11 Now processing ============== DROP INDEX nix_CUSTOMER_C11 Indices for CUSTOMER deleted Attempting the same delete again: $ delete-extindex -v -r -n CUSTOMER ACCOUNT.OFFICER Script name = XMLView_CUSTOMER.sql Script = DROP INDEX ix_CUSTOMER_C11 / DROP INDEX nix_CUSTOMER_C11 / Now processing ============== DROP INDEX ix_CUSTOMER_C11 SQLSTATE = 42S12 Native Error Code = -204 Error message = [IBM][CLI Driver][DB2/NT] SQL0204N "T24.IX_CUSTOMER_C11" is an undefined name. SQLSTATE=42704 Now processing ============== DROP INDEX nix_CUSTOMER_C11 SQLSTATE = 42S12 Native Error Code = -204 Error message = [IBM][CLI Driver][DB2/NT] SQL0204N "T24.NIX_CUSTOMER_C11" is an undefined name. SQLSTATE=42704 Error deleting index on CUSTOMER (DB2 -204)
SQL
The following examples show some of the options you can apply to indexing in SQL Server.
List-extindex
The following example use QARUNSTATS_FILE.
External index list for QARUNSTATS_FILE (QARUNSTATS_FILE) 14:48:50 01 JUL 2007 Index Name Index Type ix_QARUNSTATS_FILE ix_QARUNSTATS_FILE_PATH PATH
The resulting output is the index name, repeated for the different types of XML indexing applied (PATH and VALUE). The first line with no index type is the primary (default) XML index.
delete-extindex -v CUSTOMER VALUE
Script name = XMLView_CUSTOMER.sql Script = DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER / Now processing ============== DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER Index VALUE deleted
delete-extindex -v CUSTOMER PATH
Script name = XMLView_CUSTOMER.sql Script = DROP INDEX ix_CUSTOMER_PATH ON CUSTOMER / Now processing ============== DROP INDEX ix_CUSTOMER_PATH ON CUSTOMER Index PATH deleted
delete-extindex -v CUSTOMER PATH VALUE
Script name = XMLView_CUSTOMER.sql Script = DROP INDEX ix_CUSTOMER_PATH ON CUSTOMER / DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER / Now processing ============== DROP INDEX ix_CUSTOMER_PATH ON CUSTOMER Now processing ============== DROP INDEX ix_CUSTOMER_VALUE ON CUSTOMER Indices for CUSTOMER deleted
delete-extindex -v CUSTOMER *
Script name = XMLView_CUSTOMER.sql Script = DROP INDEX ix_CUSTOMER ON CUSTOMER / Now processing ============== DROP INDEX ix_CUSTOMER ON CUSTOMER Indices for CUSTOMER deleted
NOTE: There are no errors or warnings on redundant DELETE-EXTINDEX operations on SQL Server.
Usage of Indices
In a regular relational table, an index is typically created on a column or expression that is heavily queried and has enough diversity. This index will be used if a query uses the same expression and optimiser on the database (based on statistics) and is considered the most efficient method of retrieving the results.
Example
The following script shows an index is created on a column.
sql> CREATE INDEX ix_MNEMONIC ON CUSTOMER (MNEMONIC);
In this example, MNEMONIC is the expression and is a column. However, if the script is changed as shown below, the expression is now CAST(MNEMONIC AS INT) and index – ix_MNEMONIC – is not used.
sql> SELECT COUNT(RECID) FROM CUSTOMER WHERE CAST(MNEMONIC AS INT) = 1;
For an XML column, in which you require to reference an element, there is no real column for reference, but an expression, which evaluates the value to be indexed.
The following example shows a valid XML expression to be indexed in Oracle assuming x is the table alias.
substr(extractValue(x.xmlrecord,'/row/c18'),1,11)
The following is the corresponding query for the same.
sql> SELECT RECID FROM CUSTOMER x WHERE substr(extractValue(x.xmlrecord,'/row/c18'),1,11) = '20070101';
However, if an index is created on CONTACT.DATE, which points to attribute 18 of the CUSTOMER record, the following query is used.
sql> CREATE INDEX nix_CUSTOMER_C18 ON CUSTOMER x (NVL(NUMCUST(extractValue(x.xmlrecord,'/row/c18')),0))
The above index would not be used by the previous query. If the query is changed to the following, the expression in the query is now matching the expression in the index and can be used if it satisfies the optimiser.
sql> SELECT RECID FROM CUSTOMER x WHERE NVL(NUMCAST(substr(extractValue(x.xmlrecord,'/row/c18'),1,11)),0)
= 20070101;
From a Temenos Transact perspective, using the default options on CREATE-EXTINDEX for a given dictionary will use an expression that will match when the same dictionary is used in a query, in most cases.
NOTE: By default, right-justified dictionaries will use some form of numeric casting and may not match the indices available depending on the database type and version.
Determination of Index Usage
The easy method to find if an index is used, is to turn on tracing for the driver, copy the SQL command from the log and use RDBMS tools to analyse the query (often referred as an explain). For Oracle and DB2, the database administrator (DBA) must first create the explain tables or procedures.
Let us examine the following query and how it is handled by the RDBMS query engine, which has an index created on ACCOUNT.OFFICER.
$ COUNT CUSTOMER WITH ACCOUNT.OFFICER = 123456
EXPLAIN PLAN on Oracle
The easiest method on Oracle is to turn on the autotrace with the explain option and run the command copied from XMLdriver.log.
The command for autotrace with the explain option is as follows.
SQL> set autotrace on explain
The following script shows the execution of the command copied from ORAdriver.log
SQL> SELECT COUNT(t.RECID) FROM CUSTOMER t WHERE NVL(NUMCAST(EXTRACTVALUE(t.XMLRECORD,'/row/c11')),0) = 123456;
COUNT(T.RECID)
--------------
0
Execution Plan
----------------------------------------------------------
Plan hash value: 2958507600
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 87 | | |
|* 2 | INDEX RANGE SCAN| NIX_CUSTOMER_C11 | 2 | 174 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL("T24"."NUMCAST"(EXTRACTVALUE(SYS_MAKEXML("SYS_NC00003$"),'/row/
c11')),0)=123456)
The above script shows that an INDEX RANGE SCAN has occurred on the NIX_CUSTOMER_C11 index. Additionally, the reason for the use of the index is also shown under Predicate Information (identified by operation id). SYS_MAKEXML("SYS_NC00003$") is t.XMLRECORD from the original query.
EXPLAIN PLAN on DB2
The easiest method on DB2 is to turn on the explain mode and run the query.
The command to turn on the explain mode is as follows.
$ db2 set current explain mode explain DB20000I The SQL command completed successfully.
The following script shows the execution of the query.
$ db2 SELECT COUNT(t.RECID) FROM CUSTOMER t WHERE xmlexists('$t/row/c11[.=123456]' passing t.XMLRECORD as "t")
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604
The above message shows that the command is not actually run but just parsed for the execution plan. Now, you need to run the db2exfmt DB2 utility.
$ db2exfmt
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
Enter Database Name ==> TINYT24
Connecting to the Database.
Connect to Database Successful.
Binding package - Bind was Successful
Using only explain schema found: T24 .
Enter up to 26 character Explain timestamp (Default -1) ==>
Enter up to 8 character source name (SOURCE_NAME, Default %%) ==>
Enter source schema (SOURCE_SCHEMA, Default %%) ==>
Enter section number (0 for all, Default 0) ==>
Enter outfile name. Default is to terminal ==>
DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.01.2
SOURCE_NAME: SQLC2F0A
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2007-04-26-18.03.08.406000
EXPLAIN_REQUESTER: T24
Database Context:
----------------
Parallelism: None
CPU Speed: 7.203250e-007
Comm Speed: 0
Buffer Pool size: 1000
Sort Heap size: 55
Database Heap size: 1288
Lock List size: 3862
Maximum Lock List: 98
Average Applications: 1
Locks Available: 321704
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
SELECT COUNT(t.RECID)
FROM CUSTOMER t
WHERE xmlexists('$t/row/c11[.=123456]' passing t.XMLRECORD as "t")
Optimized Statement:
-------------------
SELECT Q4.$C0
FROM
(SELECT COUNT(*)
FROM
(SELECT $RID$
FROM $INTERNAL_LET$ ((TABLE ($INTERNAL_XPATH$
('($INTERNAL_XMLTOXML_NIEO$(Q2.XMLRECORD))/row/(c11[(. =
123456)])(:-->$C0:)'))) AS Q1), T24.CUSTOMER AS Q2
WHERE fn:exists(Q1.$C0)) AS Q3) AS Q4
Access Plan:
-----------
Total Cost: 17.0943
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
GRPBY
( 2)
17.0936
2.25
|
0.621287
NLJOIN
( 3)
17.0933
2.25
/-+-\
1.25 0.49703
FETCH XSCAN
( 4) ( 8)
7.62998 7.57067
1 1
/----+---\
1.25 158
RIDSCN TABLE: T24
( 5) CUSTOMER
0.0591412
0
|
1.25
SORT
( 6)
0.0582141
0
|
1.25
XISCAN
( 7)
0.0558497
0
|
158
XMLIN: T24
NIX_CUSTOMER_C11
<more>
The above script shows that the execution plan is using the NIX_CUSTOMER_C11 index.
EXPLAIN PLAN on SQL
The Microsoft SQL Server Management Studio has a Display Execution Plan option, which produces a graphical display of how the query is handled. You need to use the trace option to generate a SQLdriver.log, copy and paste the translated SQL into a New Query window and click Display Execution Plan to generate the execution plan.
NOTE: You may need to prefix the query with use <database> on its own line to ensure the command is run against the appropriate database.
EXPLAIN PLAN from jBASE
Only the XMLORACLE driver supports the EXPLAIN PLAN from the jBASE environment. To turn on the explain option, you need to set the JEDI_XMLORACLE_EXPLAIN environment variable to 1.
$ export JEDI_XMLORACLE_EXPLAIN=1
$ COUNT CUSTOMER WITH ACCOUNT.OFFICER = 123456
jQLHandler explained: Plan hash value: 2958507600
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 87 | | |
|* 2 | INDEX RANGE SCAN| NIX_CUSTOMER_C11 | 2 | 174 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NVL("T24"."NUMCAST"(EXTRACTVALUE(SYS_MAKEXML("SYS_NC00003$"),'/row/
c11')),0)=123456)
No Records counted
If the trace option is turned on, the above information will be in ORAdriver.log. Else, it will display as a regular screen I/O.
Add Bookmark
save your best linksView Bookmarks
Visit your best links
BACK
Are you sure you want to log-off?