none
Filtering large Oracle table using smaller table RRS feed

  • Question

  • I am attempting to filter a table from an Oracle database (about 2 million rows) to only show the values that include an ID from my filter table, ID_TABLE ( about 4000 rows of unique ID codes). The ID in the Oracle table is the primary key.

    Most things I have tried ( putting the ID_TABLE in a csv, joining the two tables, putting them in a named range and filtering) always cause the final output to completely refresh the entire 2 million rows of the oracle table upon completion. I am trying to avoid this as it takes 20+ minutes and doing this manually in oracle takes ~15 sec.

    I even tried creating a function that joined the two tables 200 at a time to preserve query folding and it did not work any faster either.

    What I landed on was converting the ID_TABLE to a text string and inserting it into the Oracle query directly. This worked when I had an ID list of less than 1000 IDs but Oracle does not accept strings longer than 1000 so it is not viable for larger sets of IDs.

    Uploading the ID list to Oracle is also not an option as I do not have write access to the database.

    If anyone has had any experience joining tables from different sources in power query or has any idea on what might work in this situation please let me know. I feel as if I have almost tried everything.

    I am fairly new to Power Query as well and welcome all help of any kind. Any advice would be greatly appreciate.

    Thanks!!!

    Thursday, March 19, 2020 8:09 PM

Answers

  • If you want the IDs to be passed to Oracle behind-the-scenes, I'd usually recommend dividing the join up into chunks. However, it sounds like you tried this, and it didn't help. Maybe a chunk size of 200 is too small. You should be able to increase the chunk size to 1k or 2k without breaking folding. Please try that and see if the perf improves when you make the chunks larger.

    Ehren


    Monday, March 23, 2020 10:39 PM
    Owner