Determining the sizes of Oracle database tables and indexes

For one of our projects we store large amounts of timeseries data in an Oracle database. Sometimes we want to get an overview of how big the tables and related indexes are. Some database client tools like Toad for Oracle can show this information directly in their user interface, but if you use other tools like the SQuirreL SQL Client or JetBrains DataGrip you have to gather this information yourself via SQL queries.

DBA_SEGMENTS and DBA_INDEXES

For Oracle databases this meta information is available via the DBA_SEGMENTS and DBA_INDEXES tables. To query the sizes of several tables in MB use the following query:

SELECT segment_name, segment_type, bytes/1024/1024 MB
  FROM dba_segments
  WHERE segment_type = 'TABLE'
    AND segment_name IN ('TABLE_NAME_1', 'TABLE_NAME_2');

This SQL query returns the sizes of TABLE_NAME_1 and TABLE_NAME_2.

If you want to see the sizes of all the indexes that are associated with a table or a set of tables you can use the following query:

SELECT idx.table_name, idx.index_name, SUM(bytes)/1024/1024 MB
  FROM dba_segments seg,
       dba_indexes idx
  WHERE idx.table_owner = 'SCHEMA_NAME'
    AND idx.table_name IN ('TABLE_NAME_1', 'TABLE_NAME_2')
    AND idx.owner       = seg.owner
    AND idx.index_name  = seg.segment_name
  GROUP BY idx.index_name, idx.table_name;

Of course, you have to replace SCHEMA_NAME, and TABLE_NAME_x with the names in your database.

Unfortunately, access to this kind of meta information is different for each database system, and the queries above only work for Oracle databases.

Displaying numbers in tables

Many software applications have to display series of numbers, for example statistical information, measurement values or financial data. Of course there are many ways to visualize values graphically with charts, but sometimes the user wants to see the actual values as numbers. The typical layout method to display numbers are tables.

Here are some guidelines you should follow when you have to display numbers in a table.

Integer numbers

Right aligned integer numbers

Right-aligned integer numbers

Integer numbers that are shown in a table column should be right-aligned, because the orders of magnitude of a number’s digits increase from right to left. Additionally you should choose a font with fixed-width digits for numbers. This ensures that digits with the same orders of magnitude line up. Thus the numbers can be compared more easily. The font itself doesn’t have to be a fixed-width font in general. Some proportional fonts with variable widths for letters have fixed-widths for digits, called tabular figures.

Non-integer numbers

Aligned with decimal points

Aligned with decimal points

Non-integer numbers with decimal points should be aligned with their decimal points. The reason is the same as above: digits with the same orders of magnitude should line up. This can be a bit more effort to implement in your application than mere right-alignment, because components such as UI widgets or HTML tables usually don’t directly support this form of alignment.

However, you can implement it by using a font with tabular figures and then right-pad the numbers with spaces. Each of these spaces must have the same width as a digit, of course. This is the case with a fixed-width font, but there is also a special Unicode character for this purpose that can be used with proportional fonts and tabular figures: it’s called figure space and has the Unicode code point U+2007.