Oracle insert lots of data to temporary table

Question

I need to insert 12000 strings into temporary table, 6 characters each. Currently I am doing it with SELECT Code FROM Articles where Code = '111111' OR Code = '222222' ... command that has 400 000 characters and takes 20 seconds to execute. I wonder how can I speed up this process?

I do not need any validation on my codes. They need to be transferred from application to database as part of the query or as command parameter.

I do not really need to do Select Code From Articles, but oracle does not support multiple records in INSERT INTO (...) VALUES (...)

Where is the list of codes you're using in the query (111111 etc.) coming from? Another table, a file, user input, ...? — Alex Poole, Mar 27 '17 at 13:54
Good point, added it to question. I think that command parameter is reasonable way. — Shadow, Mar 27 '17 at 14:33
How are they held in the application then, and what language/connection type are you using? You may be able to pass an array of values and have it treated as a collection. — Alex Poole, Mar 27 '17 at 14:35
I take those values from mysql database. There is stored procedure that does `SELECT Code FROM Sometable`. My application takes all these codes and tries to pass them to oracle temporary table. In backend I am using C# with devart MySql driver. — Shadow, Mar 27 '17 at 14:36
[Maybe something like this](http://stackoverflow.com/a/6155801/266304), but with your Oracle type created as strings not numbers. You then don't have to construct the statement based on how many values there are, you just pass a single array bind variable. — Alex Poole, Mar 27 '17 at 14:41

score 2 · Answer 1 · edited May 23 '17 at 11:46

2

IN is generally faster than OR as it stops evaluating as soon as the condition is met. See previous q here

So:

Select code
from Articles
where Code in ('111111','222222')

To allow for the extremely large list, tuples:

Select code
from Articles
where ('1', Code) in (('1','111111'),
                      ('1','222222')...)

edited May 23 '17 at 11:46

Community

1
1

answered Mar 27 '17 at 14:00

JohnHC

10,935
1
24
40

It would have to have multiple `IN` clauses `OR`d together, of course, if there are 12000 values to look for, – Alex Poole Mar 27 '17 at 14:14

score 0 · Accepted Answer · answered Apr 20 '17 at 09:39

As @AlexPoole pointed out it is best to use table valued parameter.

type t_varchar_tab is table of varchar2(10) index by pls_integer;
procedure insert_table(i_id_tab in t_varchar_tab);

body:

  procedure insert_table(i_id_tab in t_varchar_tab) is
  begin
  -- if You have temporary table You do not want commits here
  forall i in i_id_tab.first .. i_id_tab.last
      insert into MY_SCHEMA.MY_TABLE
      VALUES (i_id_tab(i));
  end ins_test;

C#:

        using (OracleCommand dbCommand = connection.CreateCommand())
        {
            dbCommand.CommandText = "MY_SCHEMA.MY_PACKAGE.insert_table";
            dbCommand.CommandType = CommandType.StoredProcedure;

            var inputArray = new OracleParameter
            {
                Direction = ParameterDirection.Input,
                CollectionType = OracleCollectionType.PLSQLAssociativeArray,
                Value = StringList.ToArray()
            };
            dbCommand.Parameters.Add(inputArray);
            await dbCommand.ExecuteNonQueryAsync();

Thanks Alex!

Oracle insert lots of data to temporary table

2 Answers2