Something fun to do, a word search!!
Ric Ramblings
by
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
Visit website
Cloud World 2024
Ric Ramblings
by
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
Visit website
EXISTS vs EQUALITY - sand in the gears
Ric Ramblings
by
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
Visit website
A misunderstanding about the Materialize hint
Ric Ramblings
by
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
Visit website
CTEs and UNIONs, Wrap it up!
Ric Ramblings
by
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
Visit website
Using the MATERIALIZE hint correctly
Ric Ramblings
by
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
Visit website
Optimizing your SQL starts before you write any code
Ric Ramblings
by
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
Visit website
Looking back after 21,915 days.
Ric Ramblings
by
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
Visit website
Automatic Plan Management – not the cure all.
Ric Ramblings
by
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
Visit website
REGEXP_LIKE and LIKE
Ric Ramblings
by
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
Visit website

Follow Ric Ramblings on FeedSpot

Continue with Google
Continue with Apple
OR