Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Unanswered: problem about query's efficency

    Table MODE_SCORE_RESULT_200405 got 3 indexs(score$;cust_value;cust_area,each field got a index),

    then run two sqls below,they returned same result.But:
    this sql take 527 seconds:
    -------------------------------------------------
    select count(custid) rdCount from MODE_SCORE_RESULT_200405
    where
    SCORE$>=0.5 and cust_value>=50.0 and cust_area = '371'


    while this sql take 0.218 seconds:
    -------------------------------------------------
    select count(*) rdCount from mode_score_result_200405
    where
    SCORE$>=0.5 and cust_value>=50.0 and cust_area = '371'
    -------------------------------------------------
    Why?
    ......

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Since custid is not indexed, the only way Oracle can count it is by performing a full scan on the table; by contrast count(*) allows Oracle to optimize the query by counting entries in one of the indexes. However, I must admit the difference in timings is huge!

    What do you get from EXPLAIN PLAN for each query? I suspect the count(*) uses index merging and the count(custid) uses a full table scan and no indexes.

Posting Permissions

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