Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

use of SQL /*+MATERIALIZE*/ with In Database Tools

Number4
8 - Asteroid

Why does the In Database 'Connect In-DB' tool not allow the use of 'Materialize' in a sql statement while the regular 'Input Data' tool will?

 

With Cust as
(select /*+MATERIALIZE*/
sto_cust_seq, etc..etc..

 

That is to say, I get a 'DataWrapOCI: unable to prepare the query' error at the 'Data Stream Out' tool.

3 REPLIES 3
CharlieS
17 - Castor
17 - Castor

It's my understanding that the In-DB tools will write temporary tables as a part of their normal operation and that specifying this command isn't necessary. 

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Multiple-Queries-In-Database/td-p/3424... 

Number4
8 - Asteroid

Thanks for pointing me to that.  It makes sense.

 

Maybe it's my companies servers, but I've found using multiple In-Database 'Connect In-DB' inputs and joining them in-database is slower than using the Data Stream Out and using a regular join.

 

While the In-Database 'Connect In-DB' is faster with most of our tables, on some of them using the Input Data tool with 'Materialize' is significantly faster.

 

 

Thank you for your answer.

CharlieS
17 - Castor
17 - Castor

Happy to help.

 

In your case, it sounds like maybe your local machine has some rockin' resources compared to your server, or there are some parallelism settings on the server doing their thing. 

 

Happy Alteryx-ing!

Labels