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

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

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

 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

 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

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

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

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

SQL is great and to make it more readable comments are wonderful.  Most code need more comments, some a lot more.  I know you're saying “Hey this code was hard to write, so it should be hard to read!”  Yea, no.  I tend write comments for me, not you.  I might have to maintain this code in the future and I’d like to at least give myself some idea of why I did certain things. But let’s talk about HOW to do your comments.  Please use the /*  */ syntax more than not. Why?  Take a look at this fictional code bock: -- Getting base information for XTLOP p ..read more

I was in a recent Tweeter debate about the use of SELECT * within CTEs (Common Table Expressions) in particular.  But really this goes beyond just using them in CTEs.    Sure the * (star or splat as some folks say) is a nice short cut to get all the columns from a table (or set of tables).  But it can be a problem.     For example, take this simple query:   select * from emp, dept where emp.deptno = dept.deptno;   This works just fine in SQL*Plus (or SQLcl).  There is a duplicate column name in the two table ..read more