This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 22, 2011 4:46 AM by 906492 Go to original post RSS
  • 15. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    Hi Randolf,

    yes that's what I realized, as well! you are right!
    Following the explain plan for the 10.2.0.4 version: sorry for the format!
    The table that is accessed by the rowid is the same than in 11.2.0.2. when using the subselect with dual: TBS_GSFT_OBJ_BZHNG_HRCH
    When executing the statement in 11.2.0.2 without any a subselect, the table TBS_VRZNG_ENHT will be accessed by rowid

    SQL> set linesize 200 pagesize 0 tab off
    SQL> SELECT * FROM TABLE(dbms_xplan.display);
    Plan hash value: 1841995506

    -------------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 109 | 3 (0)| 00:00:01 |
    | 1 | NESTED LOOPS OUTER | | 1 | 109 | 3 (0)| 00:00:01 |
    | 2 | NESTED LOOPS OUTER | | 1 | 104 | 3 (0)| 00:00:01 |
    | 3 | NESTED LOOPS OUTER | | 1 | 99 | 3 (0)| 00:00:01 |
    | 4 | NESTED LOOPS OUTER | | 1 | 94 | 3 (0)| 00:00:01 |
    | 5 | NESTED LOOPS | | 1 | 89 | 3 (0)| 00:00:01 |
    | 6 | NESTED LOOPS OUTER | | 1 | 47 | 2 (0)| 00:00:01 |
    | 7 | NESTED LOOPS | | 1 | 44 | 2 (0)| 00:00:01 |
    | 8 | TABLE ACCESS BY USER ROWID| TBS_GSFT_OBJ_BZHNG_HRCH | 1 | 15 | 1 (0)| 00:00:01 |
    |* 9 | INDEX RANGE SCAN | IDX_GSFT_OBJ_MANY1 | 1 | 29 | 1 (0)| 00:00:01 |
    |* 10 | INDEX UNIQUE SCAN | CPK_BNTZR | 1 | 3 | 0 (0)| 00:00:01 |
    | 11 | TABLE ACCESS BY INDEX ROWID | TBS_VRZNG_ENHT | 1 | 42 | 1 (0)| 00:00:01 |
    |* 12 | INDEX UNIQUE SCAN | CPK_VRZNG_ENHT | 1 | | 0 (0)| 00:00:01 |
    |* 13 | INDEX UNIQUE SCAN | CPK_GSFT_OBJ_SUB_TYP_ID | 1 | 5 | 0 (0)| 00:00:01 |
    |* 14 | INDEX UNIQUE SCAN | CPK_GSFT_OBJ_TYP | 1 | 5 | 0 (0)| 00:00:01 |
    |* 15 | INDEX UNIQUE SCAN | CPK_FRMLR | 1 | 5 | 0 (0)| 00:00:01 |
    |* 16 | INDEX UNIQUE SCAN | CPK_FRMLR | 1 | 5 | 0 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------

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

    9 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")
    10 - access("G"."CHECK_OUT_BNTZR_ID"="B"."BNTZR_ID"(+))
    12 - access("G"."GSFT_OBJ_ID"="VRZNG_ENHT_ID")
    13 - access("G"."GSFT_OBJ_SUB_TYP_ID"="GOST"."GSFT_OBJ_SUB_TYP_ID"(+))
    14 - access("G"."GSFT_OBJ_TYP_ID"="GOT"."GSFT_OBJ_TYP_ID"(+))
    15 - access("G"."BRBTG_FRMLR_ID"="F2"."FRMLR_ID"(+))
    16 - access("G"."ANST_FRMLR_ID"="F"."FRMLR_ID"(+))

    34 Zeilen ausgewõhlt.

    Following the definition of the view vws_vrzng_enht_haupt_sys:

    CREATE OR REPLACE FORCE VIEW APLKN_ARCHV_STASG.VWS_VRZNG_ENHT_HAUPT_SYS
    (
    VRZNG_ENHT_ID,
    GSFT_OBJ_KURZ_NM,
    AKTIV_IND,
    ANST_FRMLR_ID,
    BRBTG_FRMLR_ID,
    VRZNG_ENHT_CHECK_OUT_BNTZR_ID,
    VRZNG_ENHT_TYP_ID,
    VRZNG_ENHT_SUB_TYP_ID,
    VRZNG_ENHT_TYP_NM,
    VRZNG_ENHT_SUB_TYP_NM,
    ERFSG_DT,
    ERFSG_USR_CD,
    MTTN_DT,
    MTTN_USR_CD,
    TST,
    VRZNG_ENHT_BNTZB_ID,
    VRZNG_ENHT_BRBTG_STTS_ID,
    VRZNG_ENHT_BWLG_TYP_ID,
    VRZNG_ENHT_ENTRG_TYP_ID,
    VRZNG_ENHT_INHLT_ID,
    VRZNG_ENHT_SCHTZ_FRIST_ID,
    VRZNG_ENHT_ZGNGL_ID,
    SGNTR_CD,
    SGNTR_ARCHV_PLAN_CD,
    VRZNG_ENHT_TITEL,
    ARCHV_STAND_ORT_TXT,
    BNTZG_HNWS_TXT,
    DARIN_TXT,
    ERSLS_GRAD_TXT,
    ORDNG_KMPNT_TXT,
    SORT_FELD_TXT,
    TYP_TXT,
    SCHTZ_FRIST_BIS_DT,
    SCHTZ_FRIST_DAUER,
    SCHTZ_FRIST_NTZ,
    BGN_DT,
    END_DT,
    ZT_RAUM_TXT,
    MTRL_BGN_DT,
    MTRL_END_DT,
    MTRL_ZT_RAUM_TXT,
    BILD_ANST_IND,
    BILD_VRSCH_IND,
    BLKRT_IND,
    FIND_MTL_IND,
    SCHTZ_FRIST_MIN_IND,
    SCHTZ_FRIST_MTTN_IND,
    SUCH_FRGB_IND,
    VRZNG_ENHT_ENTRG_TYP_NM,
    VRZNG_ENHT_ENTRG_TYP_BSR,
    VRZNG_ENHT_BSTLG_IND,
    VRZNG_ENHT_BRBTG_STTS_NM,
    VRZNG_ENHT_BRBTG_STTS_BSR,
    VRZNG_ENHT_BWLG_TYP_NM,
    VRZNG_ENHT_BWLG_TYP_BSR,
    VRZNG_ENHT_SCHTZ_FRIST_NM,
    VRZNG_ENHT_SCHTZ_FRIST_BSR,
    VRZNG_ENHT_ZGNGL_NM,
    VRZNG_ENHT_ZGNGL_BSR,
    VRZNG_ENHT_BNTZB_NM,
    VRZNG_ENHT_BNTZB_BSR,
    VRZNG_ENHT_INHLT_NM,
    VRZNG_ENHT_INHLT_BSR,
    BLKRT_VON_BNTZR_CD,
    BLKRT_VON_BNTZR_NM,
    ANST_FRMLR_NM,
    BRBTG_FRMLR_NM,
    HRCH_PFAD,
    VRFGB_IND
    )
    AS
    SELECT ve.vrzng_enht_id,
    g.gsft_obj_kurz_nm,
    g.aktiv_ind,
    g.anst_frmlr_id,
    g.brbtg_frmlr_id,
    g.check_out_bntzr_id AS vrzng_enht_check_out_bntzr_id,
    g.gsft_obj_typ_id AS vrzng_enht_typ_id,
    gost.gsft_obj_sub_typ_id AS vrzng_enht_sub_typ_id,
    got.gsft_obj_typ_nm AS vrzng_enht_typ_nm,
    gost.gsft_obj_sub_typ_nm AS vrzng_enht_sub_typ_nm,
    g.erfsg_dt,
    g.erfsg_usr_cd,
    g.mttn_dt,
    g.mttn_usr_cd,
    g.tst,
    ve.vrzng_enht_bntzb_id,
    ve.vrzng_enht_brbtg_stts_id,
    ve.vrzng_enht_bwlg_typ_id,
    ve.vrzng_enht_entrg_typ_id,
    ve.vrzng_enht_inhlt_id,
    ve.vrzng_enht_schtz_frist_id,
    ve.vrzng_enht_zgngl_id,
    ve.sgntr_cd,
    ve.sgntr_archv_plan_cd,
    ve.vrzng_enht_titel,
    ve.archv_stand_ort_txt,
    ve.bntzg_hnws_txt,
    ve.darin_txt,
    ve.ersls_grad_txt,
    ve.ordng_kmpnt_txt,
    ve.sort_feld_txt,
    ve.typ_txt,
    ve.schtz_frist_bis_dt,
    ve.schtz_frist_dauer,
    ve.schtz_frist_ntz,
    ve.bgn_dt,
    ve.end_dt,
    ve.zt_raum_txt,
    ve.mtrl_bgn_dt,
    ve.mtrl_end_dt,
    ve.mtrl_zt_raum_txt,
    ve.bild_anst_ind,
    ve.bild_vrsch_ind,
    ve.blkrt_ind,
    ve.find_mtl_ind,
    ve.schtz_frist_min_ind,
    ve.schtz_frist_mttn_ind,
    ve.such_frgb_ind,
    et.entrg_typ_nm AS vrzng_enht_entrg_typ_nm,
    et.entrg_typ_bsr AS vrzng_enht_entrg_typ_bsr,
    et.vrzng_enht_bstlg_ind,
    vebs.vrzng_enht_brbtg_stts_nm,
    vebs.vrzng_enht_brbtg_stts_bsr,
    vebt.vrzng_enht_bwlg_typ_nm,
    vebt.vrzng_enht_bwlg_typ_bsr,
    vesf.vrzng_enht_schtz_frist_nm,
    vesf.vrzng_enht_schtz_frist_bsr,
    vez.vrzng_enht_zgngl_nm,
    vez.vrzng_enht_zgngl_bsr,
    veb.vrzng_enht_bntzb_nm,
    veb.vrzng_enht_bntzb_bsr,
    vei.vrzng_enht_inhlt_nm,
    vei.vrzng_enht_inhlt_bsr,
    b.bntzr_cd AS blkrt_von_bntzr_cd,
    b.bntzr_nm AS blkrt_von_bntzr_nm,
    f.frmlr_nm AS anst_frmlr_nm,
    f2.frmlr_nm AS brbtg_frmlr_nm,
    gobh.hrch_pfad,
    DECODE (pks_gsft_obj_rsrvg.is_gsft_obj_blkrt (ve.vrzng_enht_id),
    1, 0,
    1)
    AS vrfgb_ind
    FROM vwr_vrzng_enht ve,
    tbs_gsft_obj g,
    tbs_gsft_obj_bzhng_hrch gobh,
    tbs_gsft_obj_typ got,
    tbs_entrg_typ et,
    tbs_vrzng_enht_brbtg_stts vebs,
    tbs_vrzng_enht_bwlg_typ vebt,
    tbs_vrzng_enht_schtz_frist vesf,
    tbs_vrzng_enht_zgngl vez,
    tbs_vrzng_enht_bntzb veb,
    tbs_vrzng_enht_inhlt vei,
    tba_bntzr b,
    tbs_gsft_obj_sub_typ gost,
    tbs_frmlr f,
    tbs_frmlr f2
    WHERE g.gsft_obj_id = ve.vrzng_enht_id
    AND gobh.gsft_obj_2_id = g.gsft_obj_id
    AND ve.vrzng_enht_entrg_typ_id = et.entrg_typ_id
    AND ve.vrzng_enht_brbtg_stts_id = vebs.vrzng_enht_brbtg_stts_id
    AND ve.vrzng_enht_bwlg_typ_id = vebt.vrzng_enht_bwlg_typ_id
    AND ve.vrzng_enht_schtz_frist_id = vesf.vrzng_enht_schtz_frist_id
    AND ve.vrzng_enht_zgngl_id = vez.vrzng_enht_zgngl_id
    AND ve.vrzng_enht_bntzb_id = veb.vrzng_enht_bntzb_id
    AND ve.vrzng_enht_inhlt_id = vei.vrzng_enht_inhlt_id
    AND g.gsft_obj_typ_id = got.gsft_obj_typ_id(+)
    AND g.check_out_bntzr_id = b.bntzr_id(+)
    AND g.gsft_obj_sub_typ_id = gost.gsft_obj_sub_typ_id(+)
    AND g.anst_frmlr_id = f.frmlr_id(+)
    AND g.brbtg_frmlr_id = f2.frmlr_id(+)
    ;

    thanks again for your efforts you've already put so far in this problem!
    best regards Stefa
  • 16. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    hi Randolf,

    the problem seems to be solved: after replacing the View vwr_vrzng_enht with the direct underlying table of that view tbs_vrzng_enht the optimizer accesses the table TBS_GSFT_OBJ_BZHNG_HRCH by the ROWID like on the 10.2.0.4 version.


    SQL> SELECT * FROM TABLE(dbms_xplan.display);
    Plan hash value: 4081851611

    --------------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 39 | 2 (0)| 00:00:01 |
    | 1 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
    | 2 | NESTED LOOPS | | 1 | 18 | 1 (0)| 00:00:01 |
    | 3 | TABLE ACCESS BY USER ROWID| TBS_GSFT_OBJ_BZHNG_HRCH | 1 | 15 | 1 (0)| 00:00:01 |
    |* 4 | INDEX UNIQUE SCAN | CPK_GSFT_OBJ | 1 | 3 | 0 (0)| 00:00:01 |
    | 5 | TABLE ACCESS BY INDEX ROWID| TBS_VRZNG_ENHT | 1 | 21 | 1 (0)| 00:00:01 |
    |* 6 | INDEX UNIQUE SCAN | CPK_VRZNG_ENHT | 1 | | 0 (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------------------

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

    4 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")
    6 - access("G"."GSFT_OBJ_ID"="VE"."VRZNG_ENHT_ID")

    19 Zeilen ausgewõhlt.

    SQL> select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    2 where s.rowid='AAASN0AAFAAACBUAAB';
    AAASN0AAFAAACBUAAB
    Kanton Z³rich


    Thanks a lot for your support on that
    Regards Stefan
  • 17. Re: ORA-01410 when selecting data via ROWID
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    Just put the word "code" in curly brackets {noformat}{{noformat}code{noformat}}{noformat} before and after your code to have it formatted in fixed font.
    SQL> set linesize 200 pagesize 0 tab off
    SQL> SELECT * FROM TABLE(dbms_xplan.display);
    Plan hash value: 1841995506
    
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation                         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                  |                         |     1 |   109 |     3   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS OUTER               |                         |     1 |   109 |     3   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS OUTER              |                         |     1 |   104 |     3   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS OUTER             |                         |     1 |    99 |     3   (0)| 00:00:01 |
    |   4 |     NESTED LOOPS OUTER            |                         |     1 |    94 |     3   (0)| 00:00:01 |
    |   5 |      NESTED LOOPS                 |                         |     1 |    89 |     3   (0)| 00:00:01 |
    |   6 |       NESTED LOOPS OUTER          |                         |     1 |    47 |     2   (0)| 00:00:01 |
    |   7 |        NESTED LOOPS               |                         |     1 |    44 |     2   (0)| 00:00:01 |
    |   8 |         TABLE ACCESS BY USER ROWID| TBS_GSFT_OBJ_BZHNG_HRCH |     1 |    15 |     1   (0)| 00:00:01 |
    |*  9 |         INDEX RANGE SCAN          | IDX_GSFT_OBJ_MANY1      |     1 |    29 |     1   (0)| 00:00:01 |
    |* 10 |        INDEX UNIQUE SCAN          | CPK_BNTZR               |     1 |     3 |     0   (0)| 00:00:01 |
    |  11 |       TABLE ACCESS BY INDEX ROWID | TBS_VRZNG_ENHT          |     1 |    42 |     1   (0)| 00:00:01 |
    |* 12 |        INDEX UNIQUE SCAN          | CPK_VRZNG_ENHT          |     1 |       |     0   (0)| 00:00:01 |
    |* 13 |      INDEX UNIQUE SCAN            | CPK_GSFT_OBJ_SUB_TYP_ID |     1 |     5 |     0   (0)| 00:00:01 |
    |* 14 |     INDEX UNIQUE SCAN             | CPK_GSFT_OBJ_TYP        |     1 |     5 |     0   (0)| 00:00:01 |
    |* 15 |    INDEX UNIQUE SCAN              | CPK_FRMLR               |     1 |     5 |     0   (0)| 00:00:01 |
    |* 16 |   INDEX UNIQUE SCAN               | CPK_FRMLR               |     1 |     5 |     0   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       9 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")
      10 - access("G"."CHECK_OUT_BNTZR_ID"="B"."BNTZR_ID"(+))
      12 - access("G"."GSFT_OBJ_ID"="VRZNG_ENHT_ID")
      13 - access("G"."GSFT_OBJ_SUB_TYP_ID"="GOST"."GSFT_OBJ_SUB_TYP_ID"(+))
      14 - access("G"."GSFT_OBJ_TYP_ID"="GOT"."GSFT_OBJ_TYP_ID"(+))
      15 - access("G"."BRBTG_FRMLR_ID"="F2"."FRMLR_ID"(+))
      16 - access("G"."ANST_FRMLR_ID"="F"."FRMLR_ID"(+))
    So do you realize that the 10g plan contains a lot more tables than the 11g version? It looks like it is the new 11g "outer join elimination" transformation (a description can be found here for example: http://optimizermagic.blogspot.com/2008/06/why-are-some-of-tables-in-my-query.html) that is applied to your statement and it could be related to this new feature that the ROWID is used against the wrong table.

    You can try to test this hypothesis by using the following workarounds. With the original view definition try each of the following hints in your query failing with ORA-01410:

    1. select /*+ optimizer_features_enable('10.2.0.4') */ ...

    This should give you a 10.2.0.4-like plan provided that other inputs to the optimizer like statistics are similar - hence the error should not occur and the execution plan will be showing as many steps as the one you've posted from 10.2.0.4

    2. select /*+ opt_param('_optimizer_join_elimination_enabled', 'false') */ ...

    This disables completely the join elimination feature (10.2 already supports types of join eliminations but not the outer join one) and should give you again a working execution plan, but probably even more tables in the execution plan because I think a few have already been eliminated in 10.2.0.4

    3. select /*+ opt_param('_fix_control', '4967068:OFF') */ ...

    This one only disables the new outer join elimination feature - again it should give you a working execution plan of the otherwise failing query

    So if this hypothesis is correct you should consider switching off the outer join elimination at system level (or session level via a logon trigger) as long as there is no fix available for the behaviour seen.

    ALTER SYSTEM SET "_FIX_CONTROL" = '4967068:OFF';

    Note you need to be careful if there are already other non-default FIX_CONTROLs enabled - this would effectively override those non-defaults making them default again, because they need to specified in a single string to be enabled at the same time.

    Your workaround of replacing the view with the base table helps to come up with a working execution plan but the problem might again pop up if different variations of the query get executed, hence I would recommend to disable the feature and open a SR with Oracle to get the problem solved (provided my hypothesis is confirmed).

    Can you provide the view definition of "vwr_vrzng_enht"?

    I would like to be able to reproduce the issue.

    Hope this helps,
    Randolf
  • 18. Re: ORA-01410 when selecting data via ROWID
    906492 Newbie
    Currently Being Moderated
    hi Randolf,

    thank for your detailed respone on that.

    1. select /*+ optimizer_features_enable('10.2.0.4') */ ...
    the last plan I posted resulted because of setting optimizer_features_enable='10.2.0.4'; so I did not try to execute the statement on a real 10.2.0.4 database

    2.select /*+ opt_param('_optimizer_join_elimination_enabled', 'false') */ ...
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                           |     1 |   135 |     3   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS OUTER                      |                           |     1 |   135 |     3   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS OUTER                     |                           |     1 |   130 |     3   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS OUTER                    |                           |     1 |   125 |     3   (0)| 00:00:01 |
    |   4 |     NESTED LOOPS OUTER                   |                           |     1 |   120 |     3   (0)| 00:00:01 |
    |   5 |      NESTED LOOPS                        |                           |     1 |   115 |     3   (0)| 00:00:01 |
    |   6 |       NESTED LOOPS                       |                           |     1 |   110 |     3   (0)| 00:00:01 |
    |   7 |        NESTED LOOPS                      |                           |     1 |   107 |     3   (0)| 00:00:01 |
    |   8 |         NESTED LOOPS                     |                           |     1 |   103 |     3   (0)| 00:00:01 |
    |   9 |          NESTED LOOPS                    |                           |     1 |    99 |     3   (0)| 00:00:01 |
    |  10 |           NESTED LOOPS                   |                           |     1 |    95 |     3   (0)| 00:00:01 |
    |  11 |            NESTED LOOPS                  |                           |     1 |    92 |     3   (0)| 00:00:01 |
    |  12 |             NESTED LOOPS                 |                           |     1 |    89 |     3   (0)| 00:00:01 |
    |  13 |              NESTED LOOPS OUTER          |                           |     1 |    47 |     2   (0)| 00:00:01 |
    |  14 |               NESTED LOOPS               |                           |     1 |    44 |     2   (0)| 00:00:01 |
    |  15 |                TABLE ACCESS BY USER ROWID| TBS_GSFT_OBJ_BZHNG_HRCH   |     1 |    15 |     1   (0)| 00:00:01 |
    |* 16 |                INDEX RANGE SCAN          | IDX_GSFT_OBJ_MANY1        |     1 |    29 |     1   (0)| 00:00:01 |
    |* 17 |               INDEX UNIQUE SCAN          | CPK_BNTZR                 |     1 |     3 |     0   (0)| 00:00:01 |
    |  18 |              TABLE ACCESS BY INDEX ROWID | TBS_VRZNG_ENHT            |     1 |    42 |     1   (0)| 00:00:01 |
    |* 19 |               INDEX UNIQUE SCAN          | CPK_VRZNG_ENHT            |     1 |       |     0   (0)| 00:00:01 |
    |* 20 |             INDEX UNIQUE SCAN            | CPK_VRZNG_ENHT_BRBTG_STTS |     1 |     3 |     0   (0)| 00:00:01 |
    |* 21 |            INDEX UNIQUE SCAN             | CPK_VRZNG_ENHT_INHLT      |     1 |     3 |     0   (0)| 00:00:01 |
    |* 22 |           INDEX UNIQUE SCAN              | CPK_VRZNG_ENHT_ZGNGL      |     1 |     4 |     0   (0)| 00:00:01 |
    |* 23 |          INDEX UNIQUE SCAN               | CPK_VRZNG_ENHT_BNTZR      |     1 |     4 |     0   (0)| 00:00:01 |
    |* 24 |         INDEX UNIQUE SCAN                | CPK_VRZNG_ENHT_BWLG_TYP   |     1 |     4 |     0   (0)| 00:00:01 |
    |* 25 |        INDEX UNIQUE SCAN                 | CPK_VRZNG_ENHT_STZ_FRIST  |     1 |     3 |     0   (0)| 00:00:01 |
    |* 26 |       INDEX UNIQUE SCAN                  | CPK_ENTRG_TYP             |     1 |     5 |     0   (0)| 00:00:01 |
    |* 27 |      INDEX UNIQUE SCAN                   | CPK_GSFT_OBJ_SUB_TYP_ID   |     1 |     5 |     0   (0)| 00:00:01 |
    |* 28 |     INDEX UNIQUE SCAN                    | CPK_GSFT_OBJ_TYP          |     1 |     5 |     0   (0)| 00:00:01 |
    |* 29 |    INDEX UNIQUE SCAN                     | CPK_FRMLR                 |     1 |     5 |     0   (0)| 00:00:01 |
    |* 30 |   INDEX UNIQUE SCAN                      | CPK_FRMLR                 |     1 |     5 |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------

    16 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")
    17 - access("G"."CHECK_OUT_BNTZR_ID"="B"."BNTZR_ID"(+))
    19 - access("G"."GSFT_OBJ_ID"="VRZNG_ENHT_ID")
    20 - access("VRZNG_ENHT_BRBTG_STTS_ID"="VEBS"."VRZNG_ENHT_BRBTG_STTS_ID")
    21 - access("VRZNG_ENHT_INHLT_ID"="VEI"."VRZNG_ENHT_INHLT_ID")
    22 - access("VRZNG_ENHT_ZGNGL_ID"="VEZ"."VRZNG_ENHT_ZGNGL_ID")
    23 - access("VRZNG_ENHT_BNTZB_ID"="VEB"."VRZNG_ENHT_BNTZB_ID")
    24 - access("VRZNG_ENHT_BWLG_TYP_ID"="VEBT"."VRZNG_ENHT_BWLG_TYP_ID")
    25 - access("VRZNG_ENHT_SCHTZ_FRIST_ID"="VESF"."VRZNG_ENHT_SCHTZ_FRIST_ID")
    26 - access("VRZNG_ENHT_ENTRG_TYP_ID"="ET"."ENTRG_TYP_ID")
    27 - access("G"."GSFT_OBJ_SUB_TYP_ID"="GOST"."GSFT_OBJ_SUB_TYP_ID"(+))
    28 - access("G"."GSFT_OBJ_TYP_ID"="GOT"."GSFT_OBJ_TYP_ID"(+))
    29 - access("G"."BRBTG_FRMLR_ID"="F2"."FRMLR_ID"(+))
    30 - access("G"."ANST_FRMLR_ID"="F"."FRMLR_ID"(+))


    3.select /*+ opt_param('_fix_control', '4967068:OFF') */ ...
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                         |                           |     1 |   135 |     3   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS OUTER                      |                           |     1 |   135 |     3   (0)| 00:00:01 |
    |   2 |   NESTED LOOPS OUTER                     |                           |     1 |   130 |     3   (0)| 00:00:01 |
    |   3 |    NESTED LOOPS OUTER                    |                           |     1 |   125 |     3   (0)| 00:00:01 |
    |   4 |     NESTED LOOPS OUTER                   |                           |     1 |   120 |     3   (0)| 00:00:01 |
    |   5 |      NESTED LOOPS                        |                           |     1 |   115 |     3   (0)| 00:00:01 |
    |   6 |       NESTED LOOPS                       |                           |     1 |   110 |     3   (0)| 00:00:01 |
    |   7 |        NESTED LOOPS                      |                           |     1 |   107 |     3   (0)| 00:00:01 |
    |   8 |         NESTED LOOPS                     |                           |     1 |   103 |     3   (0)| 00:00:01 |
    |   9 |          NESTED LOOPS                    |                           |     1 |    99 |     3   (0)| 00:00:01 |
    |  10 |           NESTED LOOPS                   |                           |     1 |    95 |     3   (0)| 00:00:01 |
    |  11 |            NESTED LOOPS                  |                           |     1 |    92 |     3   (0)| 00:00:01 |
    |  12 |             NESTED LOOPS                 |                           |     1 |    89 |     3   (0)| 00:00:01 |
    |  13 |              NESTED LOOPS OUTER          |                           |     1 |    47 |     2   (0)| 00:00:01 |
    |  14 |               NESTED LOOPS               |                           |     1 |    44 |     2   (0)| 00:00:01 |
    |  15 |                TABLE ACCESS BY USER ROWID| TBS_GSFT_OBJ_BZHNG_HRCH   |     1 |    15 |     1   (0)| 00:00:01 |
    |* 16 |                INDEX RANGE SCAN          | IDX_GSFT_OBJ_MANY1        |     1 |    29 |     1   (0)| 00:00:01 |
    |* 17 |               INDEX UNIQUE SCAN          | CPK_BNTZR                 |     1 |     3 |     0   (0)| 00:00:01 |
    |  18 |              TABLE ACCESS BY INDEX ROWID | TBS_VRZNG_ENHT            |     1 |    42 |     1   (0)| 00:00:01 |
    |* 19 |               INDEX UNIQUE SCAN          | CPK_VRZNG_ENHT            |     1 |       |     0   (0)| 00:00:01 |
    |* 20 |             INDEX UNIQUE SCAN            | CPK_VRZNG_ENHT_BRBTG_STTS |     1 |     3 |     0   (0)| 00:00:01 |
    |* 21 |            INDEX UNIQUE SCAN             | CPK_VRZNG_ENHT_INHLT      |     1 |     3 |     0   (0)| 00:00:01 |
    |* 22 |           INDEX UNIQUE SCAN              | CPK_VRZNG_ENHT_ZGNGL      |     1 |     4 |     0   (0)| 00:00:01 |
    |* 23 |          INDEX UNIQUE SCAN               | CPK_VRZNG_ENHT_BNTZR      |     1 |     4 |     0   (0)| 00:00:01 |
    |* 24 |         INDEX UNIQUE SCAN                | CPK_VRZNG_ENHT_BWLG_TYP   |     1 |     4 |     0   (0)| 00:00:01 |
    |* 25 |        INDEX UNIQUE SCAN                 | CPK_VRZNG_ENHT_STZ_FRIST  |     1 |     3 |     0   (0)| 00:00:01 |
    |* 26 |       INDEX UNIQUE SCAN                  | CPK_ENTRG_TYP             |     1 |     5 |     0   (0)| 00:00:01 |
    |* 27 |      INDEX UNIQUE SCAN                   | CPK_GSFT_OBJ_SUB_TYP_ID   |     1 |     5 |     0   (0)| 00:00:01 |
    |* 28 |     INDEX UNIQUE SCAN                    | CPK_GSFT_OBJ_TYP          |     1 |     5 |     0   (0)| 00:00:01 |
    |* 29 |    INDEX UNIQUE SCAN                     | CPK_FRMLR                 |     1 |     5 |     0   (0)| 00:00:01 |
    |* 30 |   INDEX UNIQUE SCAN                      | CPK_FRMLR                 |     1 |     5 |     0   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------

    16 - access("GOBH"."GSFT_OBJ_2_ID"="G"."GSFT_OBJ_ID")
    17 - access("G"."CHECK_OUT_BNTZR_ID"="B"."BNTZR_ID"(+))
    19 - access("G"."GSFT_OBJ_ID"="VRZNG_ENHT_ID")
    20 - access("VRZNG_ENHT_BRBTG_STTS_ID"="VEBS"."VRZNG_ENHT_BRBTG_STTS_ID")
    21 - access("VRZNG_ENHT_INHLT_ID"="VEI"."VRZNG_ENHT_INHLT_ID")
    22 - access("VRZNG_ENHT_ZGNGL_ID"="VEZ"."VRZNG_ENHT_ZGNGL_ID")
    23 - access("VRZNG_ENHT_BNTZB_ID"="VEB"."VRZNG_ENHT_BNTZB_ID")
    24 - access("VRZNG_ENHT_BWLG_TYP_ID"="VEBT"."VRZNG_ENHT_BWLG_TYP_ID")
    25 - access("VRZNG_ENHT_SCHTZ_FRIST_ID"="VESF"."VRZNG_ENHT_SCHTZ_FRIST_ID")
    26 - access("VRZNG_ENHT_ENTRG_TYP_ID"="ET"."ENTRG_TYP_ID")
    27 - access("G"."GSFT_OBJ_SUB_TYP_ID"="GOST"."GSFT_OBJ_SUB_TYP_ID"(+))
    28 - access("G"."GSFT_OBJ_TYP_ID"="GOT"."GSFT_OBJ_TYP_ID"(+))
    29 - access("G"."BRBTG_FRMLR_ID"="F2"."FRMLR_ID"(+))
    30 - access("G"."ANST_FRMLR_ID"="F"."FRMLR_ID"(+))



    =========================================

    => you are totally right with your hypothesis: !!!

    SQL> ALTER Session SET "_FIX_CONTROL" = '4967068:OFF';

    Session wurde geõndert.

    SQL> select s.rowid , S.VRZNG_ENHT_TITEL from vws_vrzng_enht_haupt_sys s
    2 where s.rowid='AAASN0AAFAAACBUAAB';
    AAASN0AAFAAACBUAAB
    Kanton Z³rich


    ========================================
    Following the view VWR_VRZNG_ENHT which is only a collection of a couple of column of TBS_VRZNG_ENHT

    /* Formatted on 22.12.2011 09:29:51 (QP5 v5.185.11230.41888) */
    CREATE OR REPLACE FORCE VIEW APLKN_ARCHV_AHB.VWR_VRZNG_ENHT
    (
    VRZNG_ENHT_ID,
    LESEN_ATRSG_STUFE_ID,
    MTTN_ATRSG_STUFE_ID,
    VRZNG_ENHT_BNTZB_ID,
    VRZNG_ENHT_BRBTG_STTS_ID,
    VRZNG_ENHT_BWLG_TYP_ID,
    VRZNG_ENHT_ENTRG_TYP_ID,
    VRZNG_ENHT_INHLT_ID,
    VRZNG_ENHT_SCHTZ_FRIST_ID,
    VRZNG_ENHT_ZGNGL_ID,
    SGNTR_CD,
    SGNTR_ARCHV_PLAN_CD,
    VRZNG_ENHT_TITEL,
    ARCHV_STAND_ORT_TXT,
    BNTZG_HNWS_TXT,
    DARIN_TXT,
    ERSLS_GRAD_TXT,
    ORDNG_KMPNT_TXT,
    SORT_FELD_TXT,
    TYP_TXT,
    SCHTZ_FRIST_BIS_DT,
    SCHTZ_FRIST_DAUER,
    SCHTZ_FRIST_NTZ,
    BGN_DT,
    END_DT,
    ZT_RAUM_TXT,
    MTRL_BGN_DT,
    MTRL_END_DT,
    MTRL_ZT_RAUM_TXT,
    BILD_ANST_IND,
    BILD_VRSCH_IND,
    BLKRT_IND,
    FIND_MTL_IND,
    SCHTZ_FRIST_MIN_IND,
    SCHTZ_FRIST_MTTN_IND,
    SUCH_FRGB_IND
    )
    AS
    SELECT vrzng_enht_id,
    lesen_atrsg_stufe_id,
    mttn_atrsg_stufe_id,
    vrzng_enht_bntzb_id,
    vrzng_enht_brbtg_stts_id,
    vrzng_enht_bwlg_typ_id,
    vrzng_enht_entrg_typ_id,
    vrzng_enht_inhlt_id,
    vrzng_enht_schtz_frist_id,
    vrzng_enht_zgngl_id,
    sgntr_cd,
    sgntr_archv_plan_cd,
    vrzng_enht_titel,
    archv_stand_ort_txt,
    bntzg_hnws_txt,
    darin_txt,
    ersls_grad_txt,
    ordng_kmpnt_txt,
    sort_feld_txt,
    typ_txt,
    schtz_frist_bis_dt,
    schtz_frist_dauer,
    schtz_frist_ntz,
    bgn_dt,
    end_dt,
    zt_raum_txt,
    mtrl_bgn_dt,
    mtrl_end_dt,
    mtrl_zt_raum_txt,
    bild_anst_ind,
    bild_vrsch_ind,
    blkrt_ind,
    find_mtl_ind,
    schtz_frist_min_ind,
    schtz_frist_mttn_ind,
    such_frgb_ind
    FROM tbs_vrzng_enht;



    So, thank you so much, Randolf for your effective help on this and the time you spent on this problem

    thanks again and a merry Christmas!
    Stefan
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points