DBohner-(db)
Legendary Poster
I’m looking for a database tool – I spit an SQL statement to it, and it kicks suggested Indexes back. Current database is Oracle 10g.
I had seen a pre-release tool about five years ago – I don’t remember what it was called, or where I might find it.
Anyone?
Basically – I have:
SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU, SDOKCO, SDOORN, SDOCTO,
SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8, SDDRQJ, SDTRDJ,
SDPDDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01, SDITM, SDLITM, SDAITM,
SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU, SDRLIT, SDRKIT,
SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK, SDSOCN,
SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCARS, SDMOT,
SDZON, SDFRTH, SDUOM2, SDSQOR, SDUOM4, SDSWMS, SDCRCD, SDCRR, SDFUP, SDFEA,
SDFUC, SDTORG, SDSO16, SDPSIG, SDDRQT, SDADTM, SDOPTT, SDPDTT, SDCORD, SDDVAN,
SDRFRV, SDSHPN, SDRSDT, SDOPDJ, SDPMTN
FROM CRPDTA.F4211
WHERE ( SDDCTO = 'SO' AND SDSO15 <> '1' )
UNION
SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU, SDOKCO, SDOORN, SDOCTO,
SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8, SDDRQJ, SDTRDJ,
SDPDDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01, SDITM, SDLITM, SDAITM,
SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU, SDRLIT, SDRKIT,
SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK, SDSOCN,
SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCARS, SDMOT,
SDZON, SDFRTH, SDUOM2, SDSQOR, SDUOM4, SDSWMS, SDCRCD, SDCRR, SDFUP, SDFEA,
SDFUC, SDTORG, SDSO16, SDPSIG, SDDRQT, SDADTM, SDOPTT, SDPDTT, SDCORD, SDDVAN,
SDRFRV, SDSHPN, SDRSDT, SDOPDJ, SDPMTN
FROM CRPDTA.F42119
WHERE ( SDDCTO = 'SO' AND SDSO15 <> '1' )
ORDER BY 2 ASC , 3 ASC , 1 ASC , 4 ASC
It is doing a full table scan on ‘just’ the F42119 side, I’ve added what I thought should be valid/good indexes – and the performance is not changing. Even if I completely eliminate the F4211 parts – it still hates me. I have an index on SDDCTO and SDS015 (which, logically, should have been the ‘key’????)
Anyone know a decent tool for making the index suggestions.
(db)
Solaris, Unix, 10G, 8.12
I had seen a pre-release tool about five years ago – I don’t remember what it was called, or where I might find it.
Anyone?
Basically – I have:
SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU, SDOKCO, SDOORN, SDOCTO,
SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8, SDDRQJ, SDTRDJ,
SDPDDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01, SDITM, SDLITM, SDAITM,
SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU, SDRLIT, SDRKIT,
SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK, SDSOCN,
SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCARS, SDMOT,
SDZON, SDFRTH, SDUOM2, SDSQOR, SDUOM4, SDSWMS, SDCRCD, SDCRR, SDFUP, SDFEA,
SDFUC, SDTORG, SDSO16, SDPSIG, SDDRQT, SDADTM, SDOPTT, SDPDTT, SDCORD, SDDVAN,
SDRFRV, SDSHPN, SDRSDT, SDOPDJ, SDPMTN
FROM CRPDTA.F4211
WHERE ( SDDCTO = 'SO' AND SDSO15 <> '1' )
UNION
SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU, SDOKCO, SDOORN, SDOCTO,
SDRKCO, SDRORN, SDRCTO, SDRLLN, SDDMCT, SDAN8, SDSHAN, SDPA8, SDDRQJ, SDTRDJ,
SDPDDJ, SDADDJ, SDIVD, SDCNDJ, SDDGL, SDRSDJ, SDPEFJ, SDVR01, SDITM, SDLITM, SDAITM,
SDLOCN, SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU, SDRLIT, SDRKIT,
SDSRP1, SDSRP2, SDSRP3, SDSRP4, SDSRP5, SDUOM, SDUORG, SDSOQS, SDSOBK, SDSOCN,
SDUPRC, SDAEXP, SDUNCS, SDASN, SDKCO, SDDOC, SDDCT, SDPSN, SDDELN, SDCARS, SDMOT,
SDZON, SDFRTH, SDUOM2, SDSQOR, SDUOM4, SDSWMS, SDCRCD, SDCRR, SDFUP, SDFEA,
SDFUC, SDTORG, SDSO16, SDPSIG, SDDRQT, SDADTM, SDOPTT, SDPDTT, SDCORD, SDDVAN,
SDRFRV, SDSHPN, SDRSDT, SDOPDJ, SDPMTN
FROM CRPDTA.F42119
WHERE ( SDDCTO = 'SO' AND SDSO15 <> '1' )
ORDER BY 2 ASC , 3 ASC , 1 ASC , 4 ASC
It is doing a full table scan on ‘just’ the F42119 side, I’ve added what I thought should be valid/good indexes – and the performance is not changing. Even if I completely eliminate the F4211 parts – it still hates me. I have an index on SDDCTO and SDS015 (which, logically, should have been the ‘key’????)
Anyone know a decent tool for making the index suggestions.
(db)
Solaris, Unix, 10G, 8.12