The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

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

The sql I'm developing, has a filter over a vertica table field...
I get a list of identifiers, and I have to use that list as a filter in a sql to query the values in a vertica table...
I'm passing that identifiers list as IN predicate...
For example:
select * from where in (id1, id2, id3, ..., idn) ...
The problem is that the id list, can be a big list and when the id list is big, the performance isn't good...
Is there some way to do this without IN predicate?

Thanks in a advance

Answers

  • Just correct some mistakes:

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

  • 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.

  • 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);

  • 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

  • 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);
    
  • 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 ...

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.