Tainted query ehlp
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 24M 1023 Received on Wed Jun 11 2008 - 14:22:08 CDT