[visualsander]

Hi group,

i'm using Oracle Applications 10.7

I link the synonym ra_site_uses to so_headers.

The synonym ra_site_uses references the view ra_site_uses wich references the table ra_site_uses_all.

I use this query (and then some...):
SELECT sh.ship_to_site_use_id,
rsu.site_use_id
FROM so_headers sh, ra_site_uses rsu
WHERE sh.ship_to_site_use_id = rsu.site_use_id

when I look at the plan table I see that the table ra_site_uses_all has a FULL TABLE SCAN! However there is a index that uniquely references the field site_use_id called ra_site_uses_U1.

HOW CAN I MAKE SURE THAT THE ABOVE INDEX IS USED?

Regards,

VS

[alligatorsql.com]

Hello,

you can use a SELECT /*+ INDEX(table index) */ .... to force the
optimizer to use a special index ... but be aware of using this ...
sometime the optimizer choose a full table scan cause it is much
quicker to access the datas by a full tables scan f.e. tables are very small ...

You select your datas from ra_site_uses and ra_site_uses_all has an index ??? Is that a mistake ?

If you use cost based optimizer please check your statistic. Is it up to date ?

Hope that helps ?

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

[andrewst]

Originally posted by visualsander
Hi group,

i'm using Oracle Applications 10.7

I link the synonym ra_site_uses to so_headers.

The synonym ra_site_uses references the view ra_site_uses wich references the table ra_site_uses_all.

I use this query (and then some...):
SELECT sh.ship_to_site_use_id,
rsu.site_use_id
FROM so_headers sh, ra_site_uses rsu
WHERE sh.ship_to_site_use_id = rsu.site_use_id

when I look at the plan table I see that the table ra_site_uses_all has a FULL TABLE SCAN! However there is a index that uniquely references the field site_use_id called ra_site_uses_U1.

HOW CAN I MAKE SURE THAT THE ABOVE INDEX IS USED?

Regards,

VS

What makes you so sure it should use the index? There is no restriction in your query so it is likely to return every row from ra_site_uses anyway.

[visualsander]

Thnx for the replys.

As you probably figured out, this isn't the full query. The full query is a couple of pages long. However this is the only ref to the syn ra_site_uses. As I understand it, I can only ref the syn and not the table because of security reasons.

The query only selects 1 order at a time. This order has only 1 site_use_id. That's why I think it should use the index.

Extra Info:
The tables, syns and views are standard Oracle Application objects.

Hope this helps (to help me)

VS

[andrewst]

Originally posted by visualsander
Thnx for the replys.

As you probably figured out, this isn't the full query. The full query is a couple of pages long. However this is the only ref to the syn ra_site_uses. As I understand it, I can only ref the syn and not the table because of security reasons.

The query only selects 1 order at a time. This order has only 1 site_use_id. That's why I think it should use the index.

Extra Info:
The tables, syns and views are standard Oracle Application objects.

Hope this helps (to help me)

VS

What do you get for this query:

select num_rows, blocks
from all_tables
where table_name='RA_SITE_USES_ALL';

Is the value num_rows anywhere near the true number of rows in the table?

[visualsander]

andrewst,

yep the number is almost the same.

However, the problem is solved. But I do NOT understand why. The only thing I changed was the order of the WHERE statements.


I added the statement
sh.ship_to_site_use_id = rsu.site_use_id
to the bottom of the WHERE clauses. At this point PLAN TABLE showed a FULL TABLE SCAN. Then I moved it up in the WHERE clauses so that it was in line with the 'relationship path' and tadaa the full table scan was gone.

In fact the order of tables in the PLAN TABLE was changed. First it started with ra_site_uses_all and now it starts mtl_material_items, wich is the biggest table.

Anyway the problem is solved.

visualsander