Skip to content
  • Oracle
  • About
  • Oracle
Search
Close

– nice theory…

– Good and not so good ideas on software development and Oracle databases in particular

Category: SQL

Generating data: union all vs insert all

February 11, 2021February 11, 2021 NiceTheoryVidarLeave a comment

To test out different physical table lay-outs, I had to repeatedly generate some test-data. After a few runs, I wanted to speed it up a bit.

Snail with rocket
Read More »

A small unexpected to_date oddity

September 28, 2020December 8, 2020 NiceTheoryVidarLeave a comment

I was about to do some date/time calculations as I got an unexpected result with the to_date-function.

Read More »

Reviewing statements with hints, using PL/Scope

December 10, 2018December 7, 2018 NiceTheoryVidarLeave a comment

In my previous post, I showed how you can add a fake hint to tag the origin of a duplicated statement and as a side-effect make it unique. What if you regret and can’t remember where it was. Or you want to review old hinted statements after an upgrade to a newer version of the database/optimizer.

Read More »

Differentiating between duplicated statements

December 7, 2018 NiceTheoryVidarLeave a comment

Finding duplicate SQL statements using PL/Scope is easy. If you cannot merge them, how can you differentiate between which source is being run?

Read More »

Find bad performing queries or code with PL/Scope

November 30, 2018December 4, 2018 NiceTheoryVidar5 Comments

On our production system we’ve enabled the collection of PL/Scope metadata. Since this is a SmartDB/PinkDB-application (business logic and queries in the database), this makes it really easy to find, inspect and modify the source code of queries that doesn’t run efficiently. Now it’s even easier using reports in Oracle SQL Developer.

Read More »

Matching strings with a combination of tools

February 28, 2018February 28, 2018 NiceTheoryVidarLeave a comment

I had a case where I had to match some names and needed to find a set of ways to clean and match strings. The solution I ended up with was a combination of regular expressions, the NLSSORT-function and the UTL_MATCH-package with the Jaro-Winkler algorithm.

Read More »

TRIM: More than meets the eye

January 18, 2018January 19, 2018 NiceTheoryVidarLeave a comment

I’ve always just used the TRIM-function to remove leading and trailing spaces. While going through Markus Winands presentation “Four* Major Database Release of 2017 in Review” on SlideShare.net, I realized that the TRIM function can do more than just remove spaces.

Read More »

Blog at WordPress.com.
Back to top
  • Follow Following
    • - nice theory...
    • Join 312 other followers
    • Already have a WordPress.com account? Log in now.
    • - nice theory...
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...