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

paulosergiosantos

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

paulosergiosantos

Just correct some mistakes:

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

Nimmi_gupta

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.

marcothesane

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

paulosergiosantos

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

marcothesane

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

paulosergiosantos

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