query help needed - nested loop (semi)
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: query help needed - nested loop (semi)

  1. #1
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316

    query help needed - nested loop (semi)

    The following query is taking too long (1+ hour) to run, jumped from 2 mins; does anyone see any issues with it ?
    It is using the best indexes possible, the only thing I can think of is the nested loop, instead of Hash Join ? What does (semi) mean ?

    select * from chx_bbo_max
    where TIMESTAMP between
    TO_DATE('20030623' || ' 000000', 'YYYYMMDD HH24MISS') and
    TO_DATE('20030623' || ' 235959', 'YYYYMMDD HH24MISS')
    AND ISSUE_SYMBOL IN(
    SELECT DISTINCT ISSUE_SYMBOL
    FROM floor_stock_table_listed
    WHERE PROCESS_DATE between
    TO_DATE('20030623', 'YYYYMMDD') and
    TO_DATE('20030623', 'YYYYMMDD')
    AND TIME_OF_DAY_INDICATOR = 'BOD'
    AND TIME_DELAY > 5);

    Execution Plan
    ----------------------------------------------------------
    0
    SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=134)


    1 0
    NESTED LOOPS (SEMI) (Cost=3 Card=1 Bytes=134)


    2 1
    TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CHX_BBO_MAX' (Cost=1 Card=1 Bytes=118)


    3 2
    INDEX (RANGE SCAN) OF 'CHX_BBO_MAX_IDX1' (NON-UNIQUE)


    4 1
    TABLE ACCESS (BY INDEX ROWID) OF 'FLOOR_STOCK_TABLE_LISTED' (Cost=2 Card=2324 Bytes=37184)


    5 4
    INDEX (RANGE SCAN) OF 'FLOOR_STOCK_TABLE_LISTED_PK' (UNIQUE) (Cost=1 Card=2)

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I dont understand the point of having this part of code
    Code:
    PROCESS_DATE between
    TO_DATE('20030623', 'YYYYMMDD') and TO_DATE('20030623', 'YYYYMMDD')
    When both the To_Date Functions return '23-Jun-03 00:00:00'?????

    Possibly you could eliminate Between and use > & < operators in main & sub queries.
    Also When you are using IN clause, how does it matter if DISTINCT is not used?
    If you remove that Distinct, the query might run fast...I am assuming the table floor_stock_table_listed has few records.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    PROCESS_DATE between
    TO_DATE('20030623', 'YYYYMMDD') and TO_DATE('20030623', 'YYYYMMDD')
    I replaced the bind variable with the date for testing, that is why this does not make sense... it is flexible, if we have to query for more than one day.

    I analyzed the explain plan for using < & >, instead of between, and also took out the distinct, no change in the explain plan !

    Floor_stock_table_listed has about 900K rows, distinct about 2K.
    chx_bbo_max has 19 weekly partitions (key: timestamp) approx. 121 million rows

  4. #4
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    1. analyze tables
    2. remove distinct
    3. replace "in (subquery)" with "exists (subquery)"
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  5. #5
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    By changing from 'in' to 'exists' for the subquery: there was a small change in response time, but not much. The explain plan shows use of a FILTER, instead of NESTED LOOPS.

    Here it is:
    1 select * from chx_bbo_max
    2 where TIMESTAMP between
    3 TO_DATE('20030624' || ' 000000', 'YYYYMMDD HH24MISS') and
    4 TO_DATE('20030624' || ' 235959', 'YYYYMMDD HH24MISS')
    5 and exists(
    6 SELECT ISSUE_SYMBOL
    7 FROM floor_stock_table_listed
    8 WHERE PROCESS_DATE between
    9 TO_DATE('20030624', 'YYYYMMDD') and
    10 TO_DATE('20030624', 'YYYYMMDD')
    11 AND TIME_OF_DAY_INDICATOR = 'BOD'
    12* AND TIME_DELAY > 5)
    /
    Elapsed: 00:00:00.00

    Execution Plan
    ----------------------------------------------------------
    0
    SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=118)


    1 0
    FILTER


    2 1
    TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CHX_BBO_MAX' (Cost=1 Card=1 Bytes=118)


    3 2
    INDEX (RANGE SCAN) OF 'CHX_BBO_MAX_IDX1' (NON-UNIQUE)


    4 1
    TABLE ACCESS (BY INDEX ROWID) OF 'FLOOR_STOCK_TABLE_LISTED' (Cost=5 Card=2324 Bytes=27888)


    5 4
    INDEX (RANGE SCAN) OF 'FLOOR_STOCK_TABLE_LISTED_PK' (UNIQUE) (Cost=2 Card=2936)
    Last edited by khussain; 06-26-2003 at 10:02 AM.

  6. #6
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    with exists you should add predicate in subquery
    Code:
    and chx_bbo_max.issue_simbol = floor_stock_table_listed.issue_simbol
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  7. #7
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Ooops !

    Same as before, Nested Loops (Semi):

    1 select * from chx_bbo_max
    2 where TIMESTAMP between
    3 TO_DATE('20030624' || ' 000000', 'YYYYMMDD HH24MISS') and
    4 TO_DATE('20030624' || ' 235959', 'YYYYMMDD HH24MISS')
    5 and exists (
    6 SELECT ISSUE_SYMBOL
    7 FROM floor_stock_table_listed
    8 WHERE PROCESS_DATE between
    9 TO_DATE('20030624', 'YYYYMMDD') and
    10 TO_DATE('20030624', 'YYYYMMDD')
    11 AND TIME_OF_DAY_INDICATOR = 'BOD'
    12 AND TIME_DELAY > 5
    13* and chx_bbo_max.issue_symbol = floor_stock_table_listed.issue_symbol)
    /
    Elapsed: 00:00:00.00

    Execution Plan
    ----------------------------------------------------------
    0
    SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=1 Bytes=134)


    1 0
    NESTED LOOPS (SEMI) (Cost=17 Card=1 Bytes=134)


    2 1
    TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'CHX_BBO_MAX' (Cost=1 Card=1 Bytes=118)


    3 2
    INDEX (RANGE SCAN) OF 'CHX_BBO_MAX_IDX1' (NON-UNIQUE)


    4 1
    TABLE ACCESS (BY INDEX ROWID) OF 'FLOOR_STOCK_TABLE_LISTED' (Cost=16 Card=1311 Bytes=20976)


    5 4
    INDEX (RANGE SCAN) OF 'FLOOR_STOCK_TABLE_LISTED_PK' (UNIQUE) (Cost=15 Card=1)

  8. #8
    Join Date
    Jul 2001
    Location
    Slovenia
    Posts
    422
    have you run it?
    Tomaž
    "A common mistake that people make when trying to design something completely
    foolproof is to underestimate the ingenuity of complete fools" - Douglas Adams

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by khussain
    Floor_stock_table_listed has about 900K rows, distinct about 2K.
    chx_bbo_max has 19 weekly partitions (key: timestamp) approx. 121 million rows
    Table Floor_stock_table_listed - 900K is nearly a Million Recs... - Not a problem..
    Table chx_bbo_max - 121 million - I guess here lies the probs.

    BTW, on what COL/COLS of CHX_BBO_MAX is the index 'CHX_BBO_MAX_IDX1' built?

    If its not on ISSUE_SYMBOL, you may have to take a look to build an index.

    If Card of this COL is low then give a shot on BIT MAP Index.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  10. #10
    Join Date
    Sep 2000
    Location
    Chicago, IL
    Posts
    316
    Here is the index for CHX_BBO_MAX table:

    CREATE INDEX CHX_BBO_MAX_IDX1 ON CHX_BBO_MAX
    (TIMESTAMP, ISSUE_SYMBOL)
    INITRANS 2
    MAXTRANS 255
    LOCAL (
    PARTITION CHX_BBO_MAX_IDX1_W20030217
    ...
    ...
    ...

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