speed up query process,urgent!

[mkey]

hi all,
I have two tables that has tones of data.
approximately(16,400,000). What I want
to be able to do is to speed up the query process. The id
column in both tables are unique keys(pk).Therefore
they have indexes.

my select statement look something like this:

select a.* from table_a a, table_b b
where a.id = b.id where a.id = 100

Just this query takes about 35 seconds.
How can I speed the process. I only know that
indexes speed up the process of queries. How
can I speed this up anyother way? Any ideas?

Thank you!

 

[karluk]

Even on such large tables this query should run fast if you really have indexes on both id columns. Have you done an explain plan to verify the access path?

 

[mkey]

I'm not sure what you mean by verify access path. Can u please give me an example?

Thank you!

 

[sunpatil]

It may be because your server configuration is low.

 

[sem]

If you use sql plus you may issue
SET AUTOTRACE ON EXPLAIN
before your SELECT
This will show the execution plan of your statement (whether your indexes are used etc)
You may force index utilizing by writing a hint.

 

[angiole]

Ensure that the ANAYLZE process has been run by the DBA on the table and the index as well.

AA

 

[mkey]

hi all,
I tried forcing the use of index. But I'm getting the ora_00936 error.
my query look something like this:
select id from table_a where id (*) < 50;

What do u think i'm doing wrong here?

 

[spenglerr]

actually what is the speed determining step?

a) the query itself
b) transport via network
c) the output on the screen?

You can test this by comparing the speed of a script spooling the output to a file with the speed of the query within sql*plus.

When working with SQL*PLUS every line is filled up with spaces for the output, so:
SET TAB ON
SET TRIMSPOOL ON
SET TRIMOUT ON
will get faster results in sql*plus.

 

[angiole]

What is the purpose of the (*) in the WHERE statement