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?

Threaded View

  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 04:39 AM.

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