C.1 System table overview
System tables store cost information for base tables and indexes, as well as chunk information for multi-chunk tables.
HADB provides two types of system tables, base tables and viewed tables.
-
System table (base table)
In this manual, a system table that is a base table is called a system table (base table). The schema name of a system table (base table) is HADB.
-
System table (viewed table)
In this manual, a system table that is a viewed table is called a system table. The schema name of a system table is MASTER.
The following figure shows the relationship between a system table (base table) and a system table.
|
|
![[Figure]](GRAPHICS/F0000954.GIF)
- Explanation:
-
Cost information for base tables and indexes, and chunk information for multi-chunk tables, is stored in system tables (base tables). A viewed table that is created from a system table (base table) for a search purpose becomes a system table.
HADB users can search system tables. However, HADB users cannot search some system tables (base tables).
An HADB user can check information on items such as cost information collection date and time and chunk creation date and time by using the SELECT statement to search system tables. When searching system tables, specify MASTER as the schema name.
- Organization of this subsection
(1) List of system tables
The following table lists the system tables.
|
No. |
System table name |
Stored information |
Table ID of the system table (base table) that corresponds to the system table |
Number of subqueries included in the view definition# |
Existence of external reference |
|---|---|---|---|---|---|
|
1 |
Stores cost information related to base tables. Each row stores information for one base table. |
0x000200C9 |
1 |
Y |
|
|
2 |
Stores cost information related to columns. Each row stores information for one column. |
0x000200CA |
1 |
Y |
|
|
3 |
Stores cost information related to indexes. Each row stores information for one index. This table does not store cost information for range indexes. |
0x000200CB |
1 |
Y |
|
|
4 |
Stores chunk information related to multi-chunk tables. Each row stores information for one chunk. |
0x000200CC |
1 |
Y |
|
|
5 |
Stores information related to synonym dictionaries. Each row stores information for one dictionary. |
0x000200CD |
0 |
N |
- Legend:
-
Y: The system table's viewed table includes a subquery that performs an external reference. Therefore, you cannot specify a multiset value expression subquery in the corresponding system table.
N: The system table's viewed table does not include a subquery that performs an external reference.
- #
-
If you specified the corresponding system table in a SQL statement subquery, the number that must be added as the number of nests in that subquery is displayed.
(2) Times at which a system table is created
A system table is automatically created at the following times:
-
When the HADB server is started for the first time following the completion of database initialization
-
When the HADB server version is upgraded
(3) Scope of information in system tables that can be referenced by HADB users
The scope of information in system tables that can be referenced by HADB users varies according to the privileges each user has. The following table shows the system table information that an HADB user can reference.
Note that even for viewed tables specified as an underlying table of the system table, the information a user can reference varies according to the users' privileges. Therefore, even when searching the same viewed table, the HADB user who defined the viewed table and users granted access privileges for that viewed table might not be able to view the same information.
|
System table name |
Information being referenced |
Privilege of HADB user who references system table |
|||
|---|---|---|---|---|---|
|
DBA privilege |
Audit admin privilege |
Access privilege |
CONNECT privilege |
||
|
STATUS_TABLES |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C# |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
STATUS_COLUMNS |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C# |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
STATUS_INDEXES |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C# |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
STATUS_CHUNKS |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C# |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
|
STATUS_SYNONYM_DICTIONARIES |
Their own |
Y |
Y |
Y |
Y |
|
Other user |
Y |
N |
C# |
N |
|
|
MASTER |
-- |
-- |
-- |
-- |
|
- Legend:
-
Their own: Information related to a schema or schema object the HADB user owns.
Other user: Information related to a schema or schema object owned by another HADB user.
MASTER: Information related to dictionary tables and system tables.
Y: Can be referenced.
C: Can be referenced but conditions apply.
N: Cannot be referenced.
--: Not applicable.
- #
-
Users can reference information related to schema objects for which they have the access privilege, and the schema of those schema objects.