Oracle SQL Tuning Expert Series - Understanding Indexes
Vložit
- čas přidán 6. 04. 2015
- Ross Leishman of DWS Ltd presents the principles of Understanding Indexes for SQL Tuning.
The presentation includes an entertaining demonstration of Indexes featuring DWS Alumnus Jordan Thomas as a "Buffer Cache".
DWS Ltd is a leading publicly listed Australian IT Services company, providing services to blue chip organisations since 1991. With a business philosophy based upon integrity, reliability and professional service delivery, DWS provides end to end IT solutions.
www.dws.com.au - Sport
He is a Great instructor. I learned a lot. Thank you.
Excellent talk. Crystal clear explanation. Thanks a lot. Greetings from Chile-SoutAmerica.
Great work.
I was once thrown out of oracle interview for telling B tree is Binary Tree... :)
Wish the interviewer would have seen this and knew I am not alone...
Most RDBMS actually use B+trees as a data structure for an index. So, saying an index is a B-tree is an over-simplification of this and is generally not used as a data-structure, maybe the presenter just said it as an example. If your interviewer asked specifically a "B-Tree", then shame on him for acting so unprofessional! Otherwise, he might have expected for you to correct him or know of a B+Tree.
Anyway, I hope you found this information useful and hope you got the next great job!
Excellent Presentation.Cleared a lot of concepts.
Excellent Video. I could able to solve my performance problem with this information. Thanks Ross
thank you so much for your sharing.
good to learn and understand in simple ways
A very elegant presentation. The exercise was perfect and I feel the guy representing Buffer Cache will definitely remember oracle index working for a long long time.
Thanks Debabrata. Yes, the demo worked better than I had any right to expect, mostly thanks to Jordan's sublime comic timing.
excellent.
Wow, he is the best
where can I find the link to more presentation s of urs. loads of things to learn from you
In Q&A, talking about network latency as a reason for performance degradation reading from disks, because the data has to go back and forth - Even if the data has to be read from the buffer cache where the buffer cache is also in the server memory and hence there is a round trip (network latency). So, doesn't it just come down to only the quick access from memory versus relatively slow access from the disk? Because, there is network latency in both? Thanks
Hi MGM L. I'm not sure I understand your question. Are you saying the cost of a round trip to memory is the same as Network Latency. I'm no hardware geek, so I'm prepared to be shouted down on this, but Memory (RAM) is resident on the machine and directly accessible by the CPU. Latency to pick something out of RAM is vanishingly fast.Disk systems CAN be resident on the machine (like the SSD in my laptop), but are not directly accessible by the CPU. It needs to send a message to the disk controller to find and read data. That round-trip is called (at least I call it) Disk Latency, and it is fast, but nothing like as fast as reading from memory.Enterprise databases don't exist on my laptop. More often than not, their disk storage is NOT resident on the machine - it's on a whole nuther machine. When the database wants something off that disk system, it needs to send a message across the network to that machine, and THAT'S Network Latency - it takes time.
Ross you are awesome. I need more links to your video s
Cheers Parag. If only I could turn kudos into cash :-)
He he he... :)... Yeah, I agree with you. Thanks anyways.
Thank you Ross! Excellent video, where can I find the video for next session?
It hasn't been made yet. But subscribe to the channel so you don't miss out.
Where can I fin the next session video?
I have one doubt- during B-Tree slide, how come 400 entries comes in one block of 8KB if you are creating an index on a 8 byte field? As per my calculation it will be 8000/8 = 1000 entries, does it make sense? Will help if you anyone can clarify.
I want to learn your way - in the end you said there will be more presentations in future. Hope you have given them. But sadly, I was not able to find the links in CZcams. Can you help me finding those. Thanks in advance.
There's no more Oracle presentations ... yet. But if you follow the DWS channel, CZcams will let you know when they arrive.
Thanks Ross.
In example 5.
select c.customer_name , c.customer_address, i.invoice_num, i.invoice_date,i.invoice_amount
from Customer c, ---100 k rows
Invoice i ---- 10 M rows
where c.cust_id=i.cust_id
AND c.customer_name=' Tarun Kumar' -- exactly one row
AND i.invoice_date >= (sysdate-12) -- 20 % of all rows
If we are scanning more than 20% of rows should we add the index on an invoice date? Does not it violate the thumb rule?
Where can we download the slides? It would be very helpful. Thanks a lot
22:09 How come an 8kb block with int as 8 byte only holds 400 entries. It should be 8kb/8 bytes, 1000 entries.
what r disadvantage of index
Index has straight impact on the data loads as index has to be rebuild for data loads as specified in the presentation for new data blocks for attributes which are indexed. Be very careful on choosing what to index. If there are multiple index data loads will get impacted as well as those might or might not be used for all sql queries.
English songs
lyrics