Whats the best option to filter a list of values in vertica sql?

Answers

• paulosergiosantos Vertica Customer

  October 2019

  Just correct some mistakes:

  select * from vertica_table where id_field in (id1, id2, id3, ..., idn) ...

  0
• Nimmi_gupta - Select Field - Employee

  November 2019

  Other option would be WITH Clauses in SELECT.
  Example: Review the below example and you can get some idea how to write select using with query.
  WITH
  revenue AS (
  SELECT vendor_key, SUM(total_order_cost) AS total_revenue
  FROM store.store_orders_fact
  GROUP BY vendor_key ORDER BY 1)
  -- End defining WITH clause statement
  -- Begin main primary query
  SELECT vendor_name, vendor_address, vendor_city, total_revenue
  FROM vendor_dimension v, revenue r
  WHERE v.vendor_key = r.vendor_key AND total_revenue = (SELECT MAX(total_revenue) FROM revenue)
  ORDER BY vendor_name;
  ++ Vertica processes this query as follows:

  The WITH clause revenue evaluates its SELECT statement from table store.store_orders_fact.
  The results of the revenue clause are stored in a local temporary table.
  Whenever the revenue clause statement is referenced, the results stored in the table are used.
  The temporary table is dropped when query execution is complete.

  0
• marcothesane - Select Field - Administrator

  November 2019

  Try this:
  WITH srch(id) AS (
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
[...] -- this can continue as long as you need it...
SELECT 99999
)
SELECT b.*
FROM the_main_table
JOIN srch USING(id);

  0
• paulosergiosantos Vertica Customer

  November 2019

  Hello marchothesane and Nimmi_gupta .... thanks a lot for the attention...

  I've tried the UNION ALL with all the filter values, in that case my list values had a total of 200000 items.... and I've a got a error when I reached the 32768 item... I'm trying to figure out some configuration to increase this union all limit....

  I've tried to create some projection too, but the where clause ins't part of projection

  0
• marcothesane - Select Field - Administrator

  November 2019

  Well, with 200,000 items, an in-line common table expression won't do. There is a limit to the complexity of a SQL statement to compile.
  Do this:

  CREATE LOCAL TEMPORARY TABLE srch (
    id INT
  ) ON COMMIT PRESERVE ROWS;
  

  INSERT INTO srch (
  SELECT 1 UNION ALL
  SELECT 2 UNION ALL
  SELECT 3 UNION ALL
  [...]
  SELECT 20000;
  INSERT INTO srch (
  SELECT 20001 UNION ALL
  SELECT 20002 UNION ALL
  SELECT 20003 UNION ALL
  [...]
  SELECT 40000;
  -- 10 insert statements until you reach your 200,000 rows.

  Then

  SELECT b.*
  FROM the_main_table
  JOIN srch USING(id);
  

  0
• paulosergiosantos Vertica Customer

  November 2019

  Thank you marcothesane ...

  I have the same table in Oracle for testing, and with Oralce I've used the virtual table / column and got a good performance:
  SELECT COLUMN_VALUE FROM TABLE (SYS.ODCINUMBERLIST (...)) ...

  In Vertica I din't find the virtual table/column as in Oracle... but now I will try to create the temporary table as you suggested ...
  I think the temporary table is what I need ...
  Thank you very much again ...

  0