Category Archives: Oracle Internal

Aggregate Function , Distinct and NULL Values in Oracle.

While working on SQL Server with queries having aggregation got a warning message. “Warning: Null value is eliminated by an aggregate or other SET operation.” So thought let write up something on Aggregation and NULL Values for Tradition RDBMS for … Continue reading

Rate this:

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

#SQLSERVERLearning : Exception on Invalid/Incorrect Hints?

All my blogs with #SQLSERVERLearning is intended to cover all stuff identified as difference between Oracle and SQL Server. Hopefully it might be helpful for Oracle guy also working in SQL Server world. All findings or Observations with SQL Server … Continue reading

Rate this:

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

#SQLSERVERLearning : Does Unique Index Stored NULL Values in Index?

All my blogs with #SQLSERVERLearning are intended to cover all stuff identified as difference between Oracle and SQL Server. Hopefully it might be helpful for any Oracle guy also working with SQL Server. All finding or Observations with SQL Server … Continue reading

Rate this:

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

Should we gather stats on Global Temporary Table within any Stats Job?

Global Temporary table(GTT) are meant to store data specific to a session and it only persist’s till session lifetime. Most of Stats gathering job, also gather underlying stats on global temp table. Pre Oracle 12c, Underlying gathered statistics on Global … Continue reading

Rate this:

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

Gather Stats on Function based filter condition in oracle

For one of performance improvement, identify root cause for bad execution plan was incorrect cardinality estimation on function based condition on a table column. As part of recommendation, we just needed to get estimated cardinality improve on table filter conditions. … Continue reading

Rate this:

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

Alias on Rownum might degrade performance in oracle.

We use First’s row filtering(using Rownum) for lot of day to day functional implementation, Pagination being one of the best example. Rownum clause is best way to impose First – N Rows optimization and restrict subset of projected rows as … Continue reading

Rate this:

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

Incorrect Refer to a column alias causing performance degradation

For easy of referencing and better code readability we add aliases to table reference in most of queries. For correlated queries we add conditions based on correlation columns between any two tables or entities. Below is one of query shared … Continue reading

Rate this:

Posted in Oracle Internal | Tagged , | Leave a comment

NVL Function Internal Behaviour in Oracle for Scalar Calls.

During a performance analysis, identified a NVL Scalar Function calls was eating up lot of SQL time. Scalar function was like below, NVL ( A_Table_Column, (SELECT COUNT(*) FROM TABLE_B WHERE <>) ) If we check Scalar Columns, we are ideally … Continue reading

Rate this:

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

Histogram Statistics on Function based Index hidden column.

In one of previous post, had discussed on METHOD_OPT Params for different histogram stats setup. Current post is just an extension of previous post. If need more information on same, please check these out METHOD OPT Param for Stats in … Continue reading

Rate this:

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

Basic Understanding of Autonomous Transaction in Oracle

Most of us are aware of Use of Autonomous transactions in oracle and what benefit it exhibit. just to elaborate on same, It help to Perform SQL operations(Especially committed DML) independent of primary transaction. In precise, Its shares no lock … Continue reading

Rate this:

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