25.6.16.25 The ndbinfo dict_obj_tree Table (original) (raw)
25.6.16.25 The ndbinfo dict_obj_tree Table
The dict_obj_tree
table provides a tree-based view of table information from thedict_obj_info table. This is intended primarily for use in testing, but can be useful in visualizing hierarchies of NDB
database objects.
The dict_obj_tree
table contains the following columns:
type
Type of DICT object; join on dict_obj_types to obtain the name of the object typeid
Object identifier; same as theid
column in dict_obj_info
For Disk Data undo log files and data files, this is the same as the value shown in theLOGFILE_GROUP_NUMBER
column of the Information Schema FILES table; for undo log files, it also the same as the value shown for thelog_id
column in the ndbinfologbuffers andlogspaces tablesname
The fully qualified name of the object; the same as thefq_name
column indict_obj_info
For a table, this is_`databasename`_/def/_`tablename`_
(the same as itsparentname
); for an index of any type, this takes the formNDB$INDEX_ _`indexid`__CUSTOM
parent_type
TheDICT
object type of this object's parent object; join ondict_obj_types to obtain the name of the object typeparent_id
Identifier for this object's parent object; the same as the dict_obj_info table'sid
columnparent_name
Fully qualified name of this object's parent object; the same as thedict_obj_info table'sfq_name
column
For a table, this has the form_`databasename`_/def/_`tablename`_
. For an index, the name issys/def/_`tableid`_/_`indexname`_
. For a primary key, it issys/def/_`tableid`_/PRIMARY
, and for a unique key it issys/def/_`tableid`_/_`ukname`_$unique
root_type
TheDICT
object type of the root object; join ondict_obj_types to obtain the name of the object typeroot_id
Identifier for the root object; the same as thedict_obj_info table'sid
columnroot_name
Fully qualified name of the root object; the same as thedict_obj_info table'sfq_name
columnlevel
Level of the object in the hierarchypath
Complete path to the object in the_NDB
_ object hierarchy; objects are separated by a right arrow (represented as->
), starting with the root object on the leftindented_name
Thename
prefixed with a right arrow (represented as->
) with a number of spaces preceding it that correspond to the object's depth in the hierarchy
The path
column is useful for obtaining a complete path to a given NDB
database object in a single line, whereas the indented_name
column can be used to obtain a tree-like layout of complete hierarchy information for a desired object.
Example: Assuming the existence of atest
database and no existing table namedt1
in this database, execute the following SQL statement:
CREATE TABLE test.t1 (
a INT PRIMARY KEY,
b INT,
UNIQUE KEY(b)
) ENGINE = NDB;
You can obtain the path to the table just created using the query shown here:
mysql> SELECT path FROM ndbinfo.dict_obj_tree
-> WHERE name LIKE 'test%t1';
+-------------+
| path |
+-------------+
| test/def/t1 |
+-------------+
1 row in set (0.14 sec)
You can see the paths to all dependent objects of this table using the path to the table as the root name in a query like this one:
mysql> SELECT path FROM ndbinfo.dict_obj_tree
-> WHERE root_name = 'test/def/t1';
+----------------------------------------------------------+
| path |
+----------------------------------------------------------+
| test/def/t1 |
| test/def/t1 -> sys/def/13/b |
| test/def/t1 -> sys/def/13/b -> NDB$INDEX_15_CUSTOM |
| test/def/t1 -> sys/def/13/b$unique |
| test/def/t1 -> sys/def/13/b$unique -> NDB$INDEX_16_UI |
| test/def/t1 -> sys/def/13/PRIMARY |
| test/def/t1 -> sys/def/13/PRIMARY -> NDB$INDEX_14_CUSTOM |
+----------------------------------------------------------+
7 rows in set (0.16 sec)
To obtain a hierarchical view of the t1
table with all its dependent objects, execute a query similar to this one which selects the indented name of each object havingtest/def/t1
as the name of its root object:
mysql> SELECT indented_name FROM ndbinfo.dict_obj_tree
-> WHERE root_name = 'test/def/t1';
+----------------------------+
| indented_name |
+----------------------------+
| test/def/t1 |
| -> sys/def/13/b |
| -> NDB$INDEX_15_CUSTOM |
| -> sys/def/13/b$unique |
| -> NDB$INDEX_16_UI |
| -> sys/def/13/PRIMARY |
| -> NDB$INDEX_14_CUSTOM |
+----------------------------+
7 rows in set (0.15 sec)
When working with Disk Data tables, note that, in this context, a tablespace or log file group is considered a root object. This means that you must know the name of any tablespace or log file group associated with a given table, or obtain this information from SHOW CREATE TABLE and then querying INFORMATION_SCHEMA.FILES, or similar means as shown here:
mysql> SHOW CREATE TABLE test.dt_1\G
*************************** 1. row ***************************
Table: dt_1
Create Table: CREATE TABLE `dt_1` (
`member_id` int unsigned NOT NULL AUTO_INCREMENT,
`last_name` varchar(50) NOT NULL,
`first_name` varchar(50) NOT NULL,
`dob` date NOT NULL,
`joined` date NOT NULL,
PRIMARY KEY (`member_id`),
KEY `last_name` (`last_name`,`first_name`)
) /*!50100 TABLESPACE `ts_1` STORAGE DISK */ ENGINE=ndbcluster DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> SELECT DISTINCT TABLESPACE_NAME, LOGFILE_GROUP_NAME
-> FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='ts_1';
+-----------------+--------------------+
| TABLESPACE_NAME | LOGFILE_GROUP_NAME |
+-----------------+--------------------+
| ts_1 | lg_1 |
+-----------------+--------------------+
1 row in set (0.00 sec)
Now you can obtain hierarchical information for the table, tablespace, and log file group like this:
mysql> SELECT indented_name FROM ndbinfo.dict_obj_tree
-> WHERE root_name = 'test/def/dt_1';
+----------------------------+
| indented_name |
+----------------------------+
| test/def/dt_1 |
| -> sys/def/23/last_name |
| -> NDB$INDEX_25_CUSTOM |
| -> sys/def/23/PRIMARY |
| -> NDB$INDEX_24_CUSTOM |
+----------------------------+
5 rows in set (0.15 sec)
mysql> SELECT indented_name FROM ndbinfo.dict_obj_tree
-> WHERE root_name = 'ts_1';
+-----------------+
| indented_name |
+-----------------+
| ts_1 |
| -> data_1.dat |
| -> data_2.dat |
+-----------------+
3 rows in set (0.17 sec)
mysql> SELECT indented_name FROM ndbinfo.dict_obj_tree
-> WHERE root_name LIKE 'lg_1';
+-----------------+
| indented_name |
+-----------------+
| lg_1 |
| -> undo_1.log |
| -> undo_2.log |
+-----------------+
3 rows in set (0.16 sec)
The dict_obj_tree
table was added in NDB 8.0.24.