Skip to Main Content

Write Great SQL Office Hours

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

Header container

April 16, 2019

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 https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html
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 give us feedback and be notified of upcoming sessions!

Ask a Question

Ask The Experts - Right Now!

Do you have a question about SQL you'd like our experts to answer in their next session? Sign in and submit it here.

Please note that we cannot guarantee to answer all questions. We cannot help you with open Service Requests or account/licensing issues.

Answers

Answers
Question
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. ~!@#$%^&*()_+`-={}|[]\:";'<>>?,./
Answer

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

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

Experts

Your Experts
Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is the quizmaster for the Database Design competitions and an Oracle Developer Advocate for SQL. 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.