Tainted query ehlp

From: astalavista <nobody_at_nowhere.com>
Date: Wed, 11 Jun 2008 21:22:08 +0200
Message-ID: <485025dd$0$15527$426a74cc@news.free.fr>


Hi,

Is possible to optimize the tainted query below ?

(explain plan at bottom)

Thanks for your help (10.2.0.3)

SELECT COUNT (*) FROM (SELECT clintab, patientid, visitid, visitindex, formindex,

itemsetindex, REPLACE (trtroute_c, :"SYS_B_000", :"SYS_B_001"),

REPLACE (trtmedicnb_c, :"SYS_B_002", :"SYS_B_003"),

REPLACE (trtatccom_cra_c, :"SYS_B_004", :"SYS_B_005"),

trtdose_c, trtschadm2_c,

DECODE (trtstopdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstopdat_c_str,

NVL (INSTR (trtstopdat_c_dt,

:"SYS_B_006"
),

:"SYS_B_007"
),

:"SYS_B_008"
),

:"SYS_B_009",

:"SYS_B_010"
),

:"SYS_B_011",

:"SYS_B_012"
),

:"SYS_B_013",

:"SYS_B_014"
),

:"SYS_B_015",

:"SYS_B_016"
),

:"SYS_B_017",

:"SYS_B_018"
),

SUBSTR (trtstopdat_c_dt, :"SYS_B_019", :"SYS_B_020")

),

REPLACE (trtdisnba1_c, :"SYS_B_021", :"SYS_B_022"),

trtongoing_c, REPLACE (trtyesno_c, :"SYS_B_023", :"SYS_B_024"),

REPLACE (trtdisnba2_c, :"SYS_B_025", :"SYS_B_026"),

DECODE (trtstartdat_c_dt,

NULL, NULL, TO_DATE (trtstartdat_c_dt, :"SYS_B_027")

),

DECODE (trtstopdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstopdat_c_str,

NVL (INSTR (trtstopdat_c_dt,

:"SYS_B_028"
),

:"SYS_B_029"
),

:"SYS_B_030"
),

:"SYS_B_031",

:"SYS_B_032"
),

:"SYS_B_033",

:"SYS_B_034"
),

:"SYS_B_035",

:"SYS_B_036"
),

:"SYS_B_037",

:"SYS_B_038"
),

:"SYS_B_039",

:"SYS_B_040"
),

SUBSTR (trtstopdat_c_dt, :"SYS_B_041", :"SYS_B_042")

),

DECODE (trtstartdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstartdat_c_str,

NVL (INSTR (trtstartdat_c_dt,

:"SYS_B_043"
),

:"SYS_B_044"
),

:"SYS_B_045"
),

:"SYS_B_046",

:"SYS_B_047"
),

:"SYS_B_048",

:"SYS_B_049"
),

:"SYS_B_050",

:"SYS_B_051"
),

:"SYS_B_052",

:"SYS_B_053"
),

:"SYS_B_054",

:"SYS_B_055"
),

SUBSTR (trtstartdat_c_dt, :"SYS_B_056", :"SYS_B_057")

),

REPLACE (trttreatnam_c, :"SYS_B_058", :"SYS_B_059"),

REPLACE (trtdosunit_c, :"SYS_B_060", :"SYS_B_061"),

DECODE (trtstartdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstartdat_c_str,

NVL (INSTR (trtstartdat_c_dt,

:"SYS_B_062"
),

:"SYS_B_063"
),

:"SYS_B_064"
),

:"SYS_B_065",

:"SYS_B_066"
),

:"SYS_B_067",

:"SYS_B_068"
),

:"SYS_B_069",

:"SYS_B_070"
),

:"SYS_B_071",

:"SYS_B_072"
),

:"SYS_B_073",

:"SYS_B_074"
),

SUBSTR (trtstartdat_c_dt, :"SYS_B_075", :"SYS_B_076")

),

DECODE (trtstopdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstopdat_c_str,

NVL (INSTR (trtstopdat_c_dt,

:"SYS_B_077"
),

:"SYS_B_078"
),

:"SYS_B_079"
),

:"SYS_B_080",

:"SYS_B_081"
),

:"SYS_B_082",

:"SYS_B_083"
),

:"SYS_B_084",

:"SYS_B_085"
),

:"SYS_B_086",

:"SYS_B_087"
),

:"SYS_B_088",

:"SYS_B_089"
),

SUBSTR (trtstopdat_c_dt, :"SYS_B_090", :"SYS_B_091")

),

REPLACE (visit1_c, :"SYS_B_092", :"SYS_B_093"),

REPLACE (trtdisnba3_c, :"SYS_B_094", :"SYS_B_095"),

DECODE (trtstartdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstartdat_c_str,

NVL (INSTR (trtstartdat_c_dt,

:"SYS_B_096"
),

:"SYS_B_097"
),

:"SYS_B_098"
),

:"SYS_B_099",

:"SYS_B_100"
),

:"SYS_B_101",

:"SYS_B_102"
),

:"SYS_B_103",

:"SYS_B_104"
),

:"SYS_B_105",

:"SYS_B_106"
),

:"SYS_B_107",

:"SYS_B_108"
),

SUBSTR (trtstartdat_c_dt, :"SYS_B_109", :"SYS_B_110")

),

DECODE (trtstopdat_c_dt,

NULL, NULL, TO_DATE (trtstopdat_c_dt, :"SYS_B_111")

)

FROM t_treat_diff

WHERE ROWID = :"SYS_B_112" INTERSECT SELECT clintab, patientid, visitid, visitindex, formindex,

itemsetindex, REPLACE (trtroute_c, :"SYS_B_113", :"SYS_B_114"),

REPLACE (trtmedicnb_c, :"SYS_B_115", :"SYS_B_116"),

REPLACE (trtatccom_cra_c, :"SYS_B_117", :"SYS_B_118"),

trtdose_c, trtschadm2_c,

DECODE (trtstopdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstopdat_c_str,

NVL (INSTR (trtstopdat_c_dt,

:"SYS_B_119"
),

:"SYS_B_120"
),

:"SYS_B_121"
),

:"SYS_B_122",

:"SYS_B_123"
),

:"SYS_B_124",

:"SYS_B_125"
),

:"SYS_B_126",

:"SYS_B_127"
),

:"SYS_B_128",

:"SYS_B_129"
),

:"SYS_B_130",

:"SYS_B_131"
),

SUBSTR (trtstopdat_c_dt, :"SYS_B_132", :"SYS_B_133")

),

REPLACE (trtdisnba1_c, :"SYS_B_134", :"SYS_B_135"),

trtongoing_c, REPLACE (trtyesno_c, :"SYS_B_136", :"SYS_B_137"),

REPLACE (trtdisnba2_c, :"SYS_B_138", :"SYS_B_139"),

DECODE (trtstartdat_c_dt,

NULL, NULL, TO_DATE (trtstartdat_c_dt, :"SYS_B_140")

),

DECODE (trtstopdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstopdat_c_str,

NVL (INSTR (trtstopdat_c_dt,

:"SYS_B_141"
),

:"SYS_B_142"
),

:"SYS_B_143"
),

:"SYS_B_144",

:"SYS_B_145"
),

:"SYS_B_146",

:"SYS_B_147"
),

:"SYS_B_148",

:"SYS_B_149"
),

:"SYS_B_150",

:"SYS_B_151"
),

:"SYS_B_152",

:"SYS_B_153"
),

SUBSTR (trtstopdat_c_dt, :"SYS_B_154", :"SYS_B_155")

),

DECODE (trtstartdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstartdat_c_str,

NVL (INSTR (trtstartdat_c_dt,

:"SYS_B_156"
),

:"SYS_B_157"
),

:"SYS_B_158"
),

:"SYS_B_159",

:"SYS_B_160"
),

:"SYS_B_161",

:"SYS_B_162"
),

:"SYS_B_163",

:"SYS_B_164"
),

:"SYS_B_165",

:"SYS_B_166"
),

:"SYS_B_167",

:"SYS_B_168"
),

SUBSTR (trtstartdat_c_dt, :"SYS_B_169", :"SYS_B_170")

),

REPLACE (trttreatnam_c, :"SYS_B_171", :"SYS_B_172"),

REPLACE (trtdosunit_c, :"SYS_B_173", :"SYS_B_174"),

DECODE (trtstartdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstartdat_c_str,

NVL (INSTR (trtstartdat_c_dt,

:"SYS_B_175"
),

:"SYS_B_176"
),

:"SYS_B_177"
),

:"SYS_B_178",

:"SYS_B_179"
),

:"SYS_B_180",

:"SYS_B_181"
),

:"SYS_B_182",

:"SYS_B_183"
),

:"SYS_B_184",

:"SYS_B_185"
),

:"SYS_B_186",

:"SYS_B_187"
),

SUBSTR (trtstartdat_c_dt, :"SYS_B_188", :"SYS_B_189")

),

DECODE (trtstopdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstopdat_c_str,

NVL (INSTR (trtstopdat_c_dt,

:"SYS_B_190"
),

:"SYS_B_191"
),

:"SYS_B_192"
),

:"SYS_B_193",

:"SYS_B_194"
),

:"SYS_B_195",

:"SYS_B_196"
),

:"SYS_B_197",

:"SYS_B_198"
),

:"SYS_B_199",

:"SYS_B_200"
),

:"SYS_B_201",

:"SYS_B_202"
),

SUBSTR (trtstopdat_c_dt, :"SYS_B_203", :"SYS_B_204")

),

REPLACE (visit1_c, :"SYS_B_205", :"SYS_B_206"),

REPLACE (trtdisnba3_c, :"SYS_B_207", :"SYS_B_208"),

DECODE (trtstartdat_c_dt,

NULL, REPLACE (REPLACE (SUBSTR (REPLACE (REPLACE (SUBSTR (trtstartdat_c_str,

NVL (INSTR (trtstartdat_c_dt,

:"SYS_B_209"
),

:"SYS_B_210"
),

:"SYS_B_211"
),

:"SYS_B_212",

:"SYS_B_213"
),

:"SYS_B_214",

:"SYS_B_215"
),

:"SYS_B_216",

:"SYS_B_217"
),

:"SYS_B_218",

:"SYS_B_219"
),

:"SYS_B_220",

:"SYS_B_221"
),

SUBSTR (trtstartdat_c_dt, :"SYS_B_222", :"SYS_B_223")

),

DECODE (trtstopdat_c_dt,

NULL, NULL, TO_DATE (trtstopdat_c_dt, :"SYS_B_224")

)

FROM t_treat_save)

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 6719   SORT AGGREGATE 1

    VIEW  1     6719
      INTERSECTION
        TABLE ACCESS BY USER ROWID CDDCL318886012UID_P0U_DIFF.T_TREAT_DIFF 1 
196   1
        SORT UNIQUE  140 K 24 M 6717
          TABLE ACCESS FULL CDDCL318886012UID_P0U_DIFF.T_TREAT_SAVE 140 K 24 
M 1023 Received on Wed Jun 11 2008 - 14:22:08 CDT

Original text of this message