Not using Indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Not using Indexes

  1. #1
    Join Date
    Apr 2001
    Posts
    103

    Angry

    Hello all,
    I have an sql query as follows

    SELECT DISTINCT G_T3.NO_ANNEE, TO_NUMBER(TO_CHAR(G_T3.DT_JOUR, 'MM')) ,
    G_T4.ID_LIEU_SEJ, G_T2.ID_CANAL,
    G_T1.ID_PTVENTE
    FROM INFOCTRE.INF_TB_TR_PTVENTE G_T1 ,
    INF_TB_TR_CAN G_T2 ,
    INF_TB_TT_CAL_EXE G_T3 ,
    INF_TB_TR_LI_SE G_T4
    WHERE G_T4.ID_SAI = G_T3.CD_SAI

    when I do explain plan oracle is not using the index in
    the table INF_TB_TT_CAL_EXE G_T3.
    I did analyze, but still it is not using index.
    does any one has any idea.
    ps: i cannot use rule becasue my query is generated by ETL tool
    Thanks in advance
    Naeem

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712

    ?

    No join to G_T1 and G_T2?
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    Apr 2001
    Posts
    103

    Angry

    Yes there is no join, because i have to make cartesean product.
    Naeem

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    It's possible to force the optimizer to use the index by the INDEX hint:
    SELECT /*+ INDEX (G_T3 index_name) */ DISTINCT ...
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  5. #5
    Join Date
    Mar 2002
    Posts
    534
    Hi Naeem,

    I got some questions:
    how many rows are in the INF_TB_TR_LI_SE G_T4 table?
    how many rows are in the INF_TB_TT_CAL_EXE G_T3 table?
    how many rows are concerned by the condition G_T4.ID_SAI = G_T3.CD_SAI

    Is is correct that your index is on INF_TB_TT_CAL_EXE(CD_SAI)


    Mike

  6. #6
    Join Date
    Apr 2001
    Posts
    103

    Angry

    Hi Mike

    how many rows are in the INF_TB_TR_LI_SE G_T4 table?
    ~300000
    how many rows are in the INF_TB_TT_CAL_EXE G_T3 table?
    ~135000
    how many rows are concerned by the condition G_T4.ID_SAI = G_T3.CD_SAI
    730

    Is is correct that your index is on INF_TB_TT_CAL_EXE(CD_SAI)
    Yes
    Naeem

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Could you post the plan here, please?
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    And one more question: what's the response time of the query?
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  9. #9
    Join Date
    Apr 2001
    Posts
    103

    Angry

    SELECT STATEMENT Optimizer=CHOOSE (Cost=435209 Card=30570210 Bytes=794825460)
    SORT (UNIQUE) (Cost=435209 Card=30570210 Bytes=794825460)
    HASH JOIN (Cost=311 Card=40326660 Bytes=1048493160)
    TABLE ACCESS (FULL) OF INF_TB_TT_CAL_EXE (Cost=2 Card=730 Bytes=9490)
    MERGE JOIN (CARTESIAN) (Cost=301 Card=331452 Bytes=4308876)
    MERGE JOIN (CARTESIAN) (Cost=4 Card=297 Bytes=1782)
    INDEX (FULL SCAN) OF PK_INF_TB_TR_CANAL (UNIQUE) (Cost=1 Card=3 Bytes=9)
    SORT (JOIN) (Cost=3 Card=99 Bytes=297)
    INDEX (FULL SCAN) OF PK_INF_TB_TR_PTVENTE (UNIQUE) (Cost=1 Card=99 Bytes=297)
    SORT (JOIN) (Cost=300 Card=1116 Bytes=7812)
    INDEX (FAST FULL SCAN) OF PK_INF_TB_TR_LI_SE (UNIQUE) (Cost=1 Card=1116 Bytes=7812)
    Naeem

  10. #10
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    The G_T3 and G_T4 join will produce 730 rows. G_T1 seems to have 99 rows and G_T2 3 rows. The cartesian product should have 730*99*3=216810 rows. Is that you're expecting?

    You mentioned you cannot use RBO because the query is generated. Then you cannot use any hint and you are not able to change the text of the query. The only way left is "hack" the statistics of the index in some way. See the DBMS_STATS package. Of course, you might kill (and you do, I think) other good plans by changing index statistics.
    Ales
    The whole difference between a little boy and an adult man is the price of toys

Posting Permissions

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



Click Here to Expand Forum to Full Width