This is a subject area in which I think that Oracle's push for autonomous
management has a lot of potential. I think the autonomous indexing feature
is pretty cool.
Cary Millsap
Method R Corporation
Author of *Optimizing Oracle Performance <http://amzn.to/OM0q75>*
and *The Method R Guide to Mastering Oracle Trace Data, 3rd edition
<https://amzn.to/2IhhCG6+-+Millsap+2019.+Mastering+Oracle+Trace+Data+3ed>*
On Tue, Jan 14, 2020 at 7:46 AM Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
Dan's book is great, but I also like the Oracle document about indexing
strategy:
https://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_in.htm
On 1/13/20 3:30 PM, kyle Hailey wrote:
What are the best docs or books out there on methodologies for Index?
The first past approach is just to look at execution plan, see where there
are full tablescans scanning significant # of rows or blocks with predicate
or join filters that filters out most of the rows. Then extending that
index opportunity to other SQL and seeing which columns might be included
in the index and what order. I like this description from a PostgreSQL
extension
https://rjuju.github.io/postgresql/2020/01/06/pg_qualstats-2-global-index-advisor.html
.
Many years ago, Mogens Nørgaard recommend Dan Tow's book "SQL Tuning" to
me for SQL tuning methodologies and to this day it has been the best
example of a prescriptive , step by step process, to find the best (or near
best) execution plan for a SQL statement, and ended up helping me lead a
project
to implement it in software
<http://dboptimizer.com/2011/07/08/woohoo-vst-takes-the-next-step/>.
Would like to find something similar for indexing to find
optimal opportunities for adding indexes.
Feel free to say what not to read. Knowing what not to read can be
important as well.
Thanks
Kyle
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
