DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: reducing cost of table query

  1. #1
    Join Date
    Aug 2007
    Posts
    62

    reducing cost of table query

    I have a table query which does SELECT on table A twice. The query is as follows:

    SELECT * from A where (ID, NAME) in (
    SELECT ID, max(NAME) from A group by ID)

    I have a composite index, INN, on (ID, NAME) as well as a single index, NN, on NAME.


    However, when i ran explain plan on this query, it gives the following:


    SELECT STATEMENT Optimizer Mode=ALL_ROWS (rows=2, cost=13457)
    TABLE ACCESS BY INDEX ROWID A (rows=2, bytes=584, cost=2)
    NESTED LOOPS (rows=2, bytes=656, cost=13457)
    VIEW (rows=1 M, bytes=43 M, cost=1186)
    HASH GROUP BY (rows=1, bytes=43 M, cost=1186)
    INDEX FAST FULL SCAN INN_IDX (rows=1 M, bytes=43 M, cost=971)
    INDEX RANGE SCAN INN_IDX (rows=1, cost=1)

    Is there any way i can improve the query by utilizing my present indexes?
    It seems that the line 'SELECT * from...' has the highest cost and doesn't use any index in its scan. Correct me if i'm wrong.

    Also, is there a specific cost range which we should have to ensure optimal performance?

    Thanks!

  2. #2
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    TABLE ACCESS BY INDEX ROWID A
    should tell you that index on A is used. The optimizer executes the subquery using fast full index scan on the composite index, and then joins the results to the table A, using the composite index again, since you join on both columns (id and name)
    NL join is used, probably because of the small number of the returned rows.
    To summarize I see nothing wrong with this query, the plan seems simple and good

  3. #3
    Join Date
    Aug 2007
    Posts
    62
    Thanks Bore!

    Does that mean that this SQL performance is acceptable despite its high cost value? In this case, can we just ignore the cost value?

  4. #4
    Join Date
    Aug 2007
    Posts
    62
    Anyway, the query was re-written and the explain plan was run through as well...
    In this case, the cost is much lower. So, is it correct to say that this will have better performance than the previous query in terms of I/O and time?

    PHP Code:

    SELECT STATEMENT Optimizer Mode
    =ALL_ROWS    (rows=2cost=11)                                       
      
    TABLE ACCESS BY INDEX ROWID    A    (rows=2,    bytes=584cost=2)                                       
        
    NESTED LOOPS        (rows=2,    bytes=656cost=11)                                
          
    VIEW        (rows=80bytes=2 Kcost=8)                                       
            
    HASH GROUP BY    (rows=1,    bytes=5 Kcost=8)                                       
              
    NESTED LOOPS    (rows=80bytes=5 Kcost=    7)                                       
                
    INDEX RANGE SCAN     SYS_C0020790    (rows=1,    bytes=30cost=2)                                       
                
    INDEX RANGE SCAN      INN_IDX (rows=904bytes=31 Kcost=5)                                       
          
    INDEX RANGE SCAN    INN_IDX    (rows=1,    cost=1
    Thanks!!!

  5. #5
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    In the latest explain plan it's using index range scan where in the former plan it was using Index fast full scan. I think the later plan looks good. What about response time. Seems like the later one should have better response time.
    http://www.perf-engg.com
    A performance engineering forum

  6. #6
    Join Date
    Aug 2007
    Posts
    62
    Response time? hm.. haven't thought about it.
    Do you have any idea how to find the estimated response time? Or some link which i can refer to?

    Thanks a lot!

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    Test it.
    Trace it.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    dont believe less cost means faster, not true at all

  9. #9
    Join Date
    Aug 2007
    Posts
    62

    Talking

    The inital query takes 6 sec.. After modifications, it only took 15 ms...

    If 'less cost doesn mean faster', does that mean that we can only use cost to determine the I/O and not execution time?

    Thanks so much!!

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Quote Originally Posted by gxangel
    The inital query takes 6 sec.. After modifications, it only took 15 ms...

    If 'less cost doesn mean faster', does that mean that we can only use cost to determine the I/O and not execution time?

    Thanks so much!!
    Not even I/O

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