locked
oracle performance problem RRS feed

  • Question

  • User-804681621 posted

    i have a id list in text file. let say id 

    111,222,333 in the text file

    how can i find out the id which is not in the table_a from the list in text file? (not using temp table).

    below is my script but it seems very poor performance and wait for a long time, it can't be stop.

    select id from (select id from table_a union

    select 111 from dual union

    select 222 from dual union

    select 333 from dual) alldata where id not in (select id from table_a);

    any reference code that can be improve the above script so that i can select the required id in oracle. (unix platform)

    if the script is executing and wait for a long long time it still can't be stop, what should i do? is it any process locked? or dead lock already?

    i don't know the problem is from the script or just DB problem?

    Tuesday, June 28, 2016 2:58 PM

All replies

  • User-804681621 posted

    i have a id list in text file. let say id 

    111,222,333 in the text file

    how can i find out the id which is not in the table_a from the list in text file? (not using temp table).

    below is my script but it seems very poor performance and wait for a long time, it can't be stop.

    select id from (select id from table_a union

    select 111 from dual union

    select 222 from dual union

    select 333 from dual) alldata where id not in (select id from table_a);

    any reference code that can be improve the above script so that i can select the required id in oracle. (unix platform)

    if the script is executing and wait for a long long time it still can't be stop, what should i do? is it any process locked? or dead lock already?

    i don't know the problem is from the script or just DB problem?

    anyone knows?

    Tuesday, June 28, 2016 3:59 PM
  • User269602965 posted

    Step 1 Put the flat file into an ORacle Directory object and load the text file into an Oracle TABLE using the Oracle External Table function,

    then all your IDs will be converted from horizontal CSV values into a single column table you might name ID.

    Think of it as a virtual table referencing the CSV flat file as the source without creating a physical oracle table.

    Then you can eliminate all the hard coded UNIONs in your code, and then use traditional Oracle table VEN diagram procedures (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, INTERSECTION, MINUS) for much improved performance.

    Wednesday, June 29, 2016 12:57 AM