Results 1 to 6 of 6

Thread: SQL Tuning

  1. #1
    Join Date
    Nov 2003
    Posts
    33

    Unanswered: SQL Tuning

    Hi,
    Could someone help me why this query would not use the index on the Invoice number field.

    Here is my query and also pl find the explain plan attached and the indexes available on the EB_READING table.

    select
    b.debtornum,r.premnum,b.e_invnum,b.duedate,b.date_ r
    from eb_invoice b,eb_reading r,final_cust_unalloc f
    where
    r.reversed = 'N'
    and r.readcode = 'F'
    and f.debtornum = b.debtornum
    and b.e_invnum = r.e_invnum
    group by b.debtornum,r.premnum,b.e_invnum,b.duedate,b.date_ r

    we have an index on e_invnum on teh EB_READING table. I am not sure why it is going for a full table scan on EB_READING.

    I would appreciate if anyone could help me.

    Thanks



    SELECT STATEMENT, GOAL = CHOOSE 19088 1 49
    SORT GROUP BY 19088 1 49
    NESTED LOOPS 19067 1 49
    NESTED LOOPS 19066 1 44
    PARTITION RANGE ALL
    TABLE ACCESS FULL ENERGYDB EB_READING 19064 1 16
    TABLE ACCESS BY INDEX ROWID ENERGYDB EB_INVOICE 2 12200696 341619488
    INDEX UNIQUE SCAN ENERGYDB EINV_IDX1 1 12200696
    INDEX RANGE SCAN ENERGYDB IDX_FINAL_CUST_UNALLOC 1 16893 84465

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oracle has chosen EB_READING as the "driving" table for the query. And since there is no restriction on e_invnum (e.g. e_invnum = 1234), then it will process all rows from EB_READING, hence a full table scan is preferable.

    Alternatively, Oracle could have chosen either of the other tables as the "driving" table, and then it might well have used that index - but then it would probably full scan the chosen table.

    You should not expect Oracle to use an index for every table access, in general.

  3. #3
    Join Date
    Nov 2003
    Posts
    33
    Is there anyway i can tune this SQL better? to get the rows fast?

    thanks

  4. #4
    Join Date
    Apr 2003
    Location
    Minneapolis, MN
    Posts
    273
    Quote Originally Posted by Saratha
    Is there anyway i can tune this SQL better? to get the rows fast?

    thanks
    you can cache some or all of them into the buffer depending on the size of these tables
    Bhavin

    MS Computer Science
    OCP DBA 9i/8i

  5. #5
    Join Date
    Nov 2003
    Posts
    33
    Thanks for your suggestion. Actually in the foll SQL,

    select
    b.debtornum,r.premnum,b.e_invnum,b.duedate,b.date_ r
    from eb_invoice b,eb_reading r,final_cust_unalloc f
    where
    r.reversed = 'N'
    and r.readcode = 'F'
    and f.debtornum = b.debtornum
    and b.e_invnum = r.e_invnum
    group by b.debtornum,r.premnum,b.e_invnum,b.duedate,b.date_ r

    I want the final_cust_unalloc to be the driving table as it has only 16,000 customers, for whom i am trying to get the invoice numbers.
    This table is also a temp table in which i have captured the customers for whom i am trying to the information. For some reason Oracle does not seem to use this as the driving table. I am not sure if there is anyway to specify that.


    The EB_READING AND EB_INVOICE has 24, 20 million records respectively.
    The problem is i do not have debtornum in EB_READING to join f and r ,
    neither invoice number in f to join with eb_reading.

    Any suggestion to tune this SQL , will be appreciated very much

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select b.debtornum,r.premnum,b.e_invnum,b.duedate,b.date_ r
    from eb_invoice b,eb_reading r
    where r.reversed = 'N'
    and r.readcode = 'F'
    and b.e_invnum = r.e_invnum
    and exists ( select '1' from final_cust_unalloc f where f.debtornum = b.debtornum)
    group by b.debtornum,r.premnum,b.e_invnum,b.duedate,b.date_ r
    =================================================
    What does the GROUP BY clause do for you?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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