CODEWITHSUNDEEP
sqldatabaseplsqloracle10g
Shadow
Shadow

Reputation: 2478

Oracle insert lots of data to temporary table

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 (...)

Upvotes: 0

Views: 1135

Answers (2)

Shadow
Shadow

Reputation: 2478

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!

Upvotes: 0

JohnHC
JohnHC

Reputation: 11205

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')...)

Upvotes: 2

Related Questions