DB tool for suggesting Indexes?

[DBohner-(db)]

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

 

[dschlieder]

Daniel,

FWIW I ran it through the index wizard in MS SQL - it was happy with the indexes that I have on the tables - the standard JDE indexes - we're on 7.334 so I had to remove some of the fields from the query that don't exist in my version - it didn't want to create any more.

But then that's MS SQL not Oracle - I just thought it might come up with something that would be of help.

Regards,

Dave Schlieder

 

[Larry_Jones]

Daniel,

after creating the indices did you recompute statistics?

 

[Alex_Pastuhov]

It's frequently a matter of tuning, as a good index would likely already be there, rather than simply creating an index. Oracle may need some convincing to use an index.

On the other hand, if "SDDCTO = 'SO'" in most rows there (i.e.: over 80% or all rows), then using any index would not really be beneficial even you do convince Oracle to use it.

You can possibly also change the "SDSO15 <> '1'" part to "(SDSO15 is NULL or SDSO15 = '0')" instead - this would make this criteria a candidate for an index use, otherwise it will not be considered.

 

[DBohner-(db)]

Yes - we've been re-computing statisticicsicssitics...

I hate magic and pixie-dust!

(db)

 

[DBohner-(db)]

I found that the Oracle Enterprise Manager for 10g does a pretty good job identifying (suggesting) indexes...

Y'all on the bOrgacle - might want to take a gander at some play time with it...

(db)

 

[Alex_Pastuhov]

Yeh, pixie dust sucks, try voodoo ;-)

It's 20% science and 80% voodoo...