Home » SQL & PL/SQL » SQL & PL/SQL » How increase the speed for query
How increase the speed for query [message #194831] Mon, 25 September 2006 11:04 Go to next message
enkhbold
Messages: 36
Registered: July 2005
Location: Mongolia
Member


Hi all,

my database has more than 1million records.
many times in a day, i need to get information from the tables.
but, query is very slow.
how to increase the speed?

please give me idea!


Thanks,
Re: How increase the speed for query [message #194835 is a reply to message #194831] Mon, 25 September 2006 11:44 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
it's impossible to give an accurate answer without knowing more about the query you are running, details about the table(s), their indexes, the freshness of statistics etc.
Re: How increase the speed for query [message #194838 is a reply to message #194835] Mon, 25 September 2006 11:52 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Just when I thought I saw the most generic open-ended question ever asked in these forums:
http://www.orafaq.com/forum/m/187562/66800/?srch=%22most+generic%22#msg_187562
this one comes along. Amazing!

[Updated on: Mon, 25 September 2006 11:54]

Report message to a moderator

Re: How increase the speed for query [message #194848 is a reply to message #194838] Mon, 25 September 2006 13:38 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Then only answer would be to delete the rows....
That would speed up his query Wink
Re: How increase the speed for query [message #194850 is a reply to message #194848] Mon, 25 September 2006 13:45 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Course you could set the hidden init parameter "_go_fast=TRUE". Just kidding, please don't try that. That's not a real parameter.

As the others have indicated, more information is needed.
Re: How increase the speed for query [message #194875 is a reply to message #194850] Mon, 25 September 2006 18:41 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Of course _go_fast is rubbish; everyone knows it is _AUTOTUNE=TRUE
Re: How increase the speed for query [message #194878 is a reply to message #194850] Mon, 25 September 2006 19:35 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
In reply to Kimant - have you ever tried deleting all the rows from a table and then a full table scan (without recovering the space)?

set timing on
create table t1 as (select * from dba_objects);
insert into t1 (select * from t1);
.
.
.

insert into t1 (select * from t1);
commit;


Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

SQL> set timing on
SQL> select count(*) from t1;

  COUNT(*)
----------
   7404020

Elapsed: 00:00:29.09

-- delete 1 million at a time...
SQL> delete t1 where rownum < 1000000;

999999 rows deleted.

Elapsed: 00:00:30.05
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
.
.
.

Elapsed: 00:00:00.00
SQL> delete t1 where rownum < 1000000;

404027 rows deleted.

Elapsed: 00:00:33.03
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

-- Query is still very slow - even though it's empty
SQL> select count(*) from t1;

  COUNT(*)
----------
         0

Elapsed: 00:00:23.04

-- But now recover most unused space...
SQL> truncate table t1;

Table truncated.

Elapsed: 00:00:00.05
-- Now it's fast again...
SQL> select count(*) from t1;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.00
SQL>  
Re: How increase the speed for query [message #194879 is a reply to message #194875] Mon, 25 September 2006 19:44 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
rleishman wrote on Mon, 25 September 2006 18:41

Of course _go_fast is rubbish; everyone knows it is _AUTOTUNE=TRUE

I keep forgetting about that one !!

By the way rleishman, thanks for providing your excellent SQL Tuning website, it's EXTREMELY helpful !!
Re: How increase the speed for query [message #194893 is a reply to message #194878] Tue, 26 September 2006 00:55 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Off course Smile
- I was just playing with the way he/she wrote the question.
The onl information/issue was the number of rows.....

I just can't believe that You actually did that!

This thread is not very technically precise, do You think so?

But maybe that it just me.
Previous Topic: stddev
Next Topic: sql prompt
Goto Forum:
  


Current Time: Sat Dec 14 16:31:31 CST 2024