| Performance Issue [message #38881] |
Thu, 23 May 2002 10:11  |
Joe
Messages: 138
Registered: November 1999
|
Senior Member |
|
|
When I try to join two tables say A & B using a particular ID, one table is using the index and another one goes FULL TABLE SCAN though it has an index.
select a.name from a, b where a.id = b.id;
both tables have same no number of records and indexex defined on id column. It is taking lot of time.
Is that how it works ? Is there any way i can force the query to use both indexes. Thanks.
|
|
|
|
| Re: Performance Issue [message #38885 is a reply to message #38881] |
Thu, 23 May 2002 11:37  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
It is normal and expected to see a full-table scan for one table and an index scan for the other with this query. You are asking to find the matching row(s) in one table for _each_ row in the other. Evaluating each row translates into a full-table scan.
|
|
|
|
| Re: Performance Issue [message #38909 is a reply to message #38881] |
Sat, 25 May 2002 18:11 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
I dont agree with m that hints dont force the optimizer to use the specified indexes. The hints are meant to instruct the optimizer to what needs to be considered during the search. The INDEX hint to instruct the optimizer to use that specific index in searching process.
Joe, make sure your database has been analyzed (otherwise use ANALYZE command to analyze your database) and the statistics are ready, so that CBO will be aware of the size of the tables and other useful info for quick searching. Using hints is a better way of telling your optimizer to use the specific indexes. Try it out.
Try out the following (once after your tablesare analyzed):
ALTER SESSION SET OPTIMIZER_GOAL=CHOOSE => this instructs the optimizer to choose the cost based approach. This one alters the goal for you session.
You can also use the hint in your select statement like:
SELECT /*+CHOOSE*/ A.NAME FROM A, B WHERE A.ID=B.ID;
Try it out.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
