Home » SQL & PL/SQL » SQL & PL/SQL » Indexes (WinXP,Oracle10g)
Indexes [message #415737] Wed, 29 July 2009 01:17 Go to next message
alammas
Messages: 46
Registered: July 2008
Member
CREATE TABLE T
  (C1 NUMBER(15),C2 VARCHAR2(100)
  );
---loop to insert data
DECLARE
  I NUMBER:=1;
BEGIN
  LOOP
    INSERT INTO T VALUES
      (I,'HERE IS THE TEST FOR INDEX TIMING'
      );
    EXIT
  WHEN I > 100000;
    I   :=I+1;
  END LOOP;
END;     --INSERT TIME IS 17.66282082


SELECT * FROM T WHERE C2 LIKE '%T%';--BRINING PAGES INTO MAIN MEMORY 0.79051524
SELECT * FROM T WHERE C2 LIKE '%T%';--2ND TIME 0.05776433
SELECT * FROM T WHERE C2 LIKE '%T%';--3RD TIME 0.013183603 <<<
SELECT * FROM T WHERE C2 LIKE '%T%';--4TH TIME 0.15524571  <<<
SELECT * FROM T WHERE C2 LIKE '%T%';--4TH TIME 0.01455576  <<<

Why fetch time fluctuate each time.

--cREATING INDEXES
CREATE INDEX INDEXONC1 ON T
  (C1,C2
  );--6.02679539


--NOW REINSERT DATA INTO
DECLARE
  I NUMBER:=100001;
BEGIN
  LOOP
    INSERT INTO T VALUES
      (I,'HERE IS THE TEST FOR INDEX TIMING'
      );
    EXIT
  WHEN I > 200000;
    I   :=I+1;
  END LOOP;
END;--INSERT TIME IS [B]61.04084778[/B](17.66282082) <<<
----


After indexes insertion requires much time, i have a table with seven indexes and Approximately 5 Million records, insert a record takes much time how i can improve this
Re: Indexes [message #415739 is a reply to message #415737] Wed, 29 July 2009 01:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you write a book what would take longer:
A) Add a new page
B) Add a new page and keep an index on important tag-words
Re: Indexes [message #415752 is a reply to message #415737] Wed, 29 July 2009 02:24 Go to previous messageGo to next message
alammas
Messages: 46
Registered: July 2008
Member
Is not the case to post Please Frank
Quote:

After indexes insertion requires much time, i have a table with seven indexes and Approximately 5 Million records, insert a record takes much time how i can improve this

Re: Indexes [message #415753 is a reply to message #415752] Wed, 29 July 2009 02:41 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
After indexes insertion requires much time, i have a table with seven indexes and Approximately 5 Million records, insert a record takes much time how i can improve this



Why are you surprised that after index creation insert is taking more time?

However if this Data Warehouse type of environment and you are doing bulk load or bulk insert as a batch job then you may look at the option of disabling indexes during the load and rebuilding them after the load.

[Updated on: Wed, 29 July 2009 02:42]

Report message to a moderator

Re: Indexes [message #415760 is a reply to message #415752] Wed, 29 July 2009 03:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
alammas wrote on Wed, 29 July 2009 09:24
Is not the case to post Please Frank

I really have NO idea what you are saying here.
Re: Indexes [message #415807 is a reply to message #415737] Wed, 29 July 2009 06:35 Go to previous message
alammas
Messages: 46
Registered: July 2008
Member
Ohhh i think it was problem for me, table Tbl_ladger takes a huge time when inserting a single row ,it is most repitative table in that application indexes are to important be removed thanx
Previous Topic: how can we create procedure for all users to connect all user tables?
Next Topic: Questions on grouping.
Goto Forum:
  


Current Time: Sat Dec 03 18:07:59 CST 2016

Total time taken to generate the page: 0.23360 seconds