Welcome, Guest
Username: Password: Remember me

TOPIC: substitution in a --group expression

substitution in a --group expression 2 years 5 months ago #319

Hi,
I'm trying to remove hints from the sql displayed in the output of:

mrskew --group=substr($sql,0,70) --where1=1 *.trc

I guess I need to fit s/\\/\\*\\+.*\\*\\/// in there somewhere. Tried several methods unsuccessfully, now into guesswork too embarrassing to post! Any help gratefully received
The administrator has disabled public write access.

Re:substitution in a --group expression 2 years 5 months ago #320

Brian,

Don't feel bad; it's a complicated thing you're trying to do.

We can suggest two solutions.

Here's the first one. It uses --init to define a subroutine (Perl syntax) that then can be called by the --group expression:
mrskew *.trc --init='sub f($) { my $x = $_[0]; for ($x) { s:/\\*\\+.*?\\*/::g; }; return $x; }' --group='substr(f($sql), 0, 50)'
One subtlety to notice is in the regular expression /\\*\\+.*?\\*/. The .*? construct calls for a non-greedy match. It makes the RE work correctly for a string like "select /*+ hint1 */ stuff /*+ hint2 */". Without the ?, the RE processor would have eaten the string " stuff " as well as both hints, which would give an incorrect result.

The second one is simpler:
mrskew *.trc --group='substr(($sql =~ s:/\\*\\+.*?\\*/::g, $sql)[1], 0, 50)'
This one constructs a list of two elements. The first element executes the =~ operator, which returns a count of matches, but changes its lvalue. The second element references the changed value of $sql. The [1] appended to the list means to select only the 1th element of the list as the --group expression's ultimate value.

Note that it's important in either case to do the substr() operator after the regular expression processing. Otherwise, you risk not having the closing */ delimiter in the string in which you're searching for hints.
Last Edit: 2 years 5 months ago by Cary Millsap.
The administrator has disabled public write access.

Re:substitution in a --group expression 2 years 5 months ago #321

Cary
This is just perfect! The second gives me exactly what I want. I'll play with the first in a quiet moment later. Thank you so much!
Brian
The administrator has disabled public write access.