SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

ManualsBrandsHP ManualsServerHP NonStop G-Series

1001

Metadata Tables

HP NonStop SQL/MX Reference Manual—523725-004

10-74

Examples of Histogram Tables

•

Update the histogram statistics on the EMPNUM column for the EMPLOYEE table:

UPDATE STATISTICS FOR TABLE persnl.employee ON (empnum);

--- SQL operation complete.

•

Use the SELECT statement to retrieve the statistics in the SQL/MX HISTOGRAMS

table generated by the UPDATE STATISTICS statement, based on table and

column names:

SELECT O.OBJECT_NAME

TABLE_UID,

C.COLUMN_NAME,

H.HISTOGRAM_ID,

H.INTERVAL_COUNT,

H.ROWCOUNT,

H.TOTAL_UEC,

LOW_VALUE,

HIGH_VALUE

FROM cat.DEFINITION_SCHEMA_VERSION_1200.OBJECTS O,

cat.DEFINITION_SCHEMA_VERSION_1200.COLS C,

cat.sch.HISTOGRAMS H

WHERE O.OBJECT_UID = H.TABLE_UID

AND O.OBJECT_UID = C.OBJECT_UID

AND C.COLUMN_NUMBER = H.COLUMN_NUMBER;

•

Use the SELECT statement to retrieve the statistics in the SQL/MP HISTOGRM

table generated by the preceding UPDATE STATISTICS statement, based on table

and column names:

SELECT T.TABLENAME,

H.TABLE_UID,

C.COLNAME,

H.HISTOGRAM_ID,

H.INTERVAL_COUNT,

H.ROWCOUNT,

H.TOTAL_UEC,

H.LOW_VALUE,

H.HIGH_VALUE

FROM $data06.mycat.TABLES T,

$data06.mycat.COLUMNS C,

$data06.mycat.HISTOGRM H

WHERE T.CREATETIME = H.TABLE_UID

AND T.TABLENAME = C.TABLENAME

AND H.COLUMN_NUMBER = C.COLNUMBER;