How do I tune a SQL statement when the Optimizer picks the wrong index?
Вставка
- Опубліковано 9 лют 2025
- Understanding why the Optimizer makes a decision and providing the necessary information and access methods to allow the Optimizer to pick the plan you want by default can be challenging. But don't give in to the overwhelming temptation to use a hint to brut force the plan you want. In this short video, I explain why the Optimizer didn't choose the index folks were expecting, how to identify these types of problems and provide you with the solution. Hopefully, you will be able to apply the lessons shown in this video to your SQL Tuning tasks on your own systems!
you are a great teacher..
Thanks so much Maria, whishing you a great 2022!
Bravo, thank you very much for all the information that you share. Delivery is beautiful.
This is very informative videos Maria. Can you please also help me to understand which statistics optimizer refer while doing two or more table joins.. if you can just highlight any blog or Doc, will also a great help. Thank you
Thanks for doing the maths for us!
Any time Jeff!
Great information
Hi Maria can you please explain How you get Value : 0.013 for Cost of index range scan with filter predicate (at 5:00 ) I notice you showed formula 1/NDV of column - Can please let us know which column NDV was used either PROD_ID or CUST_ID.
Hi Jamsher, the column in question is Column 1 of the index. In this case, that's the PROD_ID column.
Hi ma'am, how come a new index created on 3 columns having required col prod id and cust id and comment has lesser cost than index used in very first plan which has 2580. Pls explain if possible. Also how Ndv calculated in your 2nd cost calculation formula.
Excellent.
The Avatar Lady is very funny 😺
Hi Maria, between 7 min and 7:30 min in the video you said that only in 2nd plan i.e. expected plan only prod id is used as access predicate but that is not the case in the plan both prod id and cust id are used as access predicate and then in filter predicate custid is used again to filter the records. Could you please help me to understand this ?
Mohd, A predicate can be used only once, either as an Acess Predicate or as a Filter Predicate. Although the CUST_ID is listed as an Access Predicate, it is not used because it's not part of the leading edge of the index. It is only used as a Filter Predicate. Unfortunately, the way it's displayed under the execution plan is confusing but when you see the same predicate listed twice, it will be used as described in the second occurrence. In this case, that's as a Filter Predicate.
@@SQLMaria thanks a lot for the explanation. It makes complete sense.
Thank you Maria.. Your explanation is awesome always.. __/\__