| speed query,urgent [message #20940] |
Tue, 02 July 2002 08:53  |
Ben
Messages: 48
Registered: January 2000
|
Member |
|
|
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? I have heard about verify access plan to speed queries but do not have enough knowledge about them. Can someone give me some input on this?
Thank you!
|
|
|
|
| Re: speed query,urgent [message #20947 is a reply to message #20940] |
Tue, 02 July 2002 10:59  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
Chances you are using the Cost Based Optimizer (CBO). If you have incorrect stats generated for those tables (e.g. the stats might say there are only 10 rows in each table) then Oracle's execution plan could opt not to use the indexes.
1.) See what they are currently:
select table_name, num_rows from user_tables where table_name in ('TABLE_A', 'TABLE_B');
2.) analyze table TABLE_A estimate statistics;
analyze table TABLE_B estimate statistics;
No try again.
The speed of retrieval of the data in your case should be almost independant of the amount of data in the tables - it should be lightning quick.
If that doesn't work - make sure the PK constraints are enabled (make sure the unique indese are in palce and are valid);
select index_name, uniqueness, status from user_indexes where table_name in ('TABLE_A', 'TABLE_B');
|
|
|
|
| Re: speed query,urgent [message #20954 is a reply to message #20947] |
Tue, 02 July 2002 18:05 |
Gopal
Messages: 23
Registered: June 1999
|
Junior Member |
|
|
Try rephrasing your query,
SELECT A.no
FROM Table A
WHERE EXISTS
(
SELECT 1 FROM TABLE B WHERE B.NO=A.NO
)
Try giving hints. /* +ALL_ROWS */
Try SET TIMING ON
SET TIME ON
SET AUTOTRACE ON
See the output of thet trace by rephrasing your queries.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
