SQL Query Tuning

From: <vinitv_at_my-dejanews.com>
Date: Wed, 17 Jun 1998 18:41:22 GMT
Message-ID: <6m92kj$4pd$1@nnrp1.dejanews.com>

Hi folks !

I am a newbie to Oracle and am currently facing a daunting task of tuning the existing application for faster performance.

I have two tables, viz., table1 which has 1.4 million records and table2 which has 100 records. Table1 has a duplicate index on col1, col2, col3, col4, col5 whereas table2 has an unique index on col1 as col1 is the primary key for table2. I also have a view, viz., view1 which has been created as create view view1 as select table2.*, table1.* where table2.col5 = table1.col5

Now I have an existing select stmt. as follows :

   select sum(col6)
   from view1
   where col1 = constant

   and col2 = constant
   and col3 = constant
   and col4 = constant

   and table2.col6 = constant

There is no index on col6 in table2. When I ran an explain plan for this select, it indicated that it first did a "full" table scan for table1, followed by a rowid scan for table2 which used the unique index for table2.

I tried to run the above select in alternative fashion as follows :

   select sum(col6)
   from table1 a, table2 b
   where col1 = constant

   and col2 = constant
   and col3 = constant
   and col4 = constant

   and table1.col5 = table2.col5
   and table2.col6 = constant

An explain plan on this select stmt. indicated that it first did a full table scan for table2 followed by a rowid scan on table1 that used a range index for table1. When I timed both the above selects, it showed that the first select was much faster than the second select, however, the timing of the first select is dismally slow. Is there is a way by which I can speed up this select ? U may mail ur response directly to me at my mail address. Thanking u in advance !

Regards,
Vinit.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Jun 17 1998 - 13:41:22 CDT