Results 1 to 4 of 4

Thread: Optimize SQL

  1. #1
    Join Date
    Oct 2002
    Posts
    7

    Unanswered: Optimize SQL

    Hi all,

    I'm not a "tuning" expert when it comes to SQL. By glancing at the explain plan below can someone recommend a hint?

    SELECT STATEMENT Optimizer=CHOOSE (Cost=295 Card=1 Bytes=388)
    HASH JOIN (Cost=295 Card=1 Bytes=388)
    MERGE JOIN (CARTESIAN) (Cost=244 Card=1 Bytes=154)
    VIEW (Cost=122 Card=1 Bytes=77)
    FILTER
    SORT (GROUP BY) (Cost=122 Card=1 Bytes=144)
    HASH JOIN (Cost=117 Card=73 Bytes=10512)
    TABLE ACCESS (BY INDEX ROWID) OF DAILY_INVENTORY_BAS (Cost=3 Card=42 Bytes=1890)
    INDEX (RANGE SCAN) OF XAKDAILY_INVENTORY_BAS (UNIQUE) (Cost=2 Card=148584)
    HASH JOIN (Cost=113 Card=158 Bytes=15642)
    NESTED LOOPS (Cost=44 Card=14 Bytes=854)
    INDEX (RANGE SCAN) OF XAKDAILY_INVENTORY_BAS (UNIQUE) (Cost=2 Card=42 Bytes=672)
    TABLE ACCESS (BY INDEX ROWID) OF ALL_TIME_PERIOD_DIM (Cost=1 Card=1 Bytes=45)
    INDEX (UNIQUE SCAN) OF XPK_ATPD (UNIQUE)
    TABLE ACCESS (FULL) OF ALL_TIME_PERIOD_DIM (Cost=68 Card=1129 Bytes=42902)
    BUFFER (SORT) (Cost=244 Card=1 Bytes=77)
    VIEW (Cost=122 Card=1 Bytes=77)
    FILTER
    SORT (GROUP BY) (Cost=122 Card=1 Bytes=144)
    HASH JOIN (Cost=117 Card=73 Bytes=10512)
    TABLE ACCESS (BY INDEX ROWID) OF DAILY_INVENTORY_BAS (Cost=3 Card=42 Bytes=1890)
    INDEX (RANGE SCAN) OF XAKDAILY_INVENTORY_BAS (UNIQUE) (Cost=2 Card=148584)
    HASH JOIN (Cost=113 Card=158 Bytes=15642)
    NESTED LOOPS (Cost=44 Card=14 Bytes=854)
    INDEX (RANGE SCAN) OF XAKDAILY_INVENTORY_BAS (UNIQUE) (Cost=2 Card=42 Bytes=672)
    TABLE ACCESS (BY INDEX ROWID) OF ALL_TIME_PERIOD_DIM (Cost=1 Card=1 Bytes=45)
    INDEX (UNIQUE SCAN) OF XPK_ATPD (UNIQUE)
    TABLE ACCESS (FULL) OF ALL_TIME_PERIOD_DIM (Cost=68 Card=1129 Bytes=42902)
    VIEW (Cost=50 Card=1 Bytes=234)
    SORT (GROUP BY) (Cost=50 Card=1 Bytes=84)
    NESTED LOOPS (Cost=45 Card=59 Bytes=4956)
    TABLE ACCESS (BY INDEX ROWID) OF DAILY_INVENTORY_BAS (Cost=3 Card=42 Bytes=1638)
    INDEX (RANGE SCAN) OF XAKDAILY_INVENTORY_BAS (UNIQUE) (Cost=2 Card=42)
    TABLE ACCESS (BY INDEX ROWID) OF ALL_TIME_PERIOD_DIM (Cost=1 Card=1 Bytes=45)
    INDEX (UNIQUE SCAN) OF XPK_ATPD (UNIQUE)
    SORT (AGGREGATE)
    HASH JOIN (Cost=4885 Card=774128 Bytes=37158144)
    TABLE ACCESS (FULL) OF ALL_TIME_PERIOD_DIM (Cost=68 Card=1129 Bytes=40644)
    TABLE ACCESS (FULL) OF DAILY_INVENTORY_BAS (Cost=4816 Card=62405 Bytes=748860)

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    try to tune anything that has "TABLE ACCESS (FULL)" first.
    by tuning I mean try to add an appropriate index or index-hint.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It could not hurt & just might help folks if you actually posted the SQL.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi

    The best I can offer is http://www.billmagee.co.uk/oracle/sqltune/index.html

    Even with the SQL, only you know your data. Sometimes a FTS (Full table scan) is the best way to go. Sometimes, a post on here might help. You're in a gray area.

    HtH
    Bill

Posting Permissions

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