Author Archives: Deepak Mahto

About Deepak Mahto

Having 10 Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.

Oracle 19c – Hint Reports with DBMS_XPLAN

With Oracle 19c, we got a feature which was most awaited one. It tries to answer, Whether hint is getting applied, its valid and getting used or not with DBMS_Xplan package. Initially, we used to get 10053 trace to understand … Continue reading

Rate this:

Posted in Oracle Internal, Oracle Performance | Tagged , , | Leave a comment

Generate Script for Foreign key’s in SQL Server.

During conducting one of the Proof Of Concept around “InMemory“, we had to disable foreign key to use Migration wizard provided by SSMS. Before disable, we had to create script for all foreign key for multiple tables in Database and … Continue reading

Rate this:

Posted in Helper SQL, SQL Server Learning | Tagged , , , , , , , , , | Leave a comment

Adding Default Column to a table in Oracle and SQL Server.

Adding a column to an existing table with default exhibit many question. For instance What would be value for that column on existing rows ? What would happen if new column added is Null? What would happen if new column … Continue reading

Rate this:

Posted in Oracle Internal | Leave a comment

Table Alias in Oracle and SQL Server.

Sometimes we assume things to work and not test it, but it surprise you and give you a reason to learn. We have a product that works on top of both Oracle and SQL Server, hence we are bound to … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , , | Leave a comment

CHAR Datatype and ConCat in Oracle and SQL Server.

Char is a Fixed length data type, provided by database to stored Character Data. It will allocate fix bytes as per length specified for CHAR datatype and will pad with Space for remaining chunks. With Variable declare as CHAR(20), Byte … Continue reading

Rate this:

Posted in SQL Server Learning | Tagged , , , | Leave a comment

SQL SERVER# : Get last possible time for a DAY.

During one of SQL requirement, was trying to form Range of Years for executing a DML in small batches as per YEAR. But due to missing last possible end time, we were not able to manipulate some records. For instance … Continue reading

Rate this:

Posted in Helper SQL, SQL Server Learning | Tagged , , , | Leave a comment

FIXED length character storage and NULL in SQL Server.

Most of us must be aware of CHAR datatype and its fixed length storage pattern. If we declare CHAR(32) and store only 4 bytes character ‘ABCD’, It will pads up remaining bytes (28 bytes). Ultimately, whole bytes is consume for … Continue reading

Rate this:

Posted in SQL Server Learning | Tagged , , , , | Leave a comment

APPROX_COUNT_DISTINCT in Oracle 12c.

Most of time during performance analysis to understand Stale stats, we usually need to check estimated NDV (Number of Distinct values). If data-set is huge , it take considerable time to project desired output using traditional COUNT(DISTINCT) appraoch. For any … Continue reading

Rate this:

Posted in Oracle 12c New Features, Oracle Internal, Oracle Performance | Tagged , , , | Leave a comment

Lateral Views in Oracle 12c.

#TGIF. In Oracle 12c, we got introduce to LATERAL Views officially(Before same was getting use internally as part of Query Transformation) to join Inline views, Pre 12c we were not able to achieve same of joining inline views. With Lateral … Continue reading

Rate this:

Posted in Oracle 12c New Features, Oracle Internal | Tagged , , | Leave a comment

SQL Server : Value Constructor Insight.

We all must be aware of VALUE Clause for Inserting values specified, in current blob we will explore different ways other then Insert were we can have use VALUE as handy.! 1. Generating Dummy Data! we can use Value to … Continue reading

Rate this:

Posted in SQL Server Learning | Tagged , , | Leave a comment