Does anyone know why during left join in roc SQL T1 (medium size table around 80k rows) and T2 (very large table with 3bln rows) Sas doesn't use index? But during inner join proc SQL it uses index and performance is great. I've used MSGLEVEL=I option to see what's happening in log.
You can also add the undocumented PROC SQL options _method and _tree to get more details on how PROC SQL is evaluating the query.
The optimal would a hash join, which requires that your left table fits into memory and uses index on the right table for the join criteria. You can affect the optimizer to chose this by adjusting the BUFFERSIZE option, but you cannnot force it.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.