title: inserting pandas dataframe in oracle extremely slow
body: <p>I am working on <code>pandas</code> dataframes. The goal of my application is to perform certain analysis on a csv file, and once that is done, insert this csv file into an <code>oracle</code> database.</p>
  
  <p>To insert into an oracle database, I used <code>to_sql</code> command of <code>pandas</code> library. But to insert 300,000 rows, my code took around 2 hours and ten minutes. However when I used <code>MySQL</code> database for the same analysis and same size of input data, it took only 90 seconds. </p>
  
  <p>I am doing all my operations inside a lubuntu VM. For reference, you can find relevant code below.</p>
  
  <pre><code>data_frame.to_sql(name='RSA_DATA', con=get_engine(), if_exists='append',
                             index=False, chunksize=config.CHUNK_SIZE)
  input_data_list = get_rsa_object_list(data_frame)
  </code></pre>
  
  <p>I used same <code>CHUNK_SIZE</code> for both the cases (which was set to 500). </p>
  
  <p>Can someone suggest what could be the reason for this and how do I get around this ? I cannot change my database system from <code>oracle</code> to any other database. I used <code>MySQL</code> just to be sure that the problem doesn't lie with my code.</p>
  
  <p>Thanks in advance.</p>
  
  <p>Following is the <code>CREATE TABLE</code> command for <code>Oracle</code> database:</p>
  
  <pre><code>  CREATE TABLE "CRISDEV"."RSA_DATA" 
     (    "GENE_SYMBOL" VARCHAR2(50 BYTE), 
      "GENEID" NUMBER, 
      "CRISPR" VARCHAR2(75 BYTE), 
      "RSASCORE" NUMBER, 
      "LOGP" NUMBER, 
      "BEST_ACTIVITY" NUMBER, 
      "RSA_HIT" NUMBER, 
      "HIT_PER_GENE" NUMBER, 
      "TOTAL_CRISPRS" NUMBER, 
      "RSA_RANK" NUMBER, 
      "SAMPLE_NAME" VARCHAR2(10 BYTE), 
      "FOLD_CHANGE" NUMBER, 
      "EXPERIMENT_NUM" NUMBER, 
      "CELL_LINE" VARCHAR2(75 BYTE), 
      "CRISPR_LIB" VARCHAR2(75 BYTE), 
      "CRISPR_LOT" VARCHAR2(25 BYTE), 
      "CAS9_VENDOR" VARCHAR2(50 BYTE), 
      "CAS9_LOT" VARCHAR2(25 BYTE), 
      "CRISPR_INFECT_DATE" DATE, 
      "CAS9_INFECT_DATE" DATE, 
      "CAS9_MOI" NUMBER, 
      "FTE" VARCHAR2(50 BYTE), 
      "SAMPLE_NAME_LONG" VARCHAR2(150 BYTE), 
      "INDICATION" VARCHAR2(100 BYTE), 
      "LOW_CRISPR_COUNT" NUMBER DEFAULT 0, 
      "MODIFICATION" VARCHAR2(20 BYTE), 
      "TIMEPOINT" VARCHAR2(10 BYTE)
     ) SEGMENT CREATION IMMEDIATE 
    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
   NOCOMPRESS LOGGING
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "CRISDEV_DATA" ;
  
    CREATE INDEX "CRISDEV"."C20150312_RSA_CELLLINE" ON "CRISDEV"."RSA_DATA" ("CELL_LINE") 
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "CRISDEV_IDX" ;
  
    CREATE INDEX "CRISDEV"."C20150312_RSA_CRISPRLIB" ON "CRISDEV"."RSA_DATA" ("CRISPR_LIB") 
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "CRISDEV_IDX" ;
  
    CREATE INDEX "CRISDEV"."C20150312_RSA_GENEID" ON "CRISDEV"."RSA_DATA" ("GENEID") 
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "CRISDEV_IDX" ;
  
    CREATE INDEX "CRISDEV"."C20150312_RSA_GENESYMBOL" ON "CRISDEV"."RSA_DATA" ("GENE_SYMBOL") 
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "CRISDEV_IDX" ;
  
    CREATE INDEX "CRISDEV"."C20150312_RSA_SAMPLELONG" ON "CRISDEV"."RSA_DATA" ("SAMPLE_NAME_LONG") 
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "CRISDEV_IDX" ;
  
    CREATE INDEX "CRISDEV"."C20150312_RSA_SAMPLENAME" ON "CRISDEV"."RSA_DATA" ("SAMPLE_NAME") 
    PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    TABLESPACE "CRISDEV_IDX" ;
  </code></pre>
  
  <p><code>CREATE TABLE</code> command for <code>MySQL</code> is as follows:</p>
  
  <pre><code>CREATE TABLE `RSA_DATA` (
    `GENE_SYMBOL` varchar(50) DEFAULT NULL,
    `GENEID` int(11) DEFAULT NULL,
    `CRISPR` varchar(75) DEFAULT NULL,
    `RSASCORE` float DEFAULT NULL,
    `LOGP` float DEFAULT NULL,
    `BEST_ACTIVITY` float DEFAULT NULL,
    `RSA_HIT` int(11) DEFAULT NULL,
    `HIT_PER_GENE` int(11) DEFAULT NULL,
    `TOTAL_CRISPRS` int(11) DEFAULT NULL,
    `RSA_RANK` int(11) DEFAULT NULL,
    `SAMPLE_NAME` varchar(10) DEFAULT NULL,
    `FOLD_CHANGE` int(11) DEFAULT NULL,
    `EXPERIMENT_NUM` int(11) DEFAULT NULL,
    `CELL_LINE` varchar(75) DEFAULT NULL,
    `CRISPR_LIB` varchar(75) DEFAULT NULL,
    `CRISPR_LOT` varchar(25) DEFAULT NULL,
    `CAS9_VENDOR` varchar(50) DEFAULT NULL,
    `CAS9_LOT` varchar(25) DEFAULT NULL,
    `CRISPR_INFECT_DATE` date DEFAULT NULL,
    `CAS9_INFECT_DATE` date DEFAULT NULL,
    `CAS9_MOI` int(11) DEFAULT NULL,
    `FTE` varchar(50) DEFAULT NULL,
    `SAMPLE_NAME_LONG` varchar(150) DEFAULT NULL,
    `INDICATION` varchar(100) DEFAULT NULL,
    `LOW_CRISPR_COUNT` int(11) DEFAULT NULL,
    `MODIFICATION` varchar(20) DEFAULT NULL,
    `TIMEPOINT` varchar(10) DEFAULT NULL
  );
  </code></pre>