How to avoid full tablescan with group function?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to avoid full tablescan with group function?

  1. #1
    Join Date
    Jun 2005
    Location
    India
    Posts
    14

    Cool How to avoid full tablescan with group function?

    Hi,

    When I access my table normaly,without group function it does index range scan like this. as there is index on pharmacyid column

    SELECT drugauctionid, amount
    FROM drugbid Y
    WHERE pharmacyid = 142

    but when i use group function it does a full tablescan like this..

    SELECT drugauctionid, amount
    FROM drugbid Y
    WHERE pharmacyid = 142
    GROUP BY drugauctionid,amount

    the execution plan is as follow when it does a full tablescan

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=8 Card=75 Bytes=
    750)

    1 0 SORT (GROUP BY) (Cost=8 Card=75 Bytes=750)
    2 1 TABLE ACCESS (FULL) OF 'DRUGBID' (Cost=4 Card=75 Bytes=7
    50)

    the value of optimizer_* parameters as as follow.

    ALTER session SET OPTIMIZER_INDEX_CACHING=100
    ALTER session SET OPTIMIZER_INDEX_COST_ADJ=30
    Optimizer mode is first_rows
    version is 9.2.0.6

    How can I use index range scan even with group function?
    Last edited by rgopani; 07-13-2005 at 05:39 AM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The difference between the two queries here is that Oracle knows that it cannot return any rows of the GROUP BY query until it has identified all of the required rows -- normally the GROUP BY would be accompanied by an aggregate function such as SUM(), but just beacuse it is not there doesn't mean that Oracle won't perform the required sorting anyway. Oracle reads the table with a ful table scan because it believes that to be the fastest way of identifying all of the required rows.

    In the first query Oracle can return rows as soon as they are identified as part of the result set, and it might find the first row quicker using an index.

    So this all comes about because the optimizer mode is FIRST_ROWS.

    I also note that in 9.2.0.6 you ought to investigate the use of the DBMS_STATS.GATHER_SYSTEM_STATS procedure to collect statistics on multiblock and single block read times, to aid the optimizer in deciding whether index or table access would be faster.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

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