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

astalavista wrote:
This is not an explain plan. It is the output from some hand-crafted script.

Please repost using DBMS_XPLAN.DISPLAY. If you don't know how follow
this link:
http://www.psoug.org/reference/dbms_xplan.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

astalavista wrote:
This is not an explain plan. It is the output from some hand-crafted script.

Please repost using DBMS_XPLAN.DISPLAY. If you don't know how follow
this link:
http://www.psoug.org/reference/dbms_xplan.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

astalavista wrote:
This is not an explain plan. It is the output from some hand-crafted script.

Please repost using DBMS_XPLAN.DISPLAY. If you don't know how follow
this link:
http://www.psoug.org/reference/dbms_xplan.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

astalavista wrote:
This is not an explain plan. It is the output from some hand-crafted script.

Please repost using DBMS_XPLAN.DISPLAY. If you don't know how follow
this link:
http://www.psoug.org/reference/dbms_xplan.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

astalavista wrote:
This is not an explain plan. It is the output from some hand-crafted script.

Please repost using DBMS_XPLAN.DISPLAY. If you don't know how follow
this link:
http://www.psoug.org/reference/dbms_xplan.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

astalavista wrote:
This is not an explain plan. It is the output from some hand-crafted script.

Please repost using DBMS_XPLAN.DISPLAY. If you don't know how follow
this link:
http://www.psoug.org/reference/dbms_xplan.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

astalavista wrote:
This is not an explain plan. It is the output from some hand-crafted script.

Please repost using DBMS_XPLAN.DISPLAY. If you don't know how follow
this link:
http://www.psoug.org/reference/dbms_xplan.html
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

On Jun 11, 3:22 pm, "astalavista" <nob... (AT) nowhere (DOT) com> wrote:

Hi,

LOL! How any different versions of the trtstopdat_c_dt column do you
need to substr/decode/replace?!

Also, there is no where clause? The underlying view does an
INTERSECT?

Regards,

Steve

On Jun 11, 3:22 pm, "astalavista" <nob... (AT) nowhere (DOT) com> wrote:

Hi,

LOL! How any different versions of the trtstopdat_c_dt column do you
need to substr/decode/replace?!

Also, there is no where clause? The underlying view does an
INTERSECT?

Regards,

Steve