Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Arrow Unanswered: Specify index for ra_site_uses_all <<URGENT>>

    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

  2. #2
    Join Date
    Jul 2001
    Location
    Germany
    Posts
    189

    Use a optimizer hint to force index use

    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

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Specify index for ra_site_uses_all <<URGENT>>

    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.

  4. #4
    Join Date
    Mar 2003
    Posts
    7
    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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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?

  6. #6
    Join Date
    Mar 2003
    Posts
    7

    SOLVED...but don't understand

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •