Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

April 16

13:00 UTC   Start Times Around the World


SQL Performance & Index Rebuilds
In April's session we looked at the impact of reading old "cold" data into the buffer cache. We also reviewed index maintenance and discussed when - if ever - you should rebuild an index (hint: the answer's almost always never).

Highlights include:

01:20 - The impact of reading old data into the buffer cache
11:30 - Using direct path reads & writes to load data into the PGA instead of buffer cache
14:10 - Using parallel DML to do direct path operations; challenges with doing this
21:45 - Avoid moving data by using partition exchange
27:40 - Recap of techniques to reduce buffer cache contention

30:40 - Are index rebuilds necessary?
31:25 - Overview of B-tree index structure
33:30 - B-tree index maintenance demo using
42:20 - When is an index rebuilt useful?
45:00 - Using coalesce to reclaim space in an index
58:00 - Using an index rebuild to change its physical properties; e.g. move to a new tablespace or change compression level
1:03:00 - Recap of index rebuilding

Subscribe to be notified of changes to sessions and give us feedback!

Having trouble watching the video on this page? Open the video in your browser.

Your Experts

Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is an Oracle Developer Advocate and quizmaster on Dev Gym. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
Keith Laker
Keith Laker, Senior Principal Product Manager    
I have been working with Oracle data warehouse technology for over 20 years working on a wide variety of data warehouse projects both as a consultant and an onsite support engineer. I am now part of the Data Warehouse Product Management Team where I am responsible for analytical SQL. I am based in the UK at our Manchester office. A key part of my role is to work with our sales teams to brief our customers on data warehousing and analytical SQL: explaining the wide variety of new and exciting opportunities that our DW and analytical solutions can support. I regularly deliver sales training for data warehousing and analytical SQL across all our sales regions and provide competitive intelligence support across all the major data warehouse vendors.


Hi, This is the table t2 I have, Sl.No. Junk 1. Cigarette use – last used 4/2017 – NS at best; 2. test]]]]]]] 3. [[[[test 4. [CDATA[]] Now I want to write query to get only the 1st row which have junk character i.e. – I try the query below, select t.* from t2 t where regexp_like(junk, '[^a-zA-Z0-9~!@#$%^&*()_+-=;{}\|'':"<>?,./` ]'); but it gives all 4 rows as I didn't put [ and ] in regex, and if use [ and ] in regex as below, select t.* from t2 t where regexp_like(junk, '[^a-zA-Z0-9~!@#$%^&*()_+-=;{}\|'':"<>?,./`\]\[ ]'); it will not fetch any rows How can I write the query to get only those data which if it contains character which is not a-Z, A-Z, 0-9 and all those special characters in keyboard i.e. ~!@#$%^&*()_+`-={}|[]\:";'<>>?,./

You can use the POSIX character class [:punct:] to match symbols:

SQL> with rws   Show more.
Answered by Chris Saxon -