SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-13
Obtaining Statistics
•
Statistics on the columns of a table as follows:
>> VOLUME $VOL.SALES;
>> SELECT TABLENAME, COLNAME, UNIQUEENTRYCOUNT,
+> SECONDHIGHVALUE, SECONDLOWVALUE
+> FROM $VOL.SALES.COLUMN
+> WHERE TABLENAME LIKE "%$VOL.SALES.ORDERS%";
•
Statistics about file information of the table as follows:
>> VOLUME $VOL.SALES;
>> SELECT B.TABLENAME, F.EOF, F.NONEMPTYBLOCKCOUNT, B.ROWCOUNT
+> FROM $VOL.SALES.BASETABS B, $VOL.SALES.FILES F
+> WHERE B.TABLENAME = "\SYS.$VOL.SALES.ORDERS" AND
+> B.FILENAME = F.FILENAME;
This example obtains statistics on a table and writes the data on a log file to provide an
output listing:
>> LOG STATS CLEAR;
>> SELECT B.TABLENAME, B.ROWCOUNT, B.STATISTICSTIME,
+> I.INDEXNAME, I.INDEXLEVELS,
+> F.EOF, F.NONEMPTYBLOCKCOUNT
+> FROM $VOL.PERSNL.BASETABS B, $VOL.PERSNL.INDEXES I,
+> $VOL.PERSNL.FILES F
+> WHERE B.TABLENAME = "\PHOENIX.$VOL.PERSNL.EMPLOYEE" AND
+> B.TABLENAME = I.TABLENAME AND
+> B.TABLENAME = F.FILENAME;
TABLENAME ROWCOUNT STATISTICSTIME
----------------------------- --------------- ---------------
INDEXNAME INDEXLEVELS EOF
----------------------------- ----------- -----------
NONEMPTYBLOCKCOUNT
-------------------
\PHOENIX.$VOL.PERSNL.EMPLOYEE 57
211439149245389562
\PHOENIX.$VOL.PERSNL.EMPLOYEE 2 12288
2
\PHOENIX.$VOL.PERSNL.EMPLOYEE 57
211439149245389562
\PHOENIX.$VOL.PERSNL.XEMPDEPT 2 12288
2
\PHOENIX.$VOL.PERSNL.EMPLOYEE 57
211439149245389562
\PHOENIX.$VOL.PERSNL.XEMPNAME 2 12288
2
--- 3 row(s) selected.