Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2007
    Posts
    6

    Unanswered: Nested Loop or Hash Join

    Hello, I have 2 tables:

    TableA = 500k rows
    TableB = 200k rows

    I have a query like this:

    SELECT a.ID
    FROM TableA, TableB
    WHERE a.entered_by = 'xxx'
    AND a.int_id = b.ID
    AND b.interest = 'STANDARD'
    AND a.intent = 'PT';

    This query takes over 40 secs to run. Looking at the explain plan I see it is doing a hash join and has a cost of 1031. If I add the FIRST_ROWS(1) hint it performs a nested loop and the cost is 3. However the query still takes a long time to return. The tables are not particularly big and it is running on a 64 bit server with 8 CPU's and 64GB RAM!! A Production server.

    Any idea's on how I can speed up this query?

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    A good place to start would be to check what indexes you have and if the tables/indexes have been analyzed recently.

    Alan

  3. #3
    Join Date
    Jan 2007
    Posts
    6
    Hi Alan, thanks for the reply. The tables all have indexes and the indexes are correctly being used. The tables and indexes have been analyzed in the past 24 hours.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    What does the explain plan look like? Also autotrace stats would be useful.

    Also do the indexes have the columns in the right order?

    Alan

  5. #5
    Join Date
    Jan 2007
    Posts
    6
    SELECT /*+ FIRST_ROWS(1) */a.ID
    FROM prices_mkt a, interests_select b
    WHERE 'TFSICAP_CARL' = a.entered_by
    AND a.int_id = b.ID
    AND b.interest_category = 'STANDARD'
    AND a.intent = 'PT'
    ------------------------------------------------------------

    Statement Id=5 Type=INDEX
    Cost=1 TimeStamp=09-01-07::11::24:57

    (1) SELECT STATEMENT HINT: FIRST_ROWS
    Est. Rows: 2 Cost: 3
    (6) NESTED LOOPS
    Est. Rows: 2 Cost: 3
    (3) TABLE TABLE ACCESS BY GLOBAL INDEX ROWID TFS.PRICES_T [Analyzed]
    Blocks: 172,392 Est. Rows: 1,950 of 834,155 Cost: 2
    (2) INDEX INDEX RANGE SCAN TFS.PRC_ENTB_I [Analyzed]
    Est. Rows: 9,195 Cost: 1
    (5) TABLE TABLE ACCESS BY INDEX ROWID TFS.INT_FILTER_T [Analyzed]
    (5) Blocks: 3,328 Est. Rows: 1 of 203,393 Cost: 1
    Tablespace: DATA1M
    (4) INDEX (UNIQUE) INDEX UNIQUE SCAN TFS.INTF_PK [Analyzed]
    Est. Rows: 1 Cost: 1

  6. #6
    Join Date
    Jan 2007
    Posts
    6
    If I dont use the FIRST_ROWS the explain plan is:

    SQL Statement from editor:


    SELECT /* + FIRST_ROWS(1) */a.ID
    FROM prices_mkt a, interests_select b
    WHERE 'TFSICAP_CARL' = a.entered_by
    AND a.int_id = b.ID
    AND b.interest_category = 'STANDARD'
    AND a.intent = 'PT'
    ------------------------------------------------------------

    Statement Id=5 Type=HASH JOIN
    Cost=0 TimeStamp=09-01-07::11::25:58

    (1) SELECT STATEMENT ALL_ROWS
    Est. Rows: 1,987 Cost: 1,031
    (8) HASH JOIN
    Est. Rows: 1,987 Cost: 1,031
    (3) TABLE TABLE ACCESS BY GLOBAL INDEX ROWID TFS.PRICES_T [Analyzed]
    Blocks: 172,392 Est. Rows: 1,987 of 834,155 Cost: 997
    (2) INDEX INDEX RANGE SCAN TFS.PRC_ENTB_I [Analyzed]
    Est. Rows: 9,195 Cost: 2
    (7) VIEW VIEW TFS.index$_join$_003
    Est. Rows: 26,319 Cost: 33
    (6) HASH JOIN
    (4) INDEX INDEX RANGE SCAN TFS.INTF_CAT [Analyzed]
    Est. Rows: 26,319 Cost: 3
    (5) INDEX (UNIQUE) INDEX FAST FULL SCAN TFS.INTF_PK [Analyzed]
    Est. Rows: 26,319 Cost: 27

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    How are the indexes on the 2 tables defined and also how are the tables partitioned?

    Alan

  8. #8
    Join Date
    Jan 2007
    Posts
    6
    CREATE INDEX prc_entb_i ON prices_t
    (
    entered_by ASC
    )
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    TABLESPACE indx32k
    STORAGE (
    INITIAL 65536
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL KEEP
    )


    and

    CREATE UNIQUE INDEX intf_pk ON int_filter_t
    (
    prd_code ASC,
    id ASC
    )
    PCTFREE 10
    INITRANS 2
    MAXTRANS 255
    TABLESPACE indx32k
    STORAGE (
    INITIAL 65536
    MINEXTENTS 1
    MAXEXTENTS 2147483645
    BUFFER_POOL KEEP
    )

    Only the prices table is partitioned, PARTITION BY HASH (ID)

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    try the following indexes on prices_t
    (entered_by, intent, int_id)
    (intent, entered_by, int_id)
    You should also try compression on the first 2 columns


    On the interests_select I am a bit puzzled as to how it manages an unique index scan when no where in your query do you reference prd_code?

    Anyway a good index on this table might be
    (ID,interest_category )

    Use autotrace to see what works best as explain plan cost is only a guesstimate.

    Alan

Posting Permissions

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