Home » SQL & PL/SQL » SQL & PL/SQL » Analyze table problem in oracle
Analyze table problem in oracle [message #184425] Wed, 26 July 2006 09:16 Go to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
hi all,
this is quite interesting problem(atleast i believe)
that is,
we are having the query which is being constituted by joining more table and also having many filtering conditions in it.

the problem is when we run the query without analyze the table with compute statistics, we are getting few rows. but if we do analyze the table with compute statistics then we are getting 0 row. we are just wondering for the reason.
further after analyzing the table if we use
alter session set query_rewrite_enabled=true;
alter session set optimizer_index_cost_adj=1 ;

the above two statement, we will be getting the same result set as we got that few rows ( already got by without analyze table).

please find the plans generated in plan tables........

Before Analyze:
================

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               |  Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                              |     1 |   860 |       |   118  (49)|
|   1 |  SORT UNIQUE                            |                              |     1 |   860 |  1128K|   118  (49)|
|*  2 |   COUNT STOPKEY                         |                              |       |       |       |            |
|*  3 |    HASH JOIN                            |                              |   627 |   526K|       |   116  (48)|
|   4 |     TABLE ACCESS FULL                   | XPW_MTH_PGRP_D               |    23 |   368 |       |     2  (50)|
|*  5 |     HASH JOIN OUTER                     |                              |   327 |   269K|       |   114  (48)|
|*  6 |      HASH JOIN OUTER                    |                              |   327 |   222K|       |   111  (48)|
|*  7 |       HASH JOIN OUTER                   |                              |   327 |   174K|       |   106  (48)|
|*  8 |        HASH JOIN OUTER                  |                              |   327 |   127K|       |   101  (47)|
|*  9 |         HASH JOIN                       |                              |   327 | 82404 |       |    98  (47)|
|  10 |          TABLE ACCESS FULL              | XPW_MTH_SF_GEOG_L4           |    34 |   986 |       |     2  (50)|
|* 11 |          HASH JOIN                      |                              |   327 | 72921 |       |    96  (47)|
|  12 |           NESTED LOOPS                  |                              |   327 | 63111 |       |    93  (47)|
|  13 |            NESTED LOOPS                 |                              |   327 | 62130 |       |    90  (46)|
|  14 |             TABLE ACCESS FULL           | ZZT4Z0905X3OA00A             |   327 | 49704 |       |     4  (50)|
|  15 |             TABLE ACCESS BY INDEX ROWID | XPW_MTH_AMA_MP_D             |     1 |    38 |       |    90  (47)|
|  16 |              BITMAP CONVERSION TO ROWIDS|                              |       |       |       |            |
|* 17 |               BITMAP INDEX SINGLE VALUE | XIF1XPW_MTH_AMA_MP_D         |       |       |       |            |
|* 18 |            INDEX RANGE SCAN             | XPKXPW_MP_PDRP_REC_TYP_LKUP  |     1 |     3 |       |            |
|  19 |           TABLE ACCESS FULL             | XPW_MTH_PAYER_ORG_D          |  2353 | 70590 |       |     2  (50)|
|  20 |         TABLE ACCESS FULL               | ZZT4Z0905X3MD007             |    82 | 12136 |       |     3  (34)|
|  21 |        TABLE ACCESS FULL                | ZZT4Z0905X3MD006             |   327 | 48396 |       |     4  (50)|
|  22 |       TABLE ACCESS FULL                 | ZZT4Z0905X3MD005             |   327 | 48396 |       |     4  (50)|
|  23 |      TABLE ACCESS FULL                  | ZZT4Z0905X3MD004             |    82 | 12136 |       |     3  (34)|
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<2)
   3 - access("PA11"."PROD_FMLY_ID"="A13"."PROD_FMLY_ID")
   5 - access("PA11"."MP_AMA_PDRP_REC_TYP_CD"="PA5"."MP_AMA_PDRP_REC_TYP_CD"(+) AND
              "PA11"."MP_PRIM_CITY_NM"="PA5"."MP_PRIM_CITY_NM"(+) AND "PA11"."MP_PRIM_PSTL_CD"="PA5"."MP_PRIM_PSTL_CD"(+) AND
              "PA11"."NTILE_SEG_ID"="PA5"."NTILE_SEG_ID"(+) AND "PA11"."PAYER_ORG_ID"="PA5"."PAYER_ORG_ID"(+) AND
              "PA11"."PROD_FMLY_ID"="PA5"."PROD_FMLY_ID"(+) AND "PA11"."XPW_MP_ID"="PA5"."XPW_MP_ID1"(+) AND
              "PA11"."XPW_MP_ID0"="PA5"."XPW_MP_ID0"(+) AND "PA11"."XPW_MP_ID1"="PA5"."XPW_MP_ID"(+) AND
              "PA11"."XPW_NTILE_MKT_ID"="PA5"."XPW_NTILE_MKT_ID"(+) AND "PA11"."XPW_SF_GEOG_L4_ID"="PA5"."XPW_SF_GEOG_L4_ID"(+))
   6 - access("PA11"."MP_AMA_PDRP_REC_TYP_CD"="PA6"."MP_AMA_PDRP_REC_TYP_CD"(+) AND
              "PA11"."MP_PRIM_CITY_NM"="PA6"."MP_PRIM_CITY_NM"(+) AND "PA11"."MP_PRIM_PSTL_CD"="PA6"."MP_PRIM_PSTL_CD"(+) AND
              "PA11"."NTILE_SEG_ID"="PA6"."NTILE_SEG_ID"(+) AND "PA11"."PAYER_ORG_ID"="PA6"."PAYER_ORG_ID"(+) AND
              "PA11"."PROD_FMLY_ID"="PA6"."PROD_FMLY_ID"(+) AND "PA11"."XPW_MP_ID"="PA6"."XPW_MP_ID1"(+) AND
              "PA11"."XPW_MP_ID0"="PA6"."XPW_MP_ID0"(+) AND "PA11"."XPW_MP_ID1"="PA6"."XPW_MP_ID"(+) AND
              "PA11"."XPW_NTILE_MKT_ID"="PA6"."XPW_NTILE_MKT_ID"(+) AND "PA11"."XPW_SF_GEOG_L4_ID"="PA6"."XPW_SF_GEOG_L4_ID"(+))
   7 - access("PA11"."MKT_ID"="PA7"."MKT_ID"(+) AND "PA11"."MP_AMA_PDRP_REC_TYP_CD"="PA7"."MP_AMA_PDRP_REC_TYP_CD"(+)
               AND "PA11"."MP_PRIM_CITY_NM"="PA7"."MP_PRIM_CITY_NM"(+) AND "PA11"."MP_PRIM_PSTL_CD"="PA7"."MP_PRIM_PSTL_CD"(+) AND
              "PA11"."NTILE_SEG_ID"="PA7"."NTILE_SEG_ID"(+) AND "PA11"."PAYER_ORG_ID"="PA7"."PAYER_ORG_ID"(+) AND
              "PA11"."XPW_MP_ID"="PA7"."XPW_MP_ID1"(+) AND "PA11"."XPW_MP_ID0"="PA7"."XPW_MP_ID0"(+) AND
              "PA11"."XPW_MP_ID1"="PA7"."XPW_MP_ID"(+) AND "PA11"."XPW_NTILE_MKT_ID"="PA7"."XPW_NTILE_MKT_ID"(+) AND
              "PA11"."XPW_SF_GEOG_L4_ID"="PA7"."XPW_SF_GEOG_L4_ID"(+))
   8 - access("PA11"."MP_AMA_PDRP_REC_TYP_CD"="PA8"."MP_AMA_PDRP_REC_TYP_CD"(+) AND
              "PA11"."MP_PRIM_CITY_NM"="PA8"."MP_PRIM_CITY_NM"(+) AND "PA11"."MP_PRIM_PSTL_CD"="PA8"."MP_PRIM_PSTL_CD"(+) AND
              "PA11"."NTILE_SEG_ID"="PA8"."NTILE_SEG_ID"(+) AND "PA11"."PAYER_ORG_ID"="PA8"."PAYER_ORG_ID"(+) AND
              "PA11"."PROD_FMLY_ID"="PA8"."PROD_FMLY_ID"(+) AND "PA11"."XPW_MP_ID"="PA8"."XPW_MP_ID1"(+) AND
              "PA11"."XPW_MP_ID0"="PA8"."XPW_MP_ID0"(+) AND "PA11"."XPW_MP_ID1"="PA8"."XPW_MP_ID"(+) AND
              "PA11"."XPW_NTILE_MKT_ID"="PA8"."XPW_NTILE_MKT_ID"(+) AND "PA11"."XPW_SF_GEOG_L4_ID"="PA8"."XPW_SF_GEOG_L4_ID"(+))
   9 - access("PA11"."XPW_SF_GEOG_L4_ID"="A11"."XPW_RLTD_NODE4_GEOG_ID")
  11 - access("PA11"."PAYER_ORG_ID"="A14"."PAYER_ORG_ID")
  17 - access("PA11"."XPW_MP_ID"="A15"."AMA_MP_ID")
  18 - access("PA11"."MP_AMA_PDRP_REC_TYP_CD"="A12"."MP_AMA_PDRP_REC_TYP_CD")

63 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'



After Analyze
================


----------------------------------------------------------------------------------------------------------
| Id  | Operation                               |  Name                     | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                           |     1 |   538 |   126  (22)|
|   1 |  SORT UNIQUE                            |                           |     1 |   538 |   126  (22)|
|*  2 |   COUNT STOPKEY                         |                           |       |       |            |
|*  3 |    HASH JOIN                            |                           |     1 |   538 |   125  (21)|
|*  4 |     HASH JOIN                           |                           |     1 |   535 |   121  (20)|
|*  5 |      HASH JOIN                          |                           |     1 |   506 |   119  (20)|
|*  6 |       HASH JOIN                         |                           |     1 |   490 |   117  (19)|
|*  7 |        HASH JOIN OUTER                  |                           |     1 |   460 |   115  (19)|
|*  8 |         HASH JOIN OUTER                 |                           |     1 |   405 |   111  (18)|
|*  9 |          HASH JOIN OUTER                |                           |     1 |   351 |   106  (16)|
|* 10 |           HASH JOIN OUTER               |                           |     1 |   296 |   101  (13)|
|* 11 |            HASH JOIN                    |                           |     1 |   148 |    98  (13)|
|  12 |             TABLE ACCESS BY INDEX ROWID | XPW_MTH_AMA_MP_D          |     9 |   844 |    93  (11)|
|  13 |              BITMAP CONVERSION TO ROWIDS|                           |       |       |            |
|  14 |               BITMAP AND                |                           |       |       |            |
|  15 |                BITMAP MERGE             |                           |       |       |            |
|  16 |                 BITMAP KEY ITERATION    |                           |       |       |            |
|* 17 |                  COUNT STOPKEY          |                           |       |       |            |
|  18 |                   MERGE JOIN CARTESIAN  |                           |     1 |   232 |     9  (34)|
|  19 |                    MERGE JOIN CARTESIAN |                           |     1 |   203 |     6  (34)|
|  20 |                     TABLE ACCESS FULL   | ZZT4Z0905X3MD004          |     1 |   148 |     3  (34)|
|  21 |                     BUFFER SORT         |                           |   353 | 19415 |     3  (34)|
|  22 |                      TABLE ACCESS FULL  | ZZT4Z0905X3MD005          |   353 | 19415 |     4  (50)|
|  23 |                    BUFFER SORT          |                           |    34 |   986 |     5  (20)|
|  24 |                     TABLE ACCESS FULL   | XPW_MTH_SF_GEOG_L4        |    34 |   986 |     2  (50)|
|* 25 |                  BITMAP INDEX RANGE SCAN| XIF1XPW_MTH_AMA_MP_D      |       |       |            |
|  26 |                BITMAP MERGE             |                           |       |       |            |
|  27 |                 BITMAP KEY ITERATION    |                           |       |       |            |
|* 28 |                  COUNT STOPKEY          |                           |       |       |            |
|  29 |                   MERGE JOIN CARTESIAN  |                           |     1 |   231 |     8  (25)|
|  30 |                    MERGE JOIN CARTESIAN |                           |     1 |   202 |     6  (34)|
|  31 |                     TABLE ACCESS FULL   | ZZT4Z0905X3MD004          |     1 |   148 |     3  (34)|
|  32 |                     BUFFER SORT         |                           |   335 | 18090 |     3  (34)|
|  33 |                      TABLE ACCESS FULL  | ZZT4Z0905X3MD006          |   335 | 18090 |     4  (50)|
|  34 |                    BUFFER SORT          |                           |    34 |   986 |     4   (0)|
|  35 |                     TABLE ACCESS FULL   | XPW_MTH_SF_GEOG_L4        |    34 |   986 |     2  (50)|
|* 36 |                  BITMAP INDEX RANGE SCAN| XIF1XPW_MTH_AMA_MP_D      |       |       |            |
|  37 |             TABLE ACCESS FULL           | ZZT4Z0905X3OA00A          |   353 | 19415 |     4  (50)|
|  38 |            TABLE ACCESS FULL            | ZZT4Z0905X3MD004          |     1 |   148 |     3  (34)|
|  39 |           TABLE ACCESS FULL             | ZZT4Z0905X3MD005          |   353 | 19415 |     4  (50)|
|  40 |          TABLE ACCESS FULL              | ZZT4Z0905X3MD006          |   335 | 18090 |     4  (50)|
|  41 |         TABLE ACCESS FULL               | ZZT4Z0905X3MD007          |    99 |  5445 |     3  (34)|
|  42 |        TABLE ACCESS FULL                | XPW_MTH_PAYER_ORG_D       |  2353 | 70590 |     2  (50)|
|  43 |       TABLE ACCESS FULL                 | XPW_MTH_PGRP_D            |    23 |   368 |     2  (50)|
|  44 |      TABLE ACCESS FULL                  | XPW_MTH_SF_GEOG_L4        |    34 |   986 |     2  (50)|
|  45 |     TABLE ACCESS FULL                   | XPW_MP_PDRP_REC_TYP_LKUP  |    82 |   246 |     3  (34)|
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<2)
   3 - access("PA11"."MP_AMA_PDRP_REC_TYP_CD"="A12"."MP_AMA_PDRP_REC_TYP_CD")
   4 - access("PA11"."XPW_SF_GEOG_L4_ID"="A11"."XPW_RLTD_NODE4_GEOG_ID")
   5 - access("PA11"."PROD_FMLY_ID"="A13"."PROD_FMLY_ID")
   6 - access("PA11"."PAYER_ORG_ID"="A14"."PAYER_ORG_ID")
   7 - access("PA11"."MP_AMA_PDRP_REC_TYP_CD"="PA8"."MP_AMA_PDRP_REC_TYP_CD"(+) AND
              "PA11"."MP_PRIM_CITY_NM"="PA8"."MP_PRIM_CITY_NM"(+) AND
              "PA11"."MP_PRIM_PSTL_CD"="PA8"."MP_PRIM_PSTL_CD"(+) AND "PA11"."NTILE_SEG_ID"="PA8"."NTILE_SEG_ID"(+) AND
              "PA11"."PAYER_ORG_ID"="PA8"."PAYER_ORG_ID"(+) AND "PA11"."PROD_FMLY_ID"="PA8"."PROD_FMLY_ID"(+) AND
              "PA11"."XPW_MP_ID"="PA8"."XPW_MP_ID1"(+) AND "PA11"."XPW_MP_ID0"="PA8"."XPW_MP_ID0"(+) AND
              "PA11"."XPW_MP_ID1"="PA8"."XPW_MP_ID"(+) AND "PA11"."XPW_NTILE_MKT_ID"="PA8"."XPW_NTILE_MKT_ID"(+) AND
              "PA11"."XPW_SF_GEOG_L4_ID"="PA8"."XPW_SF_GEOG_L4_ID"(+))
   8 - access("PA11"."MKT_ID"="PA7"."MKT_ID"(+) AND "PA11"."MP_AMA_PDRP_REC_TYP_CD"="PA7"."MP_AMA_PDRP_REC
              _TYP_CD"(+) AND "PA11"."MP_PRIM_CITY_NM"="PA7"."MP_PRIM_CITY_NM"(+) AND
              "PA11"."MP_PRIM_PSTL_CD"="PA7"."MP_PRIM_PSTL_CD"(+) AND "PA11"."NTILE_SEG_ID"="PA7"."NTILE_SEG_ID"(+) AND
              "PA11"."PAYER_ORG_ID"="PA7"."PAYER_ORG_ID"(+) AND "PA11"."XPW_MP_ID"="PA7"."XPW_MP_ID1"(+) AND
              "PA11"."XPW_MP_ID0"="PA7"."XPW_MP_ID0"(+) AND "PA11"."XPW_MP_ID1"="PA7"."XPW_MP_ID"(+) AND
              "PA11"."XPW_NTILE_MKT_ID"="PA7"."XPW_NTILE_MKT_ID"(+) AND
              "PA11"."XPW_SF_GEOG_L4_ID"="PA7"."XPW_SF_GEOG_L4_ID"(+))
   9 - access("PA11"."MP_AMA_PDRP_REC_TYP_CD"="PA6"."MP_AMA_PDRP_REC_TYP_CD"(+) AND
              "PA11"."MP_PRIM_CITY_NM"="PA6"."MP_PRIM_CITY_NM"(+) AND
              "PA11"."MP_PRIM_PSTL_CD"="PA6"."MP_PRIM_PSTL_CD"(+) AND "PA11"."NTILE_SEG_ID"="PA6"."NTILE_SEG_ID"(+) AND
              "PA11"."PAYER_ORG_ID"="PA6"."PAYER_ORG_ID"(+) AND "PA11"."PROD_FMLY_ID"="PA6"."PROD_FMLY_ID"(+) AND
              "PA11"."XPW_MP_ID"="PA6"."XPW_MP_ID1"(+) AND "PA11"."XPW_MP_ID0"="PA6"."XPW_MP_ID0"(+) AND
              "PA11"."XPW_MP_ID1"="PA6"."XPW_MP_ID"(+) AND "PA11"."XPW_NTILE_MKT_ID"="PA6"."XPW_NTILE_MKT_ID"(+) AND
              "PA11"."XPW_SF_GEOG_L4_ID"="PA6"."XPW_SF_GEOG_L4_ID"(+))
  10 - access("PA11"."MP_AMA_PDRP_REC_TYP_CD"="PA5"."MP_AMA_PDRP_REC_TYP_CD"(+) AND
              "PA11"."MP_PRIM_CITY_NM"="PA5"."MP_PRIM_CITY_NM"(+) AND
              "PA11"."MP_PRIM_PSTL_CD"="PA5"."MP_PRIM_PSTL_CD"(+) AND "PA11"."NTILE_SEG_ID"="PA5"."NTILE_SEG_ID"(+) AND
              "PA11"."PAYER_ORG_ID"="PA5"."PAYER_ORG_ID"(+) AND "PA11"."PROD_FMLY_ID"="PA5"."PROD_FMLY_ID"(+) AND
              "PA11"."XPW_MP_ID"="PA5"."XPW_MP_ID1"(+) AND "PA11"."XPW_MP_ID0"="PA5"."XPW_MP_ID0"(+) AND
              "PA11"."XPW_MP_ID1"="PA5"."XPW_MP_ID"(+) AND "PA11"."XPW_NTILE_MKT_ID"="PA5"."XPW_NTILE_MKT_ID"(+) AND
              "PA11"."XPW_SF_GEOG_L4_ID"="PA5"."XPW_SF_GEOG_L4_ID"(+))
  11 - access("PA11"."XPW_MP_ID"="A15"."AMA_MP_ID")
  17 - filter(ROWNUM<2)
  25 - access("A15"."AMA_MP_ID"="PA6"."XPW_MP_ID1"(+))
  28 - filter(ROWNUM<2)
  36 - access("A15"."AMA_MP_ID"="PA7"."XPW_MP_ID1"(+))

93 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY'



do anybody have any idea that what could be the reason???

Thanks,
Thangam

[Updated on: Wed, 26 July 2006 09:28] by Moderator

Report message to a moderator

Re: Analyze table problem in oracle [message #184435 is a reply to message #184425] Wed, 26 July 2006 09:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> but if we do analyze the table with compute statistics then we are getting 0 row
I fail to understand.
With reference to your own post, the second output shows
>>93 rows selected.

Should'nt you be using dbms_stats?
Re: Analyze table problem in oracle [message #184439 is a reply to message #184425] Wed, 26 July 2006 09:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I too am having difficulty understanding what you are saying.
If it is this:

Quote:

We have unanalyzed tables.

We execute a query and the query returns a set of data.

We then analyze the tables

We re-execute the same query, and the query returns a different set of data

Then you have found a bug. Put together a test case, and raise a SR with metalink.

If what you are saying is this, however:
Quote:

We have unanalyzed tables.

We do an explain plan on a query and the plan estimates a number of rows to be returned.

We then analyze the tables

We re-explain the same query, and the plan estimates a different number of rows

Then this is entirely to be expected. You have provided the optimiser with more information, and it has changed its view of the expected outcome.
Re: Analyze table problem in oracle [message #184524 is a reply to message #184439] Wed, 26 July 2006 21:57 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Something interesting has happended here. Note in the second EXPLAIN PLAN that there are 3 STOPKEY events, yet your SQL (assuming it is the same as the one that generated the first plan) contains only one ROWNUM < 2.

Also note that the two innermost STOPKEY events are combined in a BTIMAP AND. So, if we select 1 row via one bitmap index (the first row that matches "condition 1"), and 1 row via a different bitmap index (the first row that matches "condition 2"), then AND them, we will get NOTHING unless by chance the first row matching "condition 1" ALSO matches "condition 2".

It seems that the optimizer has made a blunder by pushing the ROWNUM<2 predicate deeper into the plan than it has any right to do. If you take out the ROWNUM < 2, you will almost certainly get the same result from both queries.

Ross Leishman
Re: Analyze table problem in oracle [message #184557 is a reply to message #184524] Thu, 27 July 2006 01:07 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member

let me give you our problem as simple as possible.
if we run th query(Before Analyze the table), we are getting few rows. however if we run the same query after analyze the table we are getting 0 rows.

for example let us assume that the query is select empno from employee(but my actual query contain combinations of many tables and filtering conditions)

So the exact problem is that...

select empno from employee;
==> it returns few rows(here i did not anlyze the table).

but
analyze table employee estimate statistics;
==>we analyzed all the tables involved in our big query.
after this if we run the same sql like
select empno from employee;
0 rows selected.

i hope the problem is clear now.

we are just wondering why is this contradiction between the result after and before analyze table in oracle!!!!

Ross Leishman: we have just used rownum inorder to limit the rows because it would return many rows...

please find the actual query here

<code>
select distinct pa11.PAYER_ORG_ID PAYER_ORG_ID,
a14.PAYER_ORG_NM PAYER_ORG_NM,
pa11.XPW_SF_GEOG_L4_ID XPW_SF_GEOG_L4_ID,
a11.RLTD_NODE4_NM XPW_SF_GEOG_L4_NM,
a11.RLTD_NODE4_LVL_DESC XPW_SF_GEOG_L4_LVL_DESC,
pa11.XPW_NTILE_MKT_ID XPW_NTILE_MKT_ID,
pa11.NTILE_SEG_ID NTILE_SEG_ID,
pa11.MP_AMA_PDRP_REC_TYP_CD MP_AMA_PDRP_REC_TYP_CD,
Case When a12.MP_AMA_PDRP_REC_TYP_CD = 'N' Then ' ' Else 'Y' End MP_AMA_PDRP_REC_TYP_DESC,
pa11.XPW_MP_ID0 XPW_MP_ID,
a15.MP_FRST_NM MP_FRST_NM,
pa11.XPW_MP_ID1 XPW_MP_ID0,
a15.MP_LAST_NM MP_LAST_NM,
pa11.XPW_MP_ID XPW_MP_ID1,
a15.MP_PRIM_CONCATD_STR_ADDR_TXT MP_PRIM_CONCATD_STR_ADDR_TXT,
pa11.MP_PRIM_CITY_NM MP_PRIM_CITY_NM,
pa11.MP_PRIM_PRVN_ST_CD MP_PRIM_PRVN_ST_CD,
pa11.MP_PRIM_PSTL_CD MP_PRIM_PSTL_CD,
pa11.PROD_FMLY_ID PROD_FMLY_ID,
a13.PROD_FMLY_NM PROD_FMLY_NM,
pa5.WJXBFS1 WJXBFS1,
NVL((pa6.TRX_CNT_MATTY / (CASE WHEN pa7.TRX_MKT_CNT_MATY=0 THEN NULL ELSE pa7.TRX_MKT_CNT_MATY END)), 0) TRX_MKT_SHR_MATY,
pa6.TRX_CNT_MATTY TRX_CNT_MATTY,
pa7.TRX_MKT_CNT_MATY TRX_MKT_CNT_MATY,
NVL(((NVL(pa6.TRX_CNT_MATTY, 0) - NVL(pa8.TRX_CNT_MATTY, 0)) / (CASE WHEN pa8.TRX_CNT_MATTY=0 THEN NULL ELSE pa8.TRX_CNT_MATTY END)), 0) TRX_GRO_MATTY_LY
from ZZT4Z0905X3OA00A pa11,
ZZT4Z0905X3MD004 pa5,
ZZT4Z0905X3MD005 pa6,
ZZT4Z0905X3MD006 pa7,
ZZT4Z0905X3MD007 pa8,
XPW_MTH_SF_GEOG_L4 a11,
XPW_MP_PDRP_REC_TYP_LKUP a12,
XPW_MTH_PGRP_D a13,
XPW_MTH_PAYER_ORG_D a14,
XPW_MTH_AMA_MP_D a15
where pa11.MP_AMA_PDRP_REC_TYP_CD = pa5.MP_AMA_PDRP_REC_TYP_CD (+) and
pa11.MP_PRIM_CITY_NM = pa5.MP_PRIM_CITY_NM (+) and
pa11.MP_PRIM_PSTL_CD = pa5.MP_PRIM_PSTL_CD (+) and
pa11.NTILE_SEG_ID = pa5.NTILE_SEG_ID (+) and
pa11.PAYER_ORG_ID = pa5.PAYER_ORG_ID (+) and
pa11.PROD_FMLY_ID = pa5.PROD_FMLY_ID (+) and
pa11.XPW_MP_ID = pa5.XPW_MP_ID1 (+) and
pa11.XPW_MP_ID0 = pa5.XPW_MP_ID0 (+) and
pa11.XPW_MP_ID1 = pa5.XPW_MP_ID (+) and
pa11.XPW_NTILE_MKT_ID = pa5.XPW_NTILE_MKT_ID (+) and
pa11.XPW_SF_GEOG_L4_ID = pa5.XPW_SF_GEOG_L4_ID (+) and
pa11.MP_AMA_PDRP_REC_TYP_CD = pa6.MP_AMA_PDRP_REC_TYP_CD (+) and
pa11.MP_PRIM_CITY_NM = pa6.MP_PRIM_CITY_NM (+) and
pa11.MP_PRIM_PSTL_CD = pa6.MP_PRIM_PSTL_CD (+) and
pa11.NTILE_SEG_ID = pa6.NTILE_SEG_ID (+) and
pa11.PAYER_ORG_ID = pa6.PAYER_ORG_ID (+) and
pa11.PROD_FMLY_ID = pa6.PROD_FMLY_ID (+) and
pa11.XPW_MP_ID = pa6.XPW_MP_ID1 (+) and
pa11.XPW_MP_ID0 = pa6.XPW_MP_ID0 (+) and
pa11.XPW_MP_ID1 = pa6.XPW_MP_ID (+) and
pa11.XPW_NTILE_MKT_ID = pa6.XPW_NTILE_MKT_ID (+) and
pa11.XPW_SF_GEOG_L4_ID = pa6.XPW_SF_GEOG_L4_ID (+) and
pa11.MKT_ID = pa7.MKT_ID (+) and
pa11.MP_AMA_PDRP_REC_TYP_CD = pa7.MP_AMA_PDRP_REC_TYP_CD (+) and
pa11.MP_PRIM_CITY_NM = pa7.MP_PRIM_CITY_NM (+) and
pa11.MP_PRIM_PSTL_CD = pa7.MP_PRIM_PSTL_CD (+) and
pa11.NTILE_SEG_ID = pa7.NTILE_SEG_ID (+) and
pa11.PAYER_ORG_ID = pa7.PAYER_ORG_ID (+) and
pa11.XPW_MP_ID = pa7.XPW_MP_ID1 (+) and
pa11.XPW_MP_ID0 = pa7.XPW_MP_ID0 (+) and
pa11.XPW_MP_ID1 = pa7.XPW_MP_ID (+) and
pa11.XPW_NTILE_MKT_ID = pa7.XPW_NTILE_MKT_ID (+) and
pa11.XPW_SF_GEOG_L4_ID = pa7.XPW_SF_GEOG_L4_ID (+) and
pa11.MP_AMA_PDRP_REC_TYP_CD = pa8.MP_AMA_PDRP_REC_TYP_CD (+) and
pa11.MP_PRIM_CITY_NM = pa8.MP_PRIM_CITY_NM (+) and
pa11.MP_PRIM_PSTL_CD = pa8.MP_PRIM_PSTL_CD (+) and
pa11.NTILE_SEG_ID = pa8.NTILE_SEG_ID (+) and
pa11.PAYER_ORG_ID = pa8.PAYER_ORG_ID (+) and
pa11.PROD_FMLY_ID = pa8.PROD_FMLY_ID (+) and
pa11.XPW_MP_ID = pa8.XPW_MP_ID1 (+) and
pa11.XPW_MP_ID0 = pa8.XPW_MP_ID0 (+) and
pa11.XPW_MP_ID1 = pa8.XPW_MP_ID (+) and
pa11.XPW_NTILE_MKT_ID = pa8.XPW_NTILE_MKT_ID (+) and
pa11.XPW_SF_GEOG_L4_ID = pa8.XPW_SF_GEOG_L4_ID (+) and
pa11.XPW_SF_GEOG_L4_ID = a11.XPW_RLTD_NODE4_GEOG_ID and
pa11.MP_AMA_PDRP_REC_TYP_CD = a12.MP_AMA_PDRP_REC_TYP_CD and
pa11.PROD_FMLY_ID = a13.PROD_FMLY_ID and
pa11.PAYER_ORG_ID = a14.PAYER_ORG_ID and
pa11.XPW_MP_ID = a15.AMA_MP_ID
</code>

this is what we did to estimate the statistics.
<code>
analyze table ZZT4Z0905X3AM000 estimate statistics;
analyze table ZZT4Z0905X3MQ001 estimate statistics;
analyze table ZZT4Z0905X3AM002 estimate statistics;
analyze table ZZT4Z0905X3MQ003 estimate statistics;
analyze table ZZT4Z0905X3MD004 estimate statistics;
analyze table ZZT4Z0905X3MD005 estimate statistics ;
analyze table ZZT4Z0905X3MD006 estimate statistics ;
analyze table ZZT4Z0905X3MD007 estimate statistics ;
analyze table ZZT4Z0905X3OJ008 estimate statistics ;
analyze table ZZT4Z0905X3OD009 estimate statistics;
analyze table ZZT4Z0905X3OA00A estimate statistics;
</code>
Re: Analyze table problem in oracle [message #184571 is a reply to message #184557] Thu, 27 July 2006 02:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That is a bug with Oracle.
The optimizer should affect how rows are retrieved, not how many rows are retrieved.

You need to talk to Oracle Support about this one.


Out of curiosity, do you still get the problem happening when you remove the rownum clause?
Re: Analyze table problem in oracle [message #184599 is a reply to message #184571] Thu, 27 July 2006 03:34 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
yes. as i mentioned earlier we just added rownum only to limit the row while print in screen
Re: Analyze table problem in oracle [message #184615 is a reply to message #184557] Thu, 27 July 2006 04:15 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
As said before,
You are supposed to use DBMS_STATS with cascade option with a decent bucketsize (Unless you are using Oracle 7.3 or less).
Try with DBMS_STATS and post the results.
Re: Analyze table problem in oracle [message #184645 is a reply to message #184615] Thu, 27 July 2006 05:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Doesn't matter - they've still found a bug.
Re: Analyze table problem in oracle [message #184841 is a reply to message #184645] Fri, 28 July 2006 05:11 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
the problem here is that the query is generated by Microstragy(It is just reporting tool. it will build the query internally). so we dont have any control over there to generete different type of query to achieve identical result.
so we cannot use dbms_stat instead of analyze table.

Thanks,
Thangam
Re: Analyze table problem in oracle [message #184842 is a reply to message #184841] Fri, 28 July 2006 05:18 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yes you can.
DBMS_STATS and ANALYZE are both commands issued to the database.
They have nothing to do with where the query is generated from.


Assuming you have access to the schema, you can use DBMS_STATS to generate a better set of statistics than ANALYZE will give you, and assuming you have access to the DBA, you an get him to change how he regenerates the stats.
Previous Topic: stored procedures
Next Topic: Update With Join
Goto Forum:
  


Current Time: Mon Dec 05 12:55:31 CST 2016

Total time taken to generate the page: 0.07438 seconds