View, Table / Index Statistics Creation

This section provides details about the views, table/index statistics creation in RDBMS.

Views

The following command creates a view on an RDBMS stub file.

$  create-view CUSTOMER

This command does the following.

1. Interrogates the stub file to determine whether it is schema based (long tag) or non-schema based (short tag).

   • For schema-based types, it reads in the CUSTOMER@XMLREF item from the Schemas file

   • For short tag it selects the dictionary file and builds a list of extended dictionaries, including those which refer the multi-value (or even sub-value) of another dictionary (LOCAL.REF I-types)

2. Writes an SQL script to the current working directory

3. Invokes the driver to read the SQL script and execute it on RDBMS

The following example shows CUSTOMER as a short tag.

$  create-view CUSTOMER
V_CUSTOMER generated
$

You can use the -v option to get a visual display of the SQL commands. Refer Appendix B and Appendix C for examples.

Each view includes the RECID as the first column and, by default, the XMLRECORD column as well. You can omit the XMLRECORD column from the view by using the -x option.

$  create-view -x CUSTOMER
V_CUSTOMER generated
$

The CREATE-VIEW command supports the following options:

Table / Index Statistics

It is a common misconception that to speed up a query you simply create an index on one or more of the columns (or expressions) used in the query and the next time the query is run it will be faster.

When a query is issued, the SQL parser needs statistics on the table (and its indices) to help determine which method will be the most efficient in producing the desired results. Just because an index is created it doesn’t mean it will be used.

After creating an index on a table, it is important to update the statistics. Note that if you plan on creating more than one index on a table you should preferably update the statistics after creating the last index.

Along with the create-extindex command, a new jBASE utility create-extstats is provided. The syntax is as follows.

create-extstats {-v} {-pn{<s>|b}} filename {index_name|dictionary_name}

The options in the above command are as follows.

The following are examples for the various drivers.

SQL

The following script is for statistics creation for SQL.

c:\>create-extstats CUSTOMER
Index stats on CUSTOMER generated
c:\>

The verbose option (-v) shows the actual SQL used to achieve the following result.

c:\>create-extstats -v CUSTOMER
Script name = XMLView_CUSTOMER.sql
Script = UPDATE STATISTICS CUSTOMER
/
Now processing
==============
UPDATE STATISTICS CUSTOMER
Index stats on CUSTOMER generated
c:\>

The following script shows another example of using the percentage option.

c:\>create-extstats -v –p15 CUSTOMER
Script name = XMLView_CUSTOMER.sql
Script = UPDATE STATISTICS CUSTOMER WITH SAMPLE 15 PERCENT
/
Now processing
==============
UPDATE STATISTICS CUSTOMER WITH SAMPLE 15 PERCENT
Index stats on CUSTOMER generated
c:\>

In SQL server, you cannot target individual XML elements for indexing and therefore the following script is not required.

c:\>create-extstats CUSTOMER ACCOUNT.OFFICER
Error generating index stats on XMLCUSTOMER (MSSQL -1)
 use -v option for more information
c:\> 

Error Example

The following is an example to generate statistics on a non-existent index.

C:\>create-extstats -v -p155 CUSTOMER
Script name = XMLView_CUSTOMER.sql
Script = UPDATE STATISTICS CUSTOMER WITH SAMPLE 155 PERCENT
/
Now processing
==============
UPDATE STATISTICS CUSTOMER WITH SAMPLE 155 PERCENT
Error: Percent values must be between 0 and 100.
Error generating index stats on CUSTOMER (MSSQL -1)
C:\>

Sampling

Generating statistics for a large table can take considerable time and consume a lot of I/O. This can be avoided or reduced using the sampling option, which will generate statistics on a percentage of rows in the table.

c:\>create-extstats CUSTOMER –p15
Index stats on CUSTOMER generated
c:\>

For SQL, the sampling methods are ignored. The following is another example with the verbose option.

c:\>create-extstats -v -p15b CUSTOMER
Script name = XMLView_CUSTOMER.sql
Script = UPDATE STATISTICS CUSTOMER WITH SAMPLE 15 PERCENT
/
Now processing
==============
UPDATE STATISTICS CUSTOMER WITH SAMPLE 15 PERCENT
Index stats on CUSTOMER generated
c:\>

The b option is ignored on SQL. After creating statistics, it is recommended to use explain on a query you expect to utilise an index. Refer the SQL SERVER driver guide for more details.

Oracle

The following script is for statistics creation for Oracle.

$ create-extstats CUSTOMER
Index stats on CUSTOMER generated
$

The verbose option (-v) shows the actual SQL used to achieve the result.

$ create-extstats -v CUSTOMER
Script name = XMLView_CUSTOMER.sql
Index stats on CUSTOMER generated
Script = begin
dbms_stats.gather_table_stats('T24','CUSTOMER',ESTIMATE_PERCENT=>100);
end;
$

The T24 instance in the above script comes from the jedi_config user/schema definition. The following is another example using the percentage option:

$ create-extstats -v –p15 CUSTOMER
Script name = XMLView_CUSTOMER.sql
Index stats on CUSTOMER generated
Script = begin
dbms_stats.gather_table_stats('T24','CUSTOMER',ESTIMATE_PERCENT=>15);
end;
$

You can also update the statistics for a given index. This is helpful in the case, when statistics is updated on a table and then an additional index is created.

$ create-extstats CUSTOMER ACCOUNT.OFFICER
Index stats on CUSTOMER,nix_CUSTOMER_C11 generated
$

The dictionary (ACCOUNT.OFFICER) is used in place of the index name. You can also execute the following script.

$ create-extstats CUSTOMER nix_CUSTOMER_C11
Index stats on CUSTOMER,nix_CUSTOMER_C11 generated
$

Note that when creating an index, especially a right-justified field, there may be more than one index created. For example, you might have nix_CUSTOMER_C11 and ix_CUSTOMER_C11 for ACCOUNT.OFFICER. In this case, you must run CREATE-EXTSTATS for both indices or re-generate the statistics for the entire table.

Error Example

The following is an example to generate statistics on a non-existent index.

$ create-extstats -v CUSTOMER NATIONALITY
Script name = XMLView_CUSTOMER.sql
Error generating index stats on CUSTOMER (ORA 20000)
Script = begin
dbms_stats.gather_index_stats('T24','ix_CUSTOMER_C15',ESTIMATE_PERCENT=>100);
end;
Error - ORA-20000: Unable to analyze INDEX "T24"."IX_CUSTOMER_C15", insufficient privileges or does not exist
ORA-06512: at "SYS.DBMS_STATS", line 10596
ORA-06512: at "SYS.DBMS_STATS", line 10645
ORA-06512: at line 2
$

Sampling

Generating statistics for a large table can take considerable time and consume a lot of I/O. This can be avoided or reduced using the sampling option, which will generate statistics on a percentage of rows in the table.

$ create-extstats CUSTOMER –p15
Index stats on CUSTOMER generated
$

For Oracle, the sampling methods are ignored. The following is another example with the verbose option.

$ create-extstats -v -p15b CUSTOMER
Script name = XMLView_XMLCUSTOMER.sql
Index stats on XMLCUSTOMER generated
Script = begin
dbms_stats.gather_table_stats('T24','XMLCUSTOMER',ESTIMATE_PERCENT=>15);
end;
$

The b option is ignored on Oracle. After creating statistics, it is recommended to use explain on a query you expect to utilise an index. Refer the Oracle Server driver guide for more details.

DB2

The following script is for statistics creation for DB2.

$ create-extstats CUSTOMER
Index stats on CUSTOMER generated
$

Although there is a verbose option (-v) there is not a lot of additional detail to be displayed as the DB2 method of updating statistics is an internal API. However, CREATE-EXTSTATS is also used by the Oracle and SQL server drivers, which complement the verbose option by displaying additional information.

$ create-extstats -v -p15 CUSTOMER
Sampling %15 of rows using the System method
Index stats on CUSTOMER generated

You can also update the statistics for a given index. This is helpful in the case, when statistics is updated on a table and then an additional index is created.

$ create-extstats CUSTOMER ACCOUNT.OFFICER
Index stats on CUSTOMER,nix_CUSTOMER_C11 generated

The dictionary (ACCOUNT.OFFICER) is used in place of the index name. You can also execute the following script.

$ create-extstats CUSTOMER nix_CUSTOMER_C11
Index stats on CUSTOMER,nix_CUSTOMER_C11 generated

Note that when creating an index, especially a right-justified field, there may be more than one index created. For example, you might have nix_CUSTOMER_C11 and ix_CUSTOMER_C11 for ACCOUNT.OFFICER. In this case, you must run CREATE-EXTSTATS for both indices or re-generate the statistics for the entire table.

Error Example

The following is an example to generate statistics on a non-existent index.

$ create-extstats -v CUSTOMER NATIONALITY
SQL2306N  The table or index "T24.IX_CUSTOMER_C15" does not exist.
Error generating index stats on CUSTOMER

Sampling

Generating statistics for a large table can take considerable time and consume a lot of I/O. This can be avoided or reduced using the sampling option, which will generate statistics on a percentage of rows in the table.

$ create-extstats CUSTOMER –p15
Index stats on CUSTOMER generated

DB2 supports two sampling methods—SYSTEM (default) and BERNOULLI. The following is another example with the verbose option.

Below is another example with the verbose option:

$ create-extstats -v -p15b CUSTOMER
Sampling %15 of rows using the Bernoulli method
Index stats on CUSTOMER generated

After creating statistics, it is recommended to use explain on a query you expect to utilise an index. Refer the DB2 Server driver guide for more details.