Results 1 to 3 of 3

Thread: index optons

  1. #1
    Join Date
    Dec 2002
    Location
    Montreal
    Posts
    1

    Question Unanswered: index optons

    Why, when I submit a query like this:

    select distinct p.blanket_po_number
    from po p , po_line_sku pls
    where pls.po_id = p.po_id
    and pls.allocation_no = 'S000001440'

    Do I get an explain plan like this telling me that the po table is being scanned? Why doesn't it use the available index?
    I have indexes on:
    po.po_id --PK
    po.blanket_po_number --non unique
    po_line_sku.po_id -- first column of unique ndx
    po_line_sku.allocation_no --non unique

    The po table has approx. 70k row
    The po_line_sku table has approx. 1.1 million rows.

    SELECT STATEMENT Optimizer=CHOOSE (Cost=627 Card=138 Bytes=1656)
    SORT (UNIQUE) (Cost=627 Card=138 Bytes=1656)
    HASH JOIN (Cost=611 Card=1735 Bytes=20820)
    TABLE ACCESS (BY INDEX ROWID) OF PO_LINE_SKU (Cost=2 Card=1735 Bytes=8675)
    INDEX (RANGE SCAN) OF PO_LINE_SKU_99 (NON-UNIQUE) (Cost=1 Card=1735)
    TABLE ACCESS (FULL) OF PO (Cost=608 Card=69149 Bytes=484043)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: index optons

    The CBO has estimated that it will be quicker to use a full scan on po and hash join to the rows selected from po_line_sku. It may well be right.

    I think the reasoning could be something like this:

    Cardinality of allocation_no is 1735
    1,100,000 / 1735 = 634
    i.e. 634 po_line_sku records on average for a given allocation_no

    To use an index to get the po records would therefore require:

    634 x index read + 634 x table read = 1278 reads

    Now po has 70000 records. I don't know how big they are, but let's say they were on average 100 bytes, so that's 7,000,000 bytes. If block size is 8K, then this will occupy 7,000,000 / 8,000 blocks = 875 blocks

    In that case, it would be quicker to full scan, i.e. 875 reads than use an index and do 1278 reads.

  3. #3
    Join Date
    Sep 2002
    Location
    ITALY
    Posts
    53

    Re: index optons

    select distinct p.blanket_po_number
    from po p , po_line_sku pls
    where pls.po_id = p.po_id
    and pls.allocation_no = 'S000001440'

    Do I get an explain plan like this telling me that the po table is being scanned? Why doesn't it use the available index?
    I have indexes on:
    po.po_id --PK
    po.blanket_po_number --non unique
    po_line_sku.po_id -- first column of unique ndx
    po_line_sku.allocation_no --non unique


    KEEP attention: try this on test environment before and be sure not do push to hell your rpduction database !!

    IMPORTANT: try different solutions and test the response time of the querys, please do not bound yourself to the plan, HJ really can be the best of the pool, expecially if you have a greate I/O subsystem !!

    The key factors you have to consider are the following:
    1) selectivity and clusterization of of index on pls.allocation_no, so check the storage allocation of your index, and eventually rebuild it;

    2) did you gathered the histograms ? if not you should compute them:
    "analyze table po_line_sku compute statistics for all indexed columns"
    in fact oracle can wrong guess applying the uniform distribution of values in the keys ...

    3) eventually try to "suggest" oracle with this "silly" from clause :-)) :
    from po_line_sku pls, po p
    and finally add the /*+ ORDERED */ hint

    good luck
    Franco Ceotto
    SIPTI srl
    OnSite Services
    9i OCP DBA, Performance Engineer

Posting Permissions

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