
Ric Ramblings
969 FOLLOWERS
Ric Van Dyke is an Oracle Ace with over 30 years of experience in the IT industry. Coming from a 3GL programing back ground he started working with Oracle Version 6 as a developer with Forms 2.3 and became a DBA at Ford Motor Credit.
Ric Ramblings
3M ago
From days gone by. I had this in some of the courses I
wrote while at Hotsos as a filler for some blank pages.
Enjoy!
  ..read more
Ric Ramblings
6M ago
Hey! I’ll be presenting at Cloud World this year! Woot-woot! I think the last time I was at this event it was still called Open World and it was still in San Francisco. Yea been a while.
Improve Custom BIP Reports: from Hours to Minutes [THR2938]
Wednesday, Sep 11, 1:50 PM - 2:10 PM PDT
And yes, I’ll get to talk about my favorite topic, CTEs!!
If you happen to be going to this spectacular event, stop by and say Hi!   ..read more
Ric Ramblings
11M ago
The best thing about doing SQL Optimization is there is always something that needs attention.
The worse thing about doing SQL Optimization is there is always something that needs attention.
A colleague in my team ran across some code that looked like this:
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE
FROM big_tab bigtab
WHERE 1 = ( SELECT 1 FROM allusers_tab alluserstab
WHERE bigtab.owner = alluserstab.username
an ..read more
Ric Ramblings
1y ago
I think I have found out why some folks are resisting using this hint when defining a CTE (Common Table Expression). There apparently is a misunderstanding about what this hint does.
This doesn’t cause the CTE to be kept in memory (the PGA) after the query finishes. This only forces the optimizer to not “merge” it back into the main query. A materialized CTE will store the results set in a GTT (Global Temp Table) like structure during the run of the statement. This GTT is dropped once the query finishes.
I say again, the GTT i ..read more
Ric Ramblings
1y ago
CTEs (Common Table Expressions) can solve a lot of performance issues. Maybe not all of them, but a majority of them.
I ran into a situation recently that was quite frustrating. A customer I was working with had created a rather large and complex CTE that was 3 select statements with the UNION ALL operation pulling them together. I’m a huge fan of the /*+ MATERIALIZE */ hint for CTEs as this will keep the CTE as a separate entity. Using this hint more times than not is best for performance.
But this CT ..read more
Ric Ramblings
1y ago
I see queries like this from time to time:
SELECT /*+ materialize */ * FROM (SELECT bla-bla-bla…);
I’m pretty sure that the MATERIALIZE hint is doing nothing when used in this way. The MATERIALIZE hint is for CTEs (common table expressions) crated using the WITH clause. I am unable to see what is does for any other use. If someone can send me a repeatable test case that I can run showing it does something in other cases I’m open to being proven wrong.
This is the type of statement where the hint works as expected ..read more
Ric Ramblings
1y ago
Writing good SQL is more about how you think than what you write. The problem that most of us have is that we think in terms of individual rows when we really need to think more in terms of sets of rows.
Of course, this is natural for us to do since the end product is the individual rows, and we don’t necessarily think of them as a set. But they are. They are a set that satisfies the conditions that the business defines for the data they want.
We need to think this way always when writing and opt ..read more
Ric Ramblings
2y ago
This March I hit the milestone of turning 60. It’s been quite a ride. And it seems appropriate to pass on some thoughts on life to others.
I tell my kids 3 things that they should do in life. These are not hard or difficult to do, it’s the doing them that is the hard part.
Save more Money. Seems simple enough and we’ve all heard variations on this likely throughout our lives. The issue is you don’t really understand how important this is until you are in your older years. Like I am ..read more
Ric Ramblings
2y ago
There has been a decent amount of buzz out there about Oracle’s Automatic SQL Plan Management. It’s not exactly new, this feature has its roots in 11. Starting in 19 for Autonomous databases it is on by default. Which isn’t necessarily a bad thing.
My concern is that folks may make a bad assumption about how this works.
The actually assumption that this feature works on is that the code is well written to begin with. This isn’t a mechanism that will be able to rewrite code to make it better, it will just m ..read more
Ric Ramblings
2y ago
I don’t use REGEXP much but ran across this interesting issue just recently.
Apparently REGEXP_LIKE doesn’t like parentheses in search strings, the LIKE operator doesn’t have an issue with parentheses.
The full code will be at the bottom of this if you’d like to try it yourself. I did run this on a super-cool Autonomous Database, version 21.3 so it’s not an old version thing.
What I do in the test is compare a column form one table to another, this is simulating the problem that I ran into recently. In the string are some parentheses. And you’ll noti ..read more