--- sybase version ---
Adaptive Server Enterprise/15.7.0/EBF 22639 SMP SP52 /P/Sun_svr4/OS 5.10/ase157sp5x/3293/64-bit/FBO/Wed May 21 02:49:14 2014
--- end version ---
I'm starting reading/reading again some chapters of 'ASE 15.7 Performance and Tuning Series: Query Processing and Abstract Plans'.
I think I get the 'big picture' of the need of the optimizer but I'm looking for way to better understand it and the information it needs to provide the best plan to the execution engine.
Is there any document/presentation that will present a kind of 'hands-on understanding the optimizer'?
For example, I get a rough image when I read this
The query optimizer processes join predicates the same way it processes search
arguments, in that it uses statistics, number of rows in the table, index heights,
and the cluster ratios for the index and data pages to determine which index and
join method provides the cheapest access. In addition, the query optimizer also
uses join density estimates derived from join histograms that give accurate
estimates of qualifying joining rows and the rows to be scanned in the outer and
inner tables. The query optimizer also must decide on the optimal join ordering
that will yield the most efficient query plan. The next sections describe the key
techniques used in processing joins.
Those words would be meaningfull to me if I can see in practice how the output of 'optdiag statistics' helps the optimizer choosing the right plan, how some dbcc traceon(traceflags) can help me understanding the optimizer work.
Thanks in advance for your input.