CODEWITHSUNDEEP
oracle-databaseindexingplsqlrecords
Chirila Vasile
Chirila Vasile

Reputation: 359

PL SQL Insert index

I need to insert into a table 500.000 records, so the process will take a while. I read about the indexes, that increase the speed of a query, but the question is next: on which parameter should I set the index in order to make the INSERT faster? (the id?). Thanks.

Edited

CREATE OR REPLACE PROCEDURE addNewUser(
                            firstName IN VARCHAR2,
                            lastName IN VARCHAR2,
                            email IN VARCHAR2,
                            username IN VARCHAR2,
                            password IN VARCHAR2,
                            job IN VARCHAR2) AS
  v_usersCount NUMBER := 0;
  v_userID NUMBER := 0;

BEGIN 
  SELECT COUNT(*) INTO v_usersCount
  FROM Users;

  v_userID := v_usersCount + 1;

  INSERT INTO Users VALUES(v_userID,firstName,lastName,email,username,password,job);
END addNewUser;

and the data is added like this : 

FOR i IN 1..50000
LOOP
    addNewTask('Task 1', 'Just Starter', 'High', 1, 'This is a description task.', 'Task Comment', '20-08-2015', 1);
END LOOP;

Upvotes: 0

Views: 1198

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

SELECT COUNT(*)... is one of slowest things you can do in any program. This requires reading every row in the table - so if you're inserting 1000 rows the first time it reads zero rows (fast), next time 1 row (fast), next time 2 rows...and eventually you've executed 1000 queries and read 499500 rows (sum of 0 through 999).

In this case you should use a SEQUENCE to generate non-repeating numbers. I'd rewrite your code as:

CREATE SEQUENCE VALUE_SEQ;  -- just take defaults for all parameters

CREATE OR REPLACE PROCEDURE addNewUser(
                            firstName IN VARCHAR2,
                            lastName IN VARCHAR2,
                            email IN VARCHAR2,
                            username IN VARCHAR2,
                            password IN VARCHAR2,
                            job IN VARCHAR2) AS
BEGIN 
  INSERT INTO Users
    VALUES(VALUE_SEQ.NEXTVAL, firstName,lastName,
           email,username,password,job);
END addNewUser;

Upvotes: 0

Tim3880
Tim3880

Reputation: 2583

You are doing a query for each insertion, which makes it slow.

Try calculate the ID outside your stored and pass it to the procedure, so you don't have to query it in your procedure every time.

Upvotes: 2

Related Questions