Set based is faster? Well – not always

March 15, 2024 NiceTheoryVidarLeave a comment

I was about to optimise some code from using FORALL to work set based, but a small test changed my mind.

Function result cache, yes or no?

May 31, 2023 NiceTheoryVidarLeave a comment

Oracles function result cache (FRC) works, in simplified terms, by caching the result of a PL/SQL function. This can greatly improve performance, but it can also lead to performance problems.

Forall is still row-by-row

May 28, 2023 NiceTheoryVidar1 Comment

I came over some code that used the forall construct to update rows in a large table. Quite a few rows were updated and the batch took some time to complete. Usually, using forall is a good idea, but sometimes other constructs can perform better.

Which tables could need updated statistics?

May 22, 2023May 26, 2023 NiceTheoryVidarLeave a comment

Does your tables need updated statistics for the optimizer? Here’s a couple of views that could be useful in the gathering of information.

Statistics gathering parameters for partitioned tables

May 20, 2023May 22, 2023 NiceTheoryVidarLeave a comment

When partitioned tables get a bit large, these parameters may be suitable to set.

DataGrip cutting DDL short, case 2

May 19, 2023May 22, 2023 NiceTheoryVidarLeave a comment

My preferred tool for writing PL/SQL code has been DataGrip from JetBrains for the last few years. Working on a case with reference partitioned tables, I came over some strange errors. At first I thought Oracle had a lot of bugs in the partitioning syntax, but it turned out to be DataGrip messing with my DDL!

DataGrip cutting DDL short, case 1

December 21, 2022December 21, 2022 NiceTheoryVidar1 Comment

Proxy user logon #JoelKallmanDay

October 11, 2022 NiceTheoryVidarLeave a comment

Are you using proxy logon when connecting to your Oracle database? Why not?

Generating data: union all vs insert all

February 11, 2021December 21, 2022 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. This is how I did it.