Forum: Other

Post: System tables?

System tables?
chbussler
Apr 12, 2010
Hi,

does VoltDB have the concept of a system table that I can query to find out the definitions of user defined tables (and e.g. the installed procedures, etc.)?

Thanks,
Christoph
re: System tables?
tcallaghan
Apr 12, 2010
Christoph,

We don't have anything as specific as you are requesting (you can get table names, but not column names and types). Our @Statistics system procedure provides the following (Check out Appendix D in "Using VoltDB" for more information.).

Syntax

VoltTable[] client.callProcedure("@Statistics", String component)

Description

The @Statistics system procedure returns information about the VoltDB database. The second argument,
component, specifies what aspect of VoltDB to return statistics about. The following are the allowable
values of component:

INITIATOR
- Returns information on the number of procedure invocations for each stored procedure
(including system procedures). The count of invocations is reported for each connection
to the database.

PROCEDURE
- Returns information on the usage of stored procedures for each site within the database
cluster. The information includes the name of the procedure, the number of invocations
(for each site), and selected performance information on minimum, maximum, and average
execution time.

TABLE
- Returns information about the database tables, including the number of rows per site for
each table. This information can be useful for seeing how well the rows are distributed
across the cluster for partitioned tables.

This will get you a list of defined stored procedures and tables.

-Tim
Parsing is alternative
chbussler
Apr 12, 2010
Christoph,

We don't have anything as specific as you are requesting (you can get table names, but not column names and types). Our @Statistics system procedure provides the following (Check out Appendix D in "Using VoltDB" for more information.)...
-Tim

Hi Tim,

thanks! I guess if an ad-hoc tool needs more details about the table definitions it could parse the ddl file. At the same time you have the parser already:-)

Not sure if the VoltDB SQL allows me to select only a random single row; in this case I could query the table and then ask for the column names and types. That would give me a bit more info, but of course less than what the ddl file gives me.

Thanks,
Christoph
re: System tables?
tcallaghan
Apr 13, 2010
Hi Tim,

thanks! I guess if an ad-hoc tool needs more details about the table definitions it could parse the ddl file. At the same time you have the parser already:-)

Not sure if the VoltDB SQL allows me to select only a random single row; in this case I could query the table and then ask for the column names and types. That would give me a bit more info, but of course less than what the ddl file gives me.

Thanks,
Christoph


Christoph,

If you ask for a SQL statement that has no rows you will get back a VoltTable with no rows. The trick is that ad-hoc is multi-partition and you need to make sure that the created SQL isn't planned poorly. Put a join to the primary key in the "where" if possible.

-Tim
Join to PK in WHERE
henning
Apr 16, 2010
Christoph,

If you ask for a SQL statement that has no rows you will get back a VoltTable with no rows. The trick is that ad-hoc is multi-partition and you need to make sure that the created SQL isn't planned poorly. Put a join to the primary key in the "where" if possible.

-Tim


"Put a join to the primary key in the "where" if possible."

Can you spell out, why? Thanks!

Henning
Hi Henning, I'll wait for Tim
ajgent
Apr 16, 2010
"Put a join to the primary key in the "where" if possible."

Can you spell out, why? Thanks!

Henning


Hi Henning,

I'll wait for Tim to return to respond, if that is OK. He could better explain what he intended by that suggestion.

--Andrew
re: Join to PK in WHERE
tcallaghan
Apr 16, 2010
"Put a join to the primary key in the "where" if possible."

Can you spell out, why? Thanks!

Henning


Henning,

Join to the PK so that you'll quickly retrieve between 0 and 1 rows.

-Tim