from index full scan to Index rowid scan
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: from index full scan to Index rowid scan

  1. #1
    Join Date
    Apr 2003
    Posts
    353

    from index full scan to Index rowid scan

    Please help me to change the
    plan of ps_item_details itemcode
    to do index rowid scan.

    Now it is taking more time.

    SELECT 1 FROM PS_MASTERS,PS_ITEM_DETAILS
    WHERE SUPL_NAME='YYYY'
    AND PS_MASTERS.ITEM_CODE=PS_ITEM_DETAILS.ITEM_CODE;

    Execution Plan
    ----------------------------------------------------------
    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4558 Card=67251940 B
    ytes=2219314020)

    1 0 MERGE JOIN (Cost=4558 Card=67251940 Bytes=2219314020)
    2 1 INDEX (FULL SCAN) OF 'X_INDX_ITEM_PSITS' (NON-UNIQUE) (C
    ost=4170 Card=574263 Bytes=4594104)

    3 1 SORT (JOIN) (Cost=386 Card=11711 Bytes=292775)
    4 3 TABLE ACCESS (BY INDEX ROWID) OF 'PS_MASTERS' (Cost=2
    Card=11711 Bytes=292775)

    5 4 INDEX (RANGE SCAN) OF 'X_PS_MASTERS_SUPL' (NON-UNIQU
    E) (Cost=1 Card=11711)





    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    4328 consistent gets
    4201 physical reads
    0 redo size
    4133 bytes sent via SQL*Net to client
    2529 bytes received via SQL*Net from client
    21 SQL*Net roundtrips to/from client
    1 sorts (memory)
    0 sorts (disk)
    290 rows processed

  2. #2
    Join Date
    Apr 2003
    Posts
    353
    sORRY i GOT THAT
    SELECT /*+index_ffS (ps_item_details X_INDX_ITEM_PSITS)*/ 1 FROM PS_MASTERS,PS_ITEM_DETAILS
    WHERE SUPL_NAME='Fragrance World (India)'
    AND PS_MASTERS.ITEM_CODE=PS_ITEM_DETAILS.ITEM_CODE

    Learning.. Plans..

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