Oracle SQL Tuning Expert Series - Understanding Indexes

Подписаться 2 тыс.

Просмотров 56 тыс.

50% 1

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

Спорт

Опубликовано:

6 апр 2015

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 32

@hoki8296 Месяц назад

He is a Great instructor. I learned a lot. Thank you.

@waldoaraya3058 2 года назад

Excellent talk. Crystal clear explanation. Thanks a lot. Greetings from Chile-SoutAmerica.

@rjairtel 6 лет назад

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...

@necbranduc 4 года назад

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!

@mmaller0319 6 лет назад

Excellent Presentation.Cleared a lot of concepts.

@prachideshmukh9527 7 лет назад

Excellent Video. I could able to solve my performance problem with this information. Thanks Ross

@DebabrataPatnaik 8 лет назад

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.

@boganpies 8 лет назад

Thanks Debabrata. Yes, the demo worked better than I had any right to expect, mostly thanks to Jordan's sublime comic timing.

@mohan1958 10 месяцев назад

Great work.

@itminds5452 8 лет назад

good to learn and understand in simple ways

@quannguyen185 5 лет назад

thank you so much for your sharing.

@mantu1234567890 8 лет назад

where can I find the link to more presentation s of urs. loads of things to learn from you

@mahmudurkhan5754 4 года назад

Wow, he is the best

@vitoroliodasda 8 лет назад

excellent.

@mgml3330 7 лет назад

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

@boganpies 7 лет назад

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.

@309saurabh 8 лет назад

Thank you Ross! Excellent video, where can I find the video for next session?

@boganpies 8 лет назад

It hasn't been made yet. But subscribe to the channel so you don't miss out.

@waldoaraya3058 2 года назад

Where can I fin the next session video?

@mantu1234567890 8 лет назад

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 RU-vid. Can you help me finding those. Thanks in advance.

@boganpies 8 лет назад

There's no more Oracle presentations ... yet. But if you follow the DWS channel, RU-vid will let you know when they arrive.

@mantu1234567890 8 лет назад

Thanks Ross.

@rahulkumaragrawal8113 5 лет назад

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.

@damajikalunge7896 5 лет назад

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?

@mantu1234567890 8 лет назад

Ross you are awesome. I need more links to your video s

@boganpies 8 лет назад

Cheers Parag. If only I could turn kudos into cash :-)

@mantu1234567890 8 лет назад

He he he... :)... Yeah, I agree with you. Thanks anyways.

@waldoaraya3058 2 года назад

Where can we download the slides? It would be very helpful. Thanks a lot

@jayak3768 2 года назад

22:09 How come an 8kb block with int as 8 byte only holds 400 entries. It should be 8kb/8 bytes, 1000 entries.

@itminds5452 8 лет назад

what r disadvantage of index

@DebabrataPatnaik 8 лет назад

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.