Hi, Since I am working on “SQL Macro”, I am keep testing things. so please see my previous post: http://mustafakalayci.me/2020/10/28/sql-macro-is-finally-here/ as mentioned in previous post, is it possible to get rid of column_value = nvl(:param, column_value) conditions? since they become a huge pain in big and complex queries. I provided a very cumbersome solution in
Development
SQL Macro is finally here!
Hi, I was waiting for this for a long time. Finally we can use sql macro in Oracle database. Actually I was excited when I first read about this but after my tests, I stuck at some points. I was hoping to use this as “parameterized views” as in, send a parameter and use it
Direct Path Insert &/vs Conventional Insert
Hi there, I couldn’t write anything for a while. Those days were busy. So I would talk to about direct path insert and conventional insert. Many people think that they are using “direct path insert” when used sqlldr utility but they are not. it is easy to confused batch insert and direct path insert. of
Flashback Data Archive Problems!
Hi, if you don’t know about flashback data archive you can check this post first: http://mustafakalayci.me/2019/03/02/goodbye-log-triggers-welcome-flashback-data-archive/ I mentioned about flashback data archive (FDA) in that post and really flattered it. Well, maybe I shouldn’t! FDA is really fast dml tracking method but thanks to Peter Schlaeger, I noticed some potential problems and “some” solutions. Few
Oracle Table Compression Part1
Hi, I have been dealing with table compression a lot during my development period and I still do. I wanted to share some information about table information but I believe this will be a long explanation so I will write them in parts and this is part1. Table compression is a very useful feature of
Timestamp Arithmetic and Well Known Mistakes
Hi, Lately, I started to see many developer uses timestamp types (like timestamp or timestamp with time zone) wrong especially in time arithmetic. So as a short reminder, adding and subtracting numbers to/from a DATE value will add/subtract number of days: Basically that is all if you use add and subtract of course we
Frequent and Fast Refresh Materialized Views has a Big Problem!
Hi, While I was checking V$SQL_PLAN view for a whole different reason, I realized that some of the cursors has incredibly high child cursors. I started to dig and finally I find the problem. My customer have a few frequently fast refreshed materialized views. One of them is refreshed every 10 seconds (you might say
CSV Data in Clob to Columns Using Select
Hi, Thanks to one of my customers, I am dealing with csv data in clob lately a lot. so they asked me if I can get a csv data in a clob column as separated columns using select statement. They wanted to show this data on their applicaitons. I suggested different methods but they said
Is It Possible to Change SQL Statement in the Database?
YES! Hi, today I want to write about something which has been asked for many times to me. Let’s say you have an application and it is running very poorly designed sql statements in your database and you are really bored having fights with development teams to ask a change about the sql. Well, in
V$SqlStats vs V$SqlStats_Plan_Hash, different columns?
Hi there, V$SQLSTATSis a beautiful view for information about sql statements. you might say v$sql has that information too which is correct but V$SQLSTATSis much more faster than v$sql and keeps data about sql statements for a longer time than v$sql. https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-SQLSTATS.html However, the V$SQLSTATS view differs from V$SQL and V$SQLAREA in that it is faster, more scalable, and has a