Hi, Edit: Thanks to Chris Saxon who is from asktom team, checked my code and warned me about a problem. Not to overload the PGA fetch_rows function only fetches 1024 rows at once so every row set chunk size is 1024. In this case for the 1025th row, previous row information is lost because fetch_rows
SQL / PLSQL
SQL Macro Part2 column_value = nvl(:param, column_value)
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
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
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
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
Performance Gain of NOCOPY Parameters
Hi, Almost every Oracle database uses PLSQL codes like packages/procedures/functions etc. Complex systems with complex codes pass many arguments (parameters) many times between plsql objects. Passing parameters might be the most executed operations in a database. So how well do you pass your parameters? I assume you know about types of parameters like IN or
Oracle Join Methods
Hi, Actually I won’t explain anything today. I will just share a video by Chris Saxon who is also from askTom team. Great guy, you should also follow him. So he made a great, so simple and explanatory video that I have ever seen. Many thanks to Chris. Please watch.
Min/Max on same column
Hi, Let’s do a simple trick. You want to get min and max value of a column which is already indexed. As you know if you have an btree index on a column and try to get a Min or Max value then you will see an “Index Full Scan(Min/Max)” line in the execution plan.
Oracle 18c New feature Private Temporary Table
Hello, 18c introduced a new object called Private Temporary Table (I will call it PTT in this post). PTT is just another version of Global Temporary Table(GTT). The data is temporary and will be stored for a time. Data in temporary tables (both PTT and GTT) can be queried by the session which inserts the