Re: PARTITION HASH INLIST -- QUERY

From: Steve Howard <stevedhoward_at_gmail.com>
Date: Wed, 23 Dec 2009 10:49:18 -0800 (PST)
Message-ID: <b02dce21-6a3c-4998-87b1-e4f017207e32_at_n13g2000vbe.googlegroups.com>



On Dec 23, 1:22 pm, Ind-dba <oraclear..._at_googlemail.com> wrote:
> I need your help in a SQL tuning exercise:
>
> SQL:
> =======
> select /*+ gather_plan_statistics index(ag,PK_AD_Y) */ count
> (t.EDIT_STATUS)
> , count(ag.cr_date)
> from CMPGN.TE_Y t
> join CMPGN.AD_Y ag on ag.ad_grp_id=t.ad_grp_id
> and  ag.ad_grp_id in
> (
> ..,
> ..,
> 997 values
> )
> order by t.acct_id, ag.cmpgn_id, t.ad_grp_id, t.term_id;
>
> PLAN:
> ======
> --------------------------------------------------------------------------- ---------------------------------------------------
> | Id  | Operation                              | Name             |
> Starts | E-Rows | A-Rows |   A-Time   | Buffers |
> Reads  |
> --------------------------------------------------------------------------- ---------------------------------------------------
> |   1 |  SORT AGGREGATE                        |
> |      1 |      1 |      1 |00:00:20.72 |    2406K|
> 36 |
> |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID    | TE_Y         |      1
> |      1 |  15988 |00:00:20.64 |    2406K|
> 36 |
> |   3 |    NESTED LOOPS                        |
> |      1 |    790 |  16986 |00:02:46.81 |    2403K|
> 36 |
> |   4 |     INLIST ITERATOR                    |
> |      1 |        |    997 |00:00:00.22 |    2991 |
> 4 |
> |   5 |      PARTITION HASH ITERATOR           |                  |
> 997 |    784 |    997 |00:00:00.19 |    2991 |
> 4 |
> |   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y       |    997
> |    784 |    997 |00:00:00.16 |    2991 |
> 4 |
> |*  7 |        INDEX UNIQUE SCAN               | PK_AD_Y    |    997
> |    303 |    997 |00:00:00.03 |    1994 |
> 0 |
> |   8 |     PARTITION HASH INLIST              |                  |
> 997 |      1 |  15988 |00:00:11.45 |    2400K|
> 32 |
> |*  9 |      INDEX RANGE SCAN                  | IDX_TE_Y_UQ1 |
> 788K|      1 |  15988 |00:00:16.43 |    2400K|
> 32 |
> --------------------------------------------------------------------------- ---------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    7 - access(("AG"."AD_GRP_ID"=16741340929 OR
> "AG"."AD_GRP_ID"=16741341429 OR
> "AG"."AD_GRP_ID"=16741341929 OR
>               "AG"."AD_GRP_ID"=16741342429 OR
> "AG"."AD_GRP_ID"=16741342929 OR
> "AG"."AD_GRP_ID"=16741343429 OR
>               "AG"."AD_GRP_ID"=16741343929 OR
> "AG"."AD_GRP_ID"=16741444429 OR
> "AG"."AD_GRP_ID"=16741444929 OR
>               "AG"."AD_GRP_ID"=16741445429 OR
> "AG"."AD_GRP_ID"=16741445929 OR
> "AG"."AD_GRP_ID"=16741446429 OR
>               "AG"."AD_GRP_ID"=16741446929 OR
> "AG"."AD_GRP_ID"=16741447429 OR
> "AG"."AD_GRP_ID"=16741447929 OR
>               "AG"."AD_GRP_ID"=16741448429 OR
> "AG"."AD_GRP_ID"=16741448929 OR
> "AG"."AD_GRP_ID"=16741449429 OR
>               "AG"."AD_GRP_ID"=16741449929 OR
> "AG"."AD_GRP_ID"=16741450429 OR
> "AG"."AD_GRP_ID"=16741450929 OR
>               "AG"."AD_GRP_ID"=16741451429 OR
> "AG"."AD_GRP_ID"=16741451929 OR
> "AG"."AD_GRP_ID"=16741452429 OR
>               "AG"."AD_GRP_ID"=16741452929 OR
> "AG"."AD_GRP_ID"=16741453429 OR
> "AG"."AD_GRP_ID"=16741453929 OR
>               "AG"."AD_GRP_ID"=16741454429 OR
> "AG"."AD_GRP_ID"=16741454929 OR
> "AG"."AD_GRP_ID"=16741455429 OR
>               "AG"."AD_GRP_ID"=16741455929 OR
> "AG"."AD_GRP_ID"=16741456429 OR
> "AG"."AD_GRP_ID"=16741456929 OR
>               "AG"."AD_GRP_ID"=16741457429 OR
> "AG"."AD_GRP_ID"=16741457929 OR
> "AG"."AD_GRP_ID"=16741458429 OR
>               "AG"."AD_GRP_ID"=16741458929 OR
> "AG"."AD_GRP_ID"=16741459429 OR
> "AG"."AD_GRP_ID"=16741459929 OR
>               "AG"."AD_GRP_ID"=16741460429 OR
> "AG"."AD_GRP_ID"=16741460929 OR
> "AG"."AD_GRP_ID"=16741461429 OR
>               "AG"."AD_GRP_ID"=16741461929 OR
> "AG"."AD_GRP_ID"=16741462429 OR
> "AG"."AD_GRP_ID"=16741462929 OR
>               "AG"."AD_GRP_ID"=16741463429 OR
> "AG"."AD_GRP_ID"=16741463929 OR
> "AG"."AD_GRP_ID"=16741464429 OR
>               "AG"."AD_GRP_ID"=16741464929 OR
> "AG"."AD_GRP_ID"=16741465429 OR
> "AG"."AD_GRP_ID"=16741465929 OR
>               "AG"."AD_GRP_ID"=16741466429 OR
> "AG"."AD_GRP_ID"=16741466929 OR
> "AG"."AD_GRP_ID"=16741467429 OR
>               "AG"."AD_GRP_ID"=16741467929 OR
> "AG"."AD_GRP_ID"=16741468429 OR
> "AG"."AD_GRP_ID"=16741468929 OR
>               "AG"."AD_GRP_ID"=16741469429 OR
> "AG"."AD_GRP_ID"=16741469929 OR
> "AG"."AD_GRP_ID"=16741470429 OR
>               "AG"."AD_GRP_ID"=16741470929 OR
> "AG"."AD_GRP_ID"=16741471429 OR
> "AG"."AD_GRP_ID"=16741471929 OR
>               "AG"."AD_GRP_ID"=16741472429 OR
> "AG"."AD_GRP_ID"=16741472929 OR
> "AG"."AD_GRP_ID"=16741473429 OR
>               "AG"."AD_GRP_ID"=16741473929 OR
> "AG"."AD_GRP_ID"=16741474429 OR
> "AG"."AD_GRP_ID"=16741474929 OR
>               "AG"."AD_GRP_ID"=16741475429 OR
> "AG"."AD_GRP_ID"=16741475929 OR
> "AG"."AD_GRP_ID"=16741476429 OR
>               "AG"."AD_GRP_ID"=16741476929 OR
> "AG"."AD_GRP_ID"=16741477429 OR
> "AG"."AD_GRP_ID"=16741477929 OR
>               "AG"."AD_GRP_ID"=16741478429 OR
> "AG"."AD_GRP_ID"=16741478929 OR
> "AG"."AD_GRP_ID"=16741479429 OR
>               "AG"."AD_GRP_ID"=16741479929 OR
> "AG"."AD_GRP_ID"=16741480429 OR
> "AG"."AD_GRP_ID"=16741480929 OR
>               "AG"."AD_GRP_ID"=16741481429 OR
> "AG"."AD_GRP_ID"=16741481929 OR
> "AG"."AD_GRP_ID"=16741482429 OR
>               "AG"."AD_GRP_ID"=16741482929 OR
> "AG"."AD_GRP_ID"=16741483429 OR
> "AG"."AD_GRP_ID"=16741483929 OR
>               "AG"."AD_GRP_ID"=16741484429 OR
> "AG"."AD_GRP_ID"=16741484929 OR
> "AG"."AD_GRP_ID"=16741485429 OR
>               "AG"."AD_GRP_ID"=16741485929 OR
> "AG"."AD_GRP_ID"=16741486429 OR
> "AG"."AD_GRP_ID"=16741486929 OR
>               "AG"."AD_GRP_ID"=16741487429 OR
> "AG"."AD_GRP_ID"=16741487929 OR
> "AG"."AD_GRP_ID"=16741488429 OR
>               "AG"."AD_GRP_ID"=16741488929 OR
> "AG"."AD_GRP_ID"=16741489429 OR
> "AG"."AD_GRP_ID"=16741489929 OR
>               "AG"."AD_GRP_ID"=16741490429 OR
> "AG"."AD_GRP_ID"=16741490929 OR
> "AG"."AD_GRP_ID"=16741491429 OR
>               "AG"."AD_GRP_ID"=16741491929 OR
> "AG"."AD_GRP_ID"=16741492429 OR
> "AG"."AD_GRP_ID"=16741492929 OR
>               "AG"."AD_GRP_ID"=16741493429 OR
> "AG"."AD_GRP_ID"=16741493929 OR
> "AG"."AD_GRP_ID"=16741494429 OR
>               "AG"."AD_GRP_ID"=16741494929 OR
> "AG"."AD_GRP_ID"=16741495429 OR
> "AG"."AD_GRP_ID"=16741495929 OR
>               "AG"."AD_GRP_ID"=16741496429 OR
> "AG"."AD_GRP_ID"=16741496929 OR
> "AG"."AD_GRP_ID"=16741497429 OR
>               "AG"."AD_GRP_ID"=16741497929 OR
> "AG"."AD_GRP_ID"=16741498429 OR
> "AG"."AD_GRP_ID"=16741498929 OR
>               "AG"."AD_GRP_ID"=16741499429 OR
> "AG"."AD_GRP_ID"=16741499929 OR
> "AG"."AD_GRP_ID"=16741500429 OR
>               "AG"."AD_GRP_ID"=16741500929 OR
> "AG"."AD_GRP_ID"=16741501429 OR
> "AG"."AD_GRP_ID"=16741501929 OR
>               "AG"."AD_GRP_ID"=16741502429 OR
> "AG"."AD_GRP_ID"=16741502929 O)
>    9 - access("T"."AD_GRP_ID"="AG"."AD_GRP_ID")
>        filter(("T"."AD_GRP_ID"=16741340929 OR
> "T"."AD_GRP_ID"=16741341429 OR
> "T"."AD_GRP_ID"=16741341929 OR
>               "T"."AD_GRP_ID"=16741342429 OR
> "T"."AD_GRP_ID"=16741342929 OR
> "T"."AD_GRP_ID"=16741343429 OR
>               "T"."AD_GRP_ID"=16741343929 OR
> "T"."AD_GRP_ID"=16741444429 OR
> "T"."AD_GRP_ID"=16741444929 OR
>               "T"."AD_GRP_ID"=16741445429 OR
> "T"."AD_GRP_ID"=16741445929 OR
> "T"."AD_GRP_ID"=16741446429 OR
>               "T"."AD_GRP_ID"=16741446929 OR
> "T"."AD_GRP_ID"=16741447429 OR
> "T"."AD_GRP_ID"=16741447929 OR
>               "T"."AD_GRP_ID"=16741448429 OR
> "T"."AD_GRP_ID"=16741448929 OR
> "T"."AD_GRP_ID"=16741449429 OR
>               "T"."AD_GRP_ID"=16741449929 OR
> "T"."AD_GRP_ID"=16741450429 OR
> "T"."AD_GRP_ID"=16741450929 OR
>               "T"."AD_GRP_ID"=16741451429 OR
> "T"."AD_GRP_ID"=16741451929 OR
> "T"."AD_GRP_ID"=16741452429 OR
>               "T"."AD_GRP_ID"=16741452929 OR
> "T"."AD_GRP_ID"=16741453429 OR
> "T"."AD_GRP_ID"=16741453929 OR
>               "T"."AD_GRP_ID"=16741454429 OR
> "T"."AD_GRP_ID"=16741454929 OR
> "T"."AD_GRP_ID"=16741455429 OR
>               "T"."AD_GRP_ID"=16741455929 OR
> "T"."AD_GRP_ID"=16741456429 OR
> "T"."AD_GRP_ID"=16741456929 OR
>               "T"."AD_GRP_ID"=16741457429 OR
> "T"."AD_GRP_ID"=16741457929 OR
> "T"."AD_GRP_ID"=16741458429 OR
>               "T"."AD_GRP_ID"=16741458929 OR
> "T"."AD_GRP_ID"=16741459429 OR
> "T"."AD_GRP_ID"=16741459929 OR
>               "T"."AD_GRP_ID"=16741460429 OR
> "T"."AD_GRP_ID"=16741460929 OR
> "T"."AD_GRP_ID"=16741461429 OR
>               "T"."AD_GRP_ID"=16741461929 OR
> "T"."AD_GRP_ID"=16741462429 OR
> "T"."AD_GRP_ID"=16741462929 OR
>               "T"."AD_GRP_ID"=16741463429 OR
> "T"."AD_GRP_ID"=16741463929 OR
> "T"."AD_GRP_ID"=16741464429 OR
>               "T"."AD_GRP_ID"=16741464929 OR
> "T"."AD_GRP_ID"=16741465429 OR
> "T"."AD_GRP_ID"=16741465929 OR
>               "T"."AD_GRP_ID"=16741466429 OR
> "T"."AD_GRP_ID"=16741466929 OR
> "T"."AD_GRP_ID"=16741467429 OR
>               "T"."AD_GRP_ID"=16741467929 OR
> "T"."AD_GRP_ID"=16741468429 OR
> "T"."AD_GRP_ID"=16741468929 OR
>               "T"."AD_GRP_ID"=16741469429 OR
> "T"."AD_GRP_ID"=16741469929 OR
> "T"."AD_GRP_ID"=16741470429 OR
>               "T"."AD_GRP_ID"=16741470929 OR
> "T"."AD_GRP_ID"=16741471429 OR
> "T"."AD_GRP_ID"=16741471929 OR
>               "T"."AD_GRP_ID"=16741472429 OR
> "T"."AD_GRP_ID"=16741472929 OR
> "T"."AD_GRP_ID"=16741473429 OR
>               "T"."AD_GRP_ID"=16741473929 OR
> "T"."AD_GRP_ID"=16741474429 OR
> "T"."AD_GRP_ID"=16741474929 OR
>               "T"."AD_GRP_ID"=16741475429 OR
> "T"."AD_GRP_ID"=16741475929 OR
> "T"."AD_GRP_ID"=16741476429 OR
>               "T"."AD_GRP_ID"=16741476929 OR
> "T"."AD_GRP_ID"=16741477429 OR
> "T"."AD_GRP_ID"=16741477929 OR
>               "T"."AD_GRP_ID"=16741478429 OR
> "T"."AD_GRP_ID"=16741478929 OR
> "T"."AD_GRP_ID"=16741479429 OR
>               "T"."AD_GRP_ID"=16741479929 OR
> "T"."AD_GRP_ID"=16741480429 OR
> "T"."AD_GRP_ID"=16741480929 OR
>               "T"."AD_GRP_ID"=16741481429 OR
> "T"."AD_GRP_ID"=16741481929 OR
> "T"."AD_GRP_ID"=16741482429 OR
>               "T"."AD_GRP_ID"=16741482929 OR
> "T"."AD_GRP_ID"=16741483429 OR
> "T"."AD_GRP_ID"=16741483929 OR
>               "T"."AD_GRP_ID"=16741484429 OR
> "T"."AD_GRP_ID"=16741484929 OR
> "T"."AD_GRP_ID"=16741485429 OR
>               "T"."AD_GRP_ID"=16741485929 OR
> "T"."AD_GRP_ID"=16741486429 OR
> "T"."AD_GRP_ID"=16741486929 OR
>               "T"."AD_GRP_ID"=16741487429 OR
> "T"."AD_GRP_ID"=16741487929 OR
> "T"."AD_GRP_ID"=16741488429 OR
>               "T"."AD_GRP_ID"=16741488929 OR
> "T"."AD_GRP_ID"=16741489429 OR
> "T"."AD_GRP_ID"=16741489929 OR
>               "T"."AD_GRP_ID"=16741490429 OR
> "T"."AD_GRP_ID"=16741490929 OR
> "T"."AD_GRP_ID"=16741491429 OR
>               "T"."AD_GRP_ID"=16741491929 OR
> "T"."AD_GRP_ID"=16741492429 OR
> "T"."AD_GRP_ID"=16741492929 OR
>               "T"."AD_GRP_ID"=16741493429 OR
> "T"."AD_GRP_ID"=16741493929 OR
> "T"."AD_GRP_ID"=16741494429 OR
>               "T"."AD_GRP_ID"=16741494929 OR
> "T"."AD_GRP_ID"=16741495429 OR
> "T"."AD_GRP_ID"=16741495929 OR
>               "T"."AD_GRP_ID"=16741496429 OR
> "T"."AD_GRP_ID"=16741496929 OR
> "T"."AD_GRP_ID"=16741497429 OR
>               "T"."AD_GRP_ID"=16741497929 OR
> "T"."AD_GRP_ID"=16741498429 OR
> "T"."AD_GRP_ID"=16741498929 OR
>               "T"."AD_GRP_ID"=16741499429 OR
> "T"."AD_GRP_ID"=16741499929 OR
> "T"."AD_GRP_ID"=16741500429 OR
>               "T"."AD_GRP_ID"=16741500929 OR
> "T"."AD_GRP_ID"=16741501429 OR
> "T"."AD_GRP_ID"=16741501929 OR
>               "T"."AD_GRP_ID"=16741502429 OR
> "T"."AD_GRP_ID"=16741502929 OR
> "T"."AD_GRP_ID"=16741503429 OR
>               "T"."AD_GRP_ID"=16741503929 OR
> "T"."AD_GRP_ID"=16741504429 OR
> "T"."AD_GRP_ID"=16741504929 OR)
>
> 118 rows selected.
>
> What I'm not able to understand is 788k "Starts" to Step 9.Why is this
> step executed so many times? The INLIST has 997 values. It seems like
> 788k comes from 997*791
> Because if I revrese the NL, I see following plan:
>
> select /*+ gather_plan_statistics index(ag,PK_AD_Y) leading(t,ag)
> use_nl(t,ag) */ count(t.EDIT_STATUS)
> , count(ag.cr_date)
> from CMPGN.TE_Y t
> join CMPGN.AD_Y ag on ag.ad_grp_id=t.ad_grp_id
> and  ag.ad_grp_id in
> (
> ..,
> ..,
> 997 values
> )
> order by t.acct_id, ag.cmpgn_id, t.ad_grp_id, t.term_id;
>
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
> | Id  | Operation                             | Name            |
> Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |
> 1Mem | Used-Mem |
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
> |   1 |  SORT AGGREGATE                      |                  |
> 1 |      1 |      1 |00:00:33.37 |    3978K|      3 |       |
> |          |
> |   2 |   PARTITION HASH INLIST              |                  |
> 1 |    790 |  15988 |00:00:55.12 |    3978K|      3 |       |
> |          |
> |*  3 |    HASH JOIN                         |                  |
> 791 |    790 |  15988 |00:00:23.24 |    3978K|      3 |  2958K|
> 1691K| 1021K (0)|
> |   4 |     INLIST ITERATOR                  |                  |
> 791  |        |  15988 |00:00:06.57 |    2400K|      0 |       |
> |          |
> |*  5 |      INDEX RANGE SCAN                | IDX_TE_Y_UQ1 |
> 788K|  67225 |  15988 |00:00:10.02 |    2400K|      0 |       |
> |          |
> |   6 |     INLIST ITERATOR                  |                  |
> 791 |        |    997 |00:00:18.62 |    1578K|      3 |       |
> |          |
> |   7 |      TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y      |
> 788K|    784 |    997 |00:00:16.67 |    1578K|      3 |       |
> |          |
> |*  8 |       INDEX UNIQUE SCAN              | PK_AD_Y    |
> 788K|    303 |    997 |00:00:08.07 |    1577K|      0 |       |
> |          |
> --------------------------------------------------------------------------- --------------------------------------------------------------------------- --
>
> Do you notice 791 in "starts" here.
> I see some correlation but not able to form a conclusive statement
> from this.
>
> I have been able to tune the SQL by using use_concat hint. But the
> solution finding was kind of luck.
>
> I would like if any one can explain me how Oracle would have
> calculated the "Starts" to 788k and how use_concat hint solved the
> issue.
>
> If I use the hint USE_CONCAT, I get the following plan:
>
> --------------------------------------------------------------------------- ---------------------------------------------------
> | Id  | Operation                              | Name             |
> Starts | E-Rows | A-Rows |   A-Time   | Buffers |
> Reads  |
> --------------------------------------------------------------------------- ---------------------------------------------------
> |   1 |  SORT AGGREGATE                        |
> |      1 |      1 |      1 |00:00:00.40 |    9047 |
> 3 |
> |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID    | TE_Y         |      1
> |    126 |  15988 |00:00:00.50 |    9047 |
> 3 |
> |   3 |    NESTED LOOPS                        |
> |      1 |  99200 |  16986 |00:00:00.19 |    6140 |
> 3 |
> |   4 |     INLIST ITERATOR                    |
> |      1 |        |    997 |00:00:00.06 |    2991 |
> 1 |
> |   5 |      PARTITION HASH ITERATOR           |                  |
> 997 |    784 |    997 |00:00:00.11 |    2991 |
> 1 |
> |   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| AD_Y       |    997
> |    784 |    997 |00:00:00.09 |    2991 |
> 1 |
> |*  7 |        INDEX UNIQUE SCAN               | PK_AD_Y    |    997
> |    303 |    997 |00:00:00.02 |    1994 |
> 0 |
> |   8 |     PARTITION HASH ITERATOR            |                  |
> 997 |    126 |  15988 |00:00:00.34 |    3149 |
> 2 |
> |*  9 |      INDEX RANGE SCAN                  | IDX_TE_Y_UQ1 |    997
> |    126 |  15988 |00:00:00.25 |    3149 |
> 2 |
> --------------------------------------------------------------------------- ---------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    7 - access(("AG"."AD_GRP_ID"=16741340929 OR
> "AG"."AD_GRP_ID"=16741341429 OR
> "AG"."AD_GRP_ID"=16741341929 OR
>               "AG"."AD_GRP_ID"=16741342429 OR
> "AG"."AD_GRP_ID"=16741342929 OR
> "AG"."AD_GRP_ID"=16741343429 OR
>               "AG"."AD_GRP_ID"=16741343929 OR
> "AG"."AD_GRP_ID"=16741444429 OR
> "AG"."AD_GRP_ID"=16741444929 OR
>               "AG"."AD_GRP_ID"=16741445429 OR
> "AG"."AD_GRP_ID"=16741445929 OR
> "AG"."AD_GRP_ID"=16741446429 OR
>               "AG"."AD_GRP_ID"=16741446929 OR
> "AG"."AD_GRP_ID"=16741447429 OR
> "AG"."AD_GRP_ID"=16741447929 OR
>               "AG"."AD_GRP_ID"=16741448429 OR
> "AG"."AD_GRP_ID"=16741448929 OR
> "AG"."AD_GRP_ID"=16741449429 OR
>               "AG"."AD_GRP_ID"=16741449929 OR
> "AG"."AD_GRP_ID"=16741450429 OR
> "AG"."AD_GRP_ID"=16741450929 OR
>               "AG"."AD_GRP_ID"=16741451429 OR
> "AG"."AD_GRP_ID"=16741451929 OR
> "AG"."AD_GRP_ID"=16741452429 OR
>               "AG"."AD_GRP_ID"=16741452929 OR
> "AG"."AD_GRP_ID"=16741453429 OR
> "AG"."AD_GRP_ID"=16741453929 OR
>               "AG"."AD_GRP_ID"=16741454429 OR
> "AG"."AD_GRP_ID"=16741454929 OR
> "AG"."AD_GRP_ID"=16741455429 OR
>               "AG"."AD_GRP_ID"=16741455929 OR
> "AG"."AD_GRP_ID"=16741456429 OR
> "AG"."AD_GRP_ID"=16741456929 OR
>               "AG"."AD_GRP_ID"=16741457429 OR
> "AG"."AD_GRP_ID"=16741457929 OR
> "AG"."AD_GRP_ID"=16741458429 OR
>               "AG"."AD_GRP_ID"=16741458929 OR
> "AG"."AD_GRP_ID"=16741459429 OR
> "AG"."AD_GRP_ID"=16741459929 OR
>               "AG"."AD_GRP_ID"=16741460429 OR
> "AG"."AD_GRP_ID"=16741460929 OR
> "AG"."AD_GRP_ID"=16741461429 OR
>               "AG"."AD_GRP_ID"=16741461929 OR
> "AG"."AD_GRP_ID"=16741462429 OR
> "AG"."AD_GRP_ID"=16741462929 OR
>               "AG"."AD_GRP_ID"=16741463429 OR
> "AG"."AD_GRP_ID"=16741463929 OR
> "AG"."AD_GRP_ID"=16741464429 OR
>               "AG"."AD_GRP_ID"=16741464929 OR
> "AG"."AD_GRP_ID"=16741465429 OR
> "AG"."AD_GRP_ID"=16741465929 OR
>               "AG"."AD_GRP_ID"=16741466429 OR
> "AG"."AD_GRP_ID"=16741466929 OR
> "AG"."AD_GRP_ID"=16741467429 OR
>               "AG"."AD_GRP_ID"=16741467929 OR
> "AG"."AD_GRP_ID"=16741468429 OR
> "AG"."AD_GRP_ID"=16741468929 OR
>               "AG"."AD_GRP_ID"=16741469429 OR
> "AG"."AD_GRP_ID"=16741469929 OR
> "AG"."AD_GRP_ID"=16741470429 OR
>               "AG"."AD_GRP_ID"=16741470929 OR
> "AG"."AD_GRP_ID"=16741471429 OR
> "AG"."AD_GRP_ID"=16741471929 OR
>               "AG"."AD_GRP_ID"=16741472429 OR
> "AG"."AD_GRP_ID"=16741472929 OR
> "AG"."AD_GRP_ID"=16741473429 OR
>               "AG"."AD_GRP_ID"=16741473929 OR
> "AG"."AD_GRP_ID"=16741474429 OR
> "AG"."AD_GRP_ID"=16741474929 OR
>               "AG"."AD_GRP_ID"=16741475429 OR
> "AG"."AD_GRP_ID"=16741475929 OR
> "AG"."AD_GRP_ID"=16741476429 OR
>               "AG"."AD_GRP_ID"=16741476929 OR
> "AG"."AD_GRP_ID"=16741477429 OR
> "AG"."AD_GRP_ID"=16741477929 OR
>               "AG"."AD_GRP_ID"=16741478429 OR
> "AG"."AD_GRP_ID"=16741478929 OR
> "AG"."AD_GRP_ID"=16741479429 OR
>               "AG"."AD_GRP_ID"=16741479929 OR
> "AG"."AD_GRP_ID"=16741480429 OR
> "AG"."AD_GRP_ID"=16741480929 OR
>               "AG"."AD_GRP_ID"=16741481429 OR
> "AG"."AD_GRP_ID"=16741481929 OR
> "AG"."AD_GRP_ID"=16741482429 OR
>               "AG"."AD_GRP_ID"=16741482929 OR
> "AG"."AD_GRP_ID"=16741483429 OR
> "AG"."AD_GRP_ID"=16741483929 OR
>               "AG"."AD_GRP_ID"=16741484429 OR
> "AG"."AD_GRP_ID"=16741484929 OR
> "AG"."AD_GRP_ID"=16741485429 OR
>               "AG"."AD_GRP_ID"=16741485929 OR
> "AG"."AD_GRP_ID"=16741486429 OR
> "AG"."AD_GRP_ID"=16741486929 OR
>               "AG"."AD_GRP_ID"=16741487429 OR
> "AG"."AD_GRP_ID"=16741487929 OR
> "AG"."AD_GRP_ID"=16741488429 OR
>               "AG"."AD_GRP_ID"=16741488929 OR
> "AG"."AD_GRP_ID"=16741489429 OR
> "AG"."AD_GRP_ID"=16741489929 OR
>               "AG"."AD_GRP_ID"=16741490429 OR
> "AG"."AD_GRP_ID"=16741490929 OR
> "AG"."AD_GRP_ID"=16741491429 OR
>               "AG"."AD_GRP_ID"=16741491929 OR
> "AG"."AD_GRP_ID"=16741492429 OR
> "AG"."AD_GRP_ID"=16741492929 OR
>               "AG"."AD_GRP_ID"=16741493429 OR
> "AG"."AD_GRP_ID"=16741493929 OR
> "AG"."AD_GRP_ID"=16741494429 OR
>               "AG"."AD_GRP_ID"=16741494929 OR
> "AG"."AD_GRP_ID"=16741495429 OR
> "AG"."AD_GRP_ID"=16741495929 OR
>               "AG"."AD_GRP_ID"=16741496429 OR
> "AG"."AD_GRP_ID"=16741496929 OR
> "AG"."AD_GRP_ID"=16741497429 OR
>               "AG"."AD_GRP_ID"=16741497929 OR
> "AG"."AD_GRP_ID"=16741498429 OR
> "AG"."AD_GRP_ID"=16741498929 OR
>               "AG"."AD_GRP_ID"=16741499429 OR
> "AG"."AD_GRP_ID"=16741499929 OR
> "AG"."AD_GRP_ID"=16741500429 OR
>               "AG"."AD_GRP_ID"=16741500929 OR
> "AG"."AD_GRP_ID"=16741501429 OR
> "AG"."AD_GRP_ID"=16741501929 OR
>               "AG"."AD_GRP_ID"=16741502429 OR
> "AG"."AD_GRP_ID"=16741502929 O)
>    9 - access("T"."AD_GRP_ID"="AG"."AD_GRP_ID")
>
> Notice there is NO FILTER in Predicate information after step 9.
>
> Also the PARTITION HASH INLIST will change to PARTITION HASH ITERATOR.
>
> More than solution, I'm curious to know why in first place we were
> doing INDEX scan 788k.
>
> Oracle version: 10.2.0.4
> Linux zebra.mm.com 2.6.9-89.ELsmp #1 SMP Mon Apr 20 10:33:05 EDT 2009
> x86_64 x86_64 x86_64 GNU/Linux
> Both the tables TE_Y and AD_Y are hash partitioned by AD_GRP_ID. No of
> partitions 2048.
> Index info:
>
> SQL> _at_index
> Enter table name: TE_Y
>
> COLUMN_POSITION COLUMN_NAME                    INDEX_NAME
> --------------- ------------------------------
> ------------------------------
>               1 TERM_ID                        IDX_TE_Y1
>               1 ACCT_ID                        IDX_TE_Y3
>               2 URL_ID                         IDX_TE_Y3
>               1 AD_GRP_ID                      IDX_TE_Y_UQ1
>               2 CANON_SEARCH_TEXT              IDX_TE_Y_UQ1
>               3 DEL_TMSTMP                     IDX_TE_Y_UQ1
>
> 6 rows selected.
>
> INDEX_NAME                     PAR UNIQUENES   NUM_ROWS LEAF_BLOCKS
> DISTINCT_KEYS     BLEVEL CLUSTERING_FACTOR
> ------------------------------ --- --------- ---------- -----------
> ------------- ---------- -----------------
> IDX_TE_Y1                      YES NONUNIQUE  534607689     5011688     534603138          2
> 300072
> IDX_TE_Y3                      YES NONUNIQUE  566383554     3026998     113693664
> 2              1512625
> IDX_TE_Y_UQ1           YES UNIQUE     534035467     5803793     534035467
> 2               803940
>
> SQL> _at_index
> Enter table name: AD_Y
>
> COLUMN_POSITION COLUMN_NAME                    INDEX_NAME
> --------------- ------------------------------
> ------------------------------
>               1 CRTV_OPT_ENABLED_LAST_UPD      IDX_AD_Y2
>               1 LAST_CMPGN_OPT_UPD             IDX_AD_Y3
>               1 ACCT_ID                        IDX_AD_Y4
>               1 STATUS                         IDX_AD_Y6
>               2 SEARCH_ENABLED                 IDX_AD_Y6
>               3 CRTV_OPT_ENABLED               IDX_AD_Y6
>               4 NEXT_CRTV_OPT_CHK              IDX_AD_Y6
>               5 AD_GRP_ID                      IDX_AD_Y6
>               6 CMPGN_ID                       IDX_AD_Y6
>               1 STATUS                         IDX_AD_Y7
>               2 SEARCH_ENABLED                 IDX_AD_Y7
>               3 CRTV_OPT_ENABLED_LAST_UPD      IDX_AD_Y7
>               4 AD_GRP_ID                      IDX_AD_Y7
>               5 CMPGN_ID                       IDX_AD_Y7
>               1 CMPGN_ID                       IDX_AD_Y_UQ1
>               2 AD_GRP_NAME                    IDX_AD_Y_UQ1
>               3 DEL_TMSTMP                     IDX_AD_Y_UQ1
>               1 AD_GRP_ID                      PK_AD_Y
>
> 18 rows selected.
>
> INDEX_NAME                     PAR UNIQUENES   NUM_ROWS LEAF_BLOCKS
> DISTINCT_KEYS     BLEVEL CLUSTERING_FACTOR
> ------------------------------ --- --------- ---------- -----------
> ------------- ---------- -----------------
> IDX_AD_Y2              YES NONUNIQUE   24940544      102400      22863872
> 1                 9419
> IDX_AD_Y3              NO  NONUNIQUE      40959        2422          1718          2
> 39396
> IDX_AD_Y4              YES NONUNIQUE   32981324      134718        260963          2
> 206827
> IDX_AD_Y6              YES NONUNIQUE   31682560     2869248      31682560
> 2                12788
> IDX_AD_Y7              YES NONUNIQUE   31569920      301056      31569920
> 1                12259
> IDX_AD_Y_UQ1           YES UNIQUE      32983623      341326      32983623
> 2                57062
> PK_AD_Y                YES UNIQUE      31569920       75776      31569920          1
> 11922
>
> 7 rows selected.
>
> I understand that I may have missed some important information that
> could be necessary to come to some sort of conclusion.
> Please ask the same. I will revert back at the earliest.

That is a big post :)

I know this doesn't answer your question, but the only thing I would note is that you are forcing the PK index to be used with your hint, which may not be the best way if it going to have read the vast majority of the table, anyway. I would be interested to see how you used the USE_CONCAT hint, as I don't see the SQL? That will force everything in the list to be aggregated in a bunch of UNION queries, which may be a more brute force effective way to resolve the query rather than OR'ing each value using the PK.

What happens if you don't hint it at all? Received on Wed Dec 23 2009 - 12:49:18 CST

Original text of this message