Checking Sort Order

If ROS data is not sorted correctly in the projection's order, query results that rely on sorted data will be incorrect. You can use the Index tool to check the ROS sort order if you suspect or detect incorrect query results. The Index tool evaluates each ROS row to determine whether it is sorted correctly. If the check locates a row that is not in order, it writes an error message to the log file with the row number and contents of the unsorted row.

Running the Tool

You can invoke the Index tool from the command line or from vsql, depending on whether the database is up or down:

Reviewing Errors

  1. Open the indextool.log file. For example:
    $ cd VMart/v_check_node0001_catalog
  2. Look for error messages that include an OID number and the string Sort Order Violation. For example:
    <INFO> ...on oid 45035996273723545: Sort Order Violation:
  3. Find detailed information about the sort order violation string by running grep on indextool.log. For example, the following command returns the line before each string (-B1), and the four lines that follow (-A4):

    [15:07:55][vertica-s1]: grep -B1 -A4 'Sort Order Violation:' /my_host/databases/check/v_check_node0001_catalog/indextool.log 
    2012-06-14 14:07:13.686 unknown:0x7fe1da7a1950 [EE] <INFO> An error occurred when running index tool thread on oid 45035996273723537: 
    Sort Order Violation: 
    Row Position: 624 
    Column Index: 0 
    Last Row: 2576000 
    This Row: 2575000 
    -- 
    2012-06-14 14:07:13.687 unknown:0x7fe1dafa2950 [EE] <INFO> An error occurred when running index tool thread on oid 45035996273723545: 
    Sort Order Violation: 
    Row Position: 3 
    Column Index: 0 
    Last Row: 4 
    This Row: 2 
    -- 
    
  4. Find the projection where a sort order violation occurred by querying the storage_containers system table. Use a storage_oid equal to the OID value listed in indextool.log. For example:
  5. => select * from storage_containers where storage_oid = 45035996273723545;