Filter Using a Column | Native Object Store ( NOS ) | Teradata Vantage - 17.10 - Filtering Using a Column Within the Data Set - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Native Object Store Getting Started Guide

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

If a query filters on a column value, the database reads all the objects in the foreign table, transforms them, and examines the individual rows in order to apply the WHERE clause criteria.

Path filtering reduces the number of external objects read. You can view the query log, if it is enabled, to see the amount of external objects that are accessed. There is less data transferred with the path filtering query.

For details on the NOS-related DBQL fields, see the Orange Book, Native Object Store: Teradata Vantage™ Advanced SQL Engine, TDN0009800 and Teradata Vantage™ - Data Dictionary, B035-1092.

The examples and results show a sample river flow data set. To use your own data, replace the table and column names, and authorization object.

  1. To run NOS-related commands, log on to the database as a user with the required privileges.
  2. Use the WHERE clause to filter on site_no:
    SELECT TOP 2 columnX, columnY
    FROM table_name
    WHERE columnN = value;

    See Variable Substitutions for Examples for the credentials and location values for the sample river flow data set.

Example: Filtering Using a Column Within the Data Set

If not already done, create the foreign table. See Filtering Using a Path Filter in the CREATE FOREIGN TABLE Definition.

SELECT TOP 2 GageHeight, Flow
FROM riverflow_pathfilter
WHERE site_no = 09396100;

Your result will be similar to the following:

GageHeight  Flow
----------  -----
1.12        9.54
0.49        0