tag:blogger.com,1999:blog-88731091250231428102024-03-18T15:17:47.153+05:30Let's Develop in OracleNimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.comBlogger308125tag:blogger.com,1999:blog-8873109125023142810.post-90664667868234633542022-03-20T10:24:00.002+05:302022-03-20T10:24:44.767+05:30Analytical SQL - Running Total - Order by without Partition ByWelcome to the third post on the series <a href="http://nimishgarg.blogspot.com/search/label/Analytical%20SQL?max-results=100" target="_blank">Analytical SQL</a>. In the first two posts we studied <div><ol style="text-align: left;"><li><a href="http://nimishgarg.blogspot.com/2022/02/analytical-sql-sum-without-window-clause.html">Analytical Function without Window Clause</a> </li><li><a href="http://nimishgarg.blogspot.com/2022/02/analytical-sql-sum-with-partition-by.html">Analytical Function Partition By</a></li></ol></div><div><br /></div><div>In this post we will create a report showing a <b>running total</b> of the complete EMP table in order of HIREDATE.
To create a running total for the complete dataset, <b>PARTITION BY is not required</b>, we just need to <b>add ORDER BY in OVER analytic clause
</b><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select d.deptno, d.dname, empno, ename, hiredate, sal,
2 sum(sal) over(order by hiredate) running_total
3 from emp e, dept d
4 where e.deptno = d.deptno;
DEPTNO DNAME EMPNO ENAME HIREDATE SAL RUNNING_TOTAL
---------- -------------- ---------- ---------- --------- ---------- -------------
20 RESEARCH 7369 SMITH 17-DEC-80 800.75 800.75
30 SALES 7499 ALLEN 20-FEB-81 1600.9 2401.65
30 SALES 7521 WARD 22-FEB-81 1250 3651.65
20 RESEARCH 7566 JONES 02-APR-81 2975.5 6627.15
30 SALES 7698 BLAKE 01-MAY-81 2850.3 9477.45
10 ACCOUNTING 7782 CLARK 09-JUN-81 2450.45 11927.9
30 SALES 7844 TURNER 08-SEP-81 1500.95 13428.85
30 SALES 7654 MARTIN 28-SEP-81 1250.05 14678.9
10 ACCOUNTING 7839 KING 17-NOV-81 5000.15 19679.05
20 RESEARCH 7902 FORD 03-DEC-81 3000.6 23630.64
30 SALES 7900 JAMES 03-DEC-81 950.99 23630.64
10 ACCOUNTING 7934 MILLER 23-JAN-82 1300 24930.64
20 RESEARCH 7788 SCOTT 19-APR-87 3000.55 27931.19
20 RESEARCH 7876 ADAMS 23-MAY-87 1100.01 29031.2
</code></pre>
As you can see in above output, data is ordered by HIREDATE, and RUNNING_TOTAL column is calculating SUM from the first row till the current row.</div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com18tag:blogger.com,1999:blog-8873109125023142810.post-2290070295517010842022-02-20T21:20:00.000+05:302022-02-20T21:20:04.602+05:30Analytical SQL - SUM with Partition By <p>Welcome to the second post on the series <b><span style="color: red;"><a href="http://nimishgarg.blogspot.com/search/label/Analytical%20SQL?max-results=100" target="_blank">Analytical SQL</a></span></b>. </p><p>In the first post we studied <a href="http://nimishgarg.blogspot.com/2022/02/analytical-sql-sum-without-window-clause.html" target="_blank">Analytical Function without Window Clause</a>. With this post, we will learn <b>Partition By</b>. </p><p><b>Analytical functions</b> compute an aggregated value based on a group of rows defined as per <b>Partition By</b> clause, which determines the range of rows used to perform the calculations. </p><p>In last post, we calculated TOTAL SAL in EMP table against each row, using</p><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>select d.deptno, d.dname, empno, ename,
sum(sal) over() total
from emp e, dept d
where e.deptno = d.deptno;
</code></pre><div><br /></div>
Now, I want to modify TOTAL SAL to DEPARTMENT TOTAL SAL, i.e. calculate total salary of the department against department of each particular row.
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select d.deptno, d.dname, empno, ename, sal,
2 sum(sal) over(partition by d.dname) total
3 from emp e, dept d
4 where e.deptno = d.deptno;
DEPTNO DNAME EMPNO ENAME SAL TOTAL
---------- -------------- ---------- ---------- ---------- ----------
10 ACCOUNTING 7839 KING 5000.15 8750.6
10 ACCOUNTING 7934 MILLER 1300 8750.6
10 ACCOUNTING 7782 CLARK 2450.45 8750.6
20 RESEARCH 7566 JONES 2975.5 10877.41
20 RESEARCH 7788 SCOTT 3000.55 10877.41
20 RESEARCH 7902 FORD 3000.6 10877.41
20 RESEARCH 7369 SMITH 800.75 10877.41
20 RESEARCH 7876 ADAMS 1100.01 10877.41
30 SALES 7521 WARD 1250 9403.19
30 SALES 7654 MARTIN 1250.05 9403.19
30 SALES 7844 TURNER 1500.95 9403.19
30 SALES 7900 JAMES 950.99 9403.19
30 SALES 7499 ALLEN 1600.9 9403.19
30 SALES 7698 BLAKE 2850.3 9403.19
</code></pre>
If you want to achieve above output <b>without Analytical Function</b>, you can also try
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>select d.deptno, d.dname, empno, ename, sal, total
from emp e, dept d,
(select sum(sal) total, deptno from emp group by deptno) t
where d.deptno = e.deptno
and d.deptno = t.deptno
order by 1;
</code></pre>
<p></p>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com18tag:blogger.com,1999:blog-8873109125023142810.post-10874013011024343092022-02-13T13:18:00.003+05:302022-02-13T13:18:47.798+05:30Analytical SQL - SUM without Window Clause<p>This is my first post in 2022, and with this I am starting a new series on <b>Analytical SQL</b>. </p><p>In The first example, we are trying to list all records from EMP and DEPT tables with the SUM of SAL column with each row. As you can see here, we have used "sum(sal) over()" without WINDOW ( partition by), which made Oracle to process all rows in table.</p><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>
select d.deptno, d.dname, empno, ename,
sum(sal) over() total
from emp e, dept d
where e.deptno = d.deptno;
DEPTNO DNAME EMPNO ENAME TOTAL
---------- -------------- ---------- ---------- ----------
10 ACCOUNTING 7839 KING 29031.2
30 SALES 7698 BLAKE 29031.2
10 ACCOUNTING 7782 CLARK 29031.2
20 RESEARCH 7566 JONES 29031.2
20 RESEARCH 7788 SCOTT 29031.2
20 RESEARCH 7902 FORD 29031.2
20 RESEARCH 7369 SMITH 29031.2
30 SALES 7499 ALLEN 29031.2
30 SALES 7521 WARD 29031.2
30 SALES 7654 MARTIN 29031.2
30 SALES 7844 TURNER 29031.2
20 RESEARCH 7876 ADAMS 29031.2
30 SALES 7900 JAMES 29031.2
10 ACCOUNTING 7934 MILLER 29031.2
</code></pre>
Above SQL is equivalent to
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>select d.deptno, d.dname, empno, ename,
(select sum(sal) from emp) total
from emp e, dept d
where e.deptno = d.deptno;
</code></pre><div><br /></div><b>
Related Posts</b> <div>-
<a href="http://nimishgarg.blogspot.com/search/label/Analytical%20Functions">Analytical Functions</a>
<p></p></div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com5tag:blogger.com,1999:blog-8873109125023142810.post-24305931059278718642021-09-09T15:46:00.001+05:302021-09-09T15:46:26.810+05:30Oracle Database 21c for on-premises is here - New Features<p><b><span style="color: red;">Oracle Database 21c</span></b> is the latest <b>innovation release</b> of world's most popular database. Oracle Database 21c is now <b>available on Oracle cloud and on-premises for linux </b>including Exadata. </p>
<p>You can directly <b>upgrade to Oracle Database 21c from</b> the following releases:<br />
- Oracle Database 19c<br />
- Oracle Database 18c<br />
- Oracle Database 12c Release 2 (12.2)</p><b>Top New features in Oracle Database 21c:</b><br />- Blockchain Tables<div>- Immutable Tables<br />- Oracle Machine Learning<div>- Native JSON Datatype</div><div>- SQL Macros<div>- In database JavaScript execution</div><div>- Compare explain plans / cursors</div><div><p>We all should be aware that <b>Innovation Releases have shorter support windows</b> than Long Term Releases such as Oracle Database 19c.</p>
<p><b><span style="color: red;">Oracle Database Innovation Releases </span></b>include many enhancements and new capabilities which will be included in the next Long Term Release. <b>Oracle Database Innovation Releases does not have extended support</b>. As Oracle Database 21c is also an innovation release, which means it will only <b>have 2 years of premium support</b>. </p>
<p><b><span style="color: red;">Oracle Database Long Term Releases</span></b> usually have <b>5 years of Premier Support followed by 3 years of Extended Support</b>. Personally I would not like to upgrade my production servers to an innovation release (Oracle Database 21c). I would like to upgrade (or stay with my Oracle Database Server with <b>Oracle Database 19c which has Premier Support till April 30, 2024</b> and after that Extended Support will start for Oracle Database 19c.</p><p>I personally would suggest every-one to upgrade their Oracle Database to 19c not to 21c. We can upgrade our database once Oracle Database 23c Database is released.</p>
<p>You can find complete <b>documentation </b>of Oracle Database 21c at <br /><a href="- https://docs.oracle.com/en/database/oracle/oracle-database/21/whats-new.html" target="_blank">https://docs.oracle.com/en/database/oracle/oracle-database/21/</a></p><p></p><div class="separator" style="clear: both; text-align: center;"><br /></div><br /><b>Related Links-</b><p></p>
- <a href="http://nimishgarg.blogspot.com/2020/05/automatic-indexing-in-oracle-19c.html">Automatic Indexing in Oracle 19c Autonomous Database</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/03/top-18-features-of-oracle-18c.html">Top 18 features of Oracle 18c</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/10/oracle-18c-database-autonomous-database.html">Oracle 18c Database - Autonomous Database Cloud</a><br />
- <a href="http://nimishgarg.blogspot.com/2016/10/top-15-new-features-of-oracle-database.html">Top 15 new features of Oracle Database 12.2 for developers</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/11/oracle-12c-partitioning-new-features.html">Oracle 12c Partitioning New Features - Top 10</a><br />
- <a href="http://nimishgarg.blogspot.com/2015/09/json-in-oracle-database-with-examples.html">JSON in Oracle Database with Examples</a><br /></div></div></div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com5tag:blogger.com,1999:blog-8873109125023142810.post-56200416104456626212021-06-02T13:19:00.001+05:302021-06-02T13:21:01.562+05:30Oracle: String to Date, Timestamp with TimezoneI recently got a text from a friend to convert '2021-04-07T14:03:54.000000-0700' string to date.<div><br /></div><div>He Tried -<br /><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select
2 to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM')
3 from dual;
to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM')
*
ERROR at line 2:
ORA-01821: date format not recognized
</code></pre>
but it was failing with <b>ORA-01821: date format not recognized</b>.<div><br /></div><div><b><span style="color: red;">There are 2 issues in above SQL.</span></b> </div><div><br /></div><div><b>1. The format in the SQL is itself incorrect. </b>The correct format is 'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'.</div><div><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select
2 to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
3 from dual;
to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
*
ERROR at line 2:
ORA-01821: date format not recognized
</code></pre><b>
2. The use of the TO_DATE function is incorrect also</b>, the value is timestamp with timezone, so we can try to_timestamp instead of to_date
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select
2 to_timestamp('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
3 from dual;
to_timestamp('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
*
ERROR at line 2:
ORA-01821: date format not recognized
</code></pre>
but to_timestamp is also failing. </div><div><br /></div><div>Oracle provides <b>to_timestamp_tz to convert a string to timestamp with timezone</b>.
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select
2 to_timestamp_tz('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
3 from dual;
TO_TIMESTAMP_TZ('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.
---------------------------------------------------------------------------
07-APR-21 02.03.54.000000000 PM -07:00
</code></pre>
Now we just need to convert this to date.
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select
2 cast(
3 to_timestamp_tz('2021-04-07T14:03:54.000000-0700',
4 'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'
5 ) as date
6 )
7 from dual;
CAST(TO_TIMESTAMP_TZ
--------------------
07-Apr-2021 14:03:54
</code></pre>
If someone do not want to go into these details, he can simply remove the data after seconds and then convert it to date
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select
2 to_date(
3 regexp_replace('2021-04-07T14:03:54.000000-0700','\..+'),
4 'YYYY-MM-DD"T"HH24:MI:SS'
5 )
6 from dual;
TO_DATE(REGEXP_REPLA
--------------------
07-Apr-2021 14:03:54
</code></pre><b><div><b><br /></b></div>
Related Posts - </b></div><div>- <a href="http://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html">Oracle: Some Important Date Queries</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2011/03/playing-with-truncate-and-date.html">Playing With Truncate and Date</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2016/03/prefer-oracle-native-date-arithmetic.html">Prefer Oracle Native Date Arithmetic over ANSI INTERVAL</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2014/04/ora-01830-date-format-picture-ends.html">ORA-01830 date format picture ends before converting entire input string</a></div></div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com59tag:blogger.com,1999:blog-8873109125023142810.post-55224325490731415762021-02-17T14:20:00.002+05:302021-02-17T14:20:22.079+05:30Star Pattern by SQL in Oracle Database<b>Star Patterns by SQL in Oracle -
</b><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>nimish@garg> select lpad('*',level,'*') from dual connect by level <= 5;
LPAD('*',LEVEL,'*')
---------------------------------------------------------------------------------
*
**
***
****
*****
nimish@garg> select lpad('*',5-level+1,'*') from dual connect by level <= 5;
LPAD('*',5-LEVEL+1,'*')
---------------------------------------------------------------------------------
*****
****
***
**
*
nimish@garg> select lpad(' ',5-level,' ') || lpad('*',level,'*') from dual connect by level <= 5;
LPAD('',5-LEVEL,'')||LPAD('*',LEVEL,'*')
------------------------------------------------------------------------------------------------------
*
**
***
****
*****
nimish@garg> select lpad(' ',level-1,' ') || lpad('*',5-level+1,'*') from dual connect by level <= 5;
LPAD('',LEVEL-1,'')||LPAD('*',5-LEVEL+1,'*')
---------------------------------------------------------------------------------------------------------
*****
****
***
**
*
nimish@garg> select lpad('*',5-level+1,'*') || lpad('*',level*2-1,' ') || lpad('*',5-level,'*') star_pattern
2 from dual connect by level <= 5
3 union all
4 select lpad('*',level,'*') || lpad('*',(5-level)*2+1,' ') || lpad('*',level-1,'*')
5 from dual connect by level <= 5;
STAR_PATTERN
-----------------------------------------------------------------------------------------------------------------
**********
**** ****
*** ***
** **
* *
* *
** **
*** ***
**** ****
**********
10 rows selected.
nimish@garg> select lpad(' ',5-level+1,' ') || lpad('*',level*2-1,'*') diamond
2 from dual connect by level <= 5
3 union all
4 select lpad(' ',level+1,' ') || lpad('*',(5-level)*2-1,'*') diamond
5 from dual connect by level <= 5;
DIAMOND
-----------------------------------------------------------------------------------------------------------------
*
***
*****
*******
*********
*******
*****
***
*
10 rows selected.
</code></pre>
<b><div><b><br /></b></div>Related Posts -</b> <div>- <a href="http://nimishgarg.blogspot.com/2020/02/sql-puzzle-calendar-of-current-year.html">SQL Puzzle - Calendar of Current Year</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2014/02/sql-puzzle-grouping-deals.html">SQL Puzzle - Grouping Deals</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2015/08/sql-puzzle-sorting-versions-stored-in.html">SQL Puzzle - Sorting Versions stored in Varchar2 Column</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2018/09/sql-puzzle-transpose-rows-and-shift.html">SQL Puzzle - Transpose Rows and Shift Values among columns</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2013/12/sql-puzzle-consecutive-wins.html">SQL Puzzle - Consecutive Wins</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2015/07/diamond-shaped-star-pattern-by-sql.html">Diamond Shaped Star Pattern by SQL</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2014/06/graph-shortest-path-solution-by-sql.html">Graph Shortest Path Solution by SQL</a>
</div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com8tag:blogger.com,1999:blog-8873109125023142810.post-32654273384932330492021-02-02T16:56:00.009+05:302021-02-02T16:56:56.571+05:30LISTAGG DISTINCT - Remove Duplicates - Oracle 19c <b>LISTAGG</b> function was introduced in Oracle 11g. LISTAGG function aggregates the result set in multiple rows into one single column. <div><br /></div><div>In <b>Oracle 12c R2 </b>LISTAGG function was enhanced to manage situations where the length of the concatenated string is too long by providing us ON OVERFLOW TRUNCATE clause. </div><div><br /></div><div>With <b>Oracle 19c</b> Database LISTAGG function can also remove the duplicate values by using <b>DISTINCT</b> keyword. This feature was requested by many developers from the inception of LISTAGG function, and now we do not need to write complex SQL to remove the duplicates from the list.</div><div><br /></div><div><span style="color: red;"><b>Following SQL is the example of LISTAGG with DISTINCT keyword in Oracle 19c.</b></span></div><div><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select d.deptno, dname,
2 listagg(distinct job, ',') within group (order by job) jobs_in_dept
3 from dept d, emp e
4 where e.deptno = d.deptno
5 group by d.deptno, dname;
DEPTNO DNAME JOBS_IN_DEPT
---------- -------------- --------------------------------------------------
10 ACCOUNTING CLERK,MANAGER,PRESIDENT
20 RESEARCH ANALYST,CLERK,MANAGER
30 SALES CLERK,MANAGER,SALESMAN
</code></pre>
If we execute the above SQL <b>without DISTINCT keyword</b>, we will get following result.
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select d.deptno, dname,
2 listagg(job, ',') within group (order by job) jobs_in_dept
3 from dept d, emp e
4 where e.deptno = d.deptno
5 group by d.deptno, dname;
DEPTNO DNAME JOBS_IN_DEPT
---------- -------------- --------------------------------------------------
10 ACCOUNTING CLERK,MANAGER,PRESIDENT
20 RESEARCH ANALYST,ANALYST,CLERK,CLERK,MANAGER
30 SALES CLERK,MANAGER,SALESMAN,SALESMAN,SALESMAN,SALESMAN
</code></pre><b><span style="color: red;">
In Oracle 18c or lower </span></b>versions of Oracle database (like Oracle 12c or Oracle 11g), we can <b>remove the duplicate values by removing the duplicate first in nested SQL</b> (or in WITH clause) and then using the LISTAGG function.
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select deptno, dname,
2 listagg(job, ',') within group (order by job) jobs_in_dept
3 from
4 (
5 select distinct
6 d.deptno, d.dname, e.job
7 from dept d, emp e
8 where e.deptno = d.deptno
9 )
10 group by deptno, dname;
DEPTNO DNAME JOBS_IN_DEPT
---------- -------------- --------------------------------------------------
10 ACCOUNTING CLERK,MANAGER,PRESIDENT
20 RESEARCH ANALYST,CLERK,MANAGER
30 SALES CLERK,MANAGER,SALESMAN
</code></pre>
We can <b>also use REGEXP_REPLACE to remove the duplicates</b> from the list generated by LISTAGG
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> with data as
2 (
3 select d.deptno, dname,
4 listagg(job, ',') within group (order by job) jobs_in_dept
5 from dept d, emp e
6 where e.deptno = d.deptno
7 group by d.deptno, dname
8 )
9 select deptno, dname,
10 regexp_replace(jobs_in_dept,'([^,]+)(,\1)+', '\1') jobs_in_dept
11 from data;
DEPTNO DNAME JOBS_IN_DEPT
---------- -------------- --------------------------------------------------
10 ACCOUNTING CLERK,MANAGER,PRESIDENT
20 RESEARCH ANALYST,CLERK,MANAGER
30 SALES CLERK,MANAGER,SALESMAN
</code></pre><b><div><b><br /></b></div>
Related Posts -</b> </div><div>- <a href="http://nimishgarg.blogspot.com/2019/07/listagg-on-overflow-truncate-oracle-12c.html">LISTAGG - ON OVERFLOW TRUNCATE - Oracle 12c R2 enhancement</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2010/07/oracle-differece-between-wmconcat-and.html">Oracle: Difference between wm_concat and ListAgg</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2015/12/comma-separated-values-in-table-to-rows.html">Comma Separated Values in Table to Rows</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2010/02/oracle-new-string-aggregation.html">Oracle: New String Aggregation Techniques</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2020/12/oracle-database-21c-is-here-innovation.html">Oracle Database 21c is here - Innovation Release</a> </div><div>- <a href="https://nimishgarg.blogspot.com/2020/05/automatic-indexing-in-oracle-19c.html">Automatic Indexing in Oracle 19c Autonomous Database</a> </div><div>- <a href="https://nimishgarg.blogspot.com/2020/06/parse-json-data-in-oracle-database.html">Parse JSON data in Oracle Database using JSON_TABLE in SQL</a></div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com3tag:blogger.com,1999:blog-8873109125023142810.post-63467971695672414072021-01-11T13:49:00.007+05:302021-01-11T13:49:49.234+05:30Redo Files, Redo Group and Redo Thread in Oracle Database<p><b>Redo log files </b>used to maintain logs of all transactions performed against the Oracle database mainly for disaster recovery. An Oracle database must have at least two redo log files. These files are written in a circular fashion by the LGWR process. </p><p>If Oracle database is running in archivelog mode, then ARCH process copies the log file to ARCHIVE_LOG_DEST directory at the time of LOG SWITCH. </p><p>Following is the <b>SQL which provides basic information about Redo Log files</b> on my Oracle 18c XE database.
</p><pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "Andale Mono", "Lucida Console", Monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>select
log.thread#,
log.group#,
file.member,
log.archived,
log.status,
(bytes/1024/1024) SIZE_MB
from
v$log log,
v$logfile file
where
f.group# = l.group#
order by 1,2 ;
THREAD# GROUP# MEMBER ARC STATUS SIZE_MB
------- ------- ----------------------------------------- --- ---------- --------
1 1 C:\ORACLEXE\18.0.0\ORADATA\XE\REDO01.LOG NO INACTIVE 200
1 2 C:\ORACLEXE\18.0.0\ORADATA\XE\REDO02.LOG NO CURRENT 200
1 3 C:\ORACLEXE\18.0.0\ORADATA\XE\REDO03.LOG NO INACTIVE 200
</code></pre><b>
Following are the detail about columns generated by above SQL </b><div>-
<b>THREAD#</b> - This column is basically useful in RAC environment where multiple instances concurrently access single database and each instance has its own thread of redo. </div><div>- <b>GROUP#</b> - A redo log group consist of Redo log file and optionally its identical copies. Here we have 3 redo log groups with one file each. </div><div>- <b>MEMBER</b> - All the member files in each Redo log group. </div><div>- <b>ARCHIVED </b>- If file has been archived by ARCH process, happens only if Oracle database is running in archivelog mode. </div><div>- <b>STATUS</b> - CURRENT, INACTIVE, ACTIVE, CLEARING and UNUSED </div><div>- <b>SIZE_MB</b> - Size of the redo log file in MB. </div><div><br /></div><div><b>Related Posts - </b></div><div>- <a href="http://nimishgarg.blogspot.com/2016/11/tune-complete-refresh-of-materialized.html">Tune Complete Refresh of Materialized View by atomic_refresh=>false parameter of dbms_mview.refresh</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2010/09/oracle-improve-import-export.html">Oracle: Improve Import/ Export performance (imp / exp)</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2012/01/ora-00257-archiver-error-connect.html">ORA-00257 archiver error. Connect internal only, until freed</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2013/06/ora-27101-shared-memory-realm-does-not.html">ORA-27101: shared memory realm does not exist</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2014/01/ora-01157-cannot-identifylock-data-file.html">ORA-01157: cannot identify/lock data file string - see DBWR trace file</a><p></p></div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com0tag:blogger.com,1999:blog-8873109125023142810.post-63719190515074338892020-12-22T13:59:00.000+05:302020-12-22T13:59:18.602+05:30Oracle Autonomous Database scored highest in Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases" report<p><b>
Oracle</b> is named as a Leader in 2020 <b>Gartner Magic Quadrant</b> for <b>Cloud Database Management Systems</b>. <b><span style="color: red;">Oracle Autonomous Transaction Processing</span></b> (ATP) ranked highest in 2020 <b>Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases" </b>report.</p><div style="text-align: left;"><b><span style="color: red;">
Gartner "Critical Capabilities for Cloud Database Management Systems for Operational Use Cases"</span></b> evaluates cloud DBMS products for their suitability to support following four operational use cases - </div><div style="text-align: left;">1) traditional transactions </div><div style="text-align: left;">2) augmented transaction processing </div><div style="text-align: left;">3) operational intelligence </div><div style="text-align: left;">4) stream/event processing. </div><div style="text-align: left;"><br /></div><div style="text-align: left;">As per <b>Gartner report</b>, Oracle ATP is positioned No. 1 out of all products evaluated for all four use cases. Its high evaluation is due to it being the same mature DBMS that has been used on-premises for over 40 years. </div><div style="text-align: left;"><br /></div><div style="text-align: left;">Oracle Autonomous Database was launched just two years ago. <b><span style="color: red;">Oracle Autonomous Database is optimized for</span></b> transaction processing, data warehousing, and JSON document processing. Oracle Autonomous Database has following <b>main features</b> </div><div style="text-align: left;">1. runs natively on Oracle Cloud Infrastructure </div><div style="text-align: left;">2. automatically provisions highly available databases </div><div style="text-align: left;">3. configures and tunes for specific workloads </div><div style="text-align: left;">4. scales compute resources when needed. </div><div style="text-align: left;"><br /></div><div style="text-align: left;">Oracle’s unique converged database enables organizations to use one database versus many specialized databases for all of their workloads, decreasing complexity, cost and risk for customers. </div><div style="text-align: left;"><br /></div><div style="text-align: left;">Additionally, <b>Oracle Autonomous Data Warehouse (ADW) ranked first in Operational Intelligence</b> and second in the three other use cases in the 2020 Gartner "Critical Capabilities for Cloud Database Management Systems for Analytical Use Cases" report. </div><div style="text-align: left;"><br /></div><div style="text-align: left;"><b>Following is the link of Oracle's Press Release </b></div><div style="text-align: left;">https://www.oracle.com/news/announcement/oracle-recognized-in-gartner-cloud-database-market-reports-121420.html </div><div style="text-align: left;"><br /></div><div style="text-align: left;"><b><span style="color: red;">Related Links - </span></b></div><div style="text-align: left;">- <a href="http://nimishgarg.blogspot.com/2017/10/oracle-18c-database-autonomous-database.html">Oracle 18c Database - Autonomous Database Cloud</a> </div><div style="text-align: left;">- <a href="http://nimishgarg.blogspot.com/2018/03/top-18-features-of-oracle-18c.html">Top 18 features of Oracle 18c</a> </div><div style="text-align: left;">- <a href="http://nimishgarg.blogspot.com/2019/09/oracle-cloud-introducing-new-always.html">Oracle Cloud - Introducing New Always Free Services</a> </div><div style="text-align: left;">- <a href="http://nimishgarg.blogspot.com/2020/05/automatic-indexing-in-oracle-19c.html">Automatic Indexing in Oracle 19c Autonomous Database</a> </div><div style="text-align: left;">- <a href="http://nimishgarg.blogspot.com/2020/12/oracle-database-21c-is-here-innovation.html">Oracle Database 21c is here - Innovation Release</a> </div><div style="text-align: left;">- <a href="http://nimishgarg.blogspot.com/2018/02/oracle-cloud-performance-benchmark-by.html">Oracle Cloud Performance Benchmark by Accenture</a></div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com2tag:blogger.com,1999:blog-8873109125023142810.post-39011118199164986542020-12-10T13:15:00.002+05:302020-12-10T13:17:45.528+05:30Oracle Database 21c is here - Innovation Release<p><b><span style="color: red;">Oracle Database 21c</span></b> is the latest <b>innovation release</b> of world's most popular database. Oracle Database 21c is initially <b>available on Oracle cloud</b> with Autonomous Database Free Tier and Database Cloud Service.</p>
<p>You can directly <b>upgrade to Oracle Database 21c from</b> the following releases:<br />
- Oracle Database 19c<br />
- Oracle Database 18c<br />
- Oracle Database 12c Release 2 (12.2)</p>
<p><b><span style="color: red;">Oracle Database Innovation Releases </span></b>include many enhancements and new capabilities which will be included in the next Long Term Release. <b>Oracle Database Innovation Releases does not have extended support</b>. As Oracle Database 21c is also an innovation release, which means it will only <b>have 2 years of premium support</b>. </p>
<p><b><span style="color: red;">Oracle Database Long Term Releases</span></b> usually have <b>5 years of Premier Support followed by 3 years of Extended Support</b>. Personally I would not like to upgrade my production servers to an innovation release (Oracle Database 21c). I would like to upgrade (or stay with my Oracle Database Server with <b>Oracle Database 19c which has Premier Support till April 30, 2024</b> and after that Extended Support will start for Oracle Database 19c.</p>
<p>You can find complete <b>documentation </b>of Oracle Database 21c at <br /><a href="- https://docs.oracle.com/en/database/oracle/oracle-database/21/whats-new.html" target="_blank">- https://docs.oracle.com/en/database/oracle/oracle-database/21/whats-new.html</a></p>
<p><img alt="" data-original-height="704" data-original-width="1194" height="236" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0nvhIBTC4Lrp4y2BlK520ZpYgkGMT4X-A8EJg_vbrBdY0-qka-obs8mV-S8wxJszeIxjpY8WS0wFAJbRygpf-d_gUekc7AUVciCZh-Gd3tKwAlnKQM43Xnix8xt-zm75SHKpZ9PyQrlMZ/w400-h236/image.png" width="400" /></p><p><br /></p><b>
Related Links-</b><br />
- <a href="http://nimishgarg.blogspot.com/2020/05/automatic-indexing-in-oracle-19c.html">Automatic Indexing in Oracle 19c Autonomous Database</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/03/top-18-features-of-oracle-18c.html">Top 18 features of Oracle 18c</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/10/oracle-18c-database-autonomous-database.html">Oracle 18c Database - Autonomous Database Cloud</a><br />
- <a href="http://nimishgarg.blogspot.com/2016/10/top-15-new-features-of-oracle-database.html">Top 15 new features of Oracle Database 12.2 for developers</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/11/oracle-12c-partitioning-new-features.html">Oracle 12c Partitioning New Features - Top 10</a><br />
- <a href="http://nimishgarg.blogspot.com/2015/09/json-in-oracle-database-with-examples.html">JSON in Oracle Database with Examples</a><br />Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com1tag:blogger.com,1999:blog-8873109125023142810.post-42045779381211796952020-08-23T14:13:00.008+05:302020-08-23T14:28:00.730+05:30Generate Nested JSON using SQL in Oracle DatabaseIt has been a long time, since I wrote my last post on <a href="http://nimishgarg.blogspot.com/2020/06/parse-json-data-in-oracle-database.html">Parse JSON data in Oracle Database</a>. I got lot of questions on my last post mainly how did I create the nested JSON data for the demo. So with this post, I am sharing the <b>SQL which I used to create the nested JSON data </b>for demo. Oracle Database provides JSON_OBJECT, which creates JSON from the resulting SQL. We just need to pass output Schema to input schema mapping. I am using Oracle Autonomous Database for this demo. <pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> SELECT D.DEPTNO ID,
2 JSON_OBJECT (
3 'DEPTNO' VALUE D.DEPTNO,
4 'DNAME' VALUE D.DNAME,
5 'EMPLOYEES' VALUE JSON_ARRAYAGG
6 (
7 JSON_OBJECT
8 (
9 'EMPNO' VALUE EMPNO,
10 'ENAME' VALUE ENAME,
11 'JOB' VALUE JOB,
12 'SAL' VALUE SAL
13 )
14 ) FORMAT JSON
15 ) JSON_VALUE
16 FROM DEPT D, EMP E
17 WHERE D.DEPTNO = E.DEPTNO
18 GROUP BY D.DEPTNO, D.DNAME;
ID JSON_VALUE
---------- ------------------------------------------------------------------------------------------------------------------------
10 {"DEPTNO":10,"DNAME":"ACCOUNTING","EMPLOYEES":[{"EMPNO":7782,"ENAME":"CLARK","JOB":"MANAGER","SAL":2450.45},{"EMPNO":783
9,"ENAME":"KING","JOB":"PRESIDENT","SAL":5000.15},{"EMPNO":7934,"ENAME":"MILLER","JOB":"CLERK","SAL":1300}]}
20 {"DEPTNO":20,"DNAME":"RESEARCH","EMPLOYEES":[{"EMPNO":7902,"ENAME":"FORD","JOB":"ANALYST","SAL":3000.6},{"EMPNO":7876,"E
NAME":"ADAMS","JOB":"CLERK","SAL":1100.01},{"EMPNO":7369,"ENAME":"SMITH","JOB":"CLERK","SAL":800.75},{"EMPNO":7566,"ENAM
E":"JONES","JOB":"MANAGER","SAL":2975.5},{"EMPNO":7788,"ENAME":"SCOTT","JOB":"ANALYST","SAL":3000.55}]}
30 {"DEPTNO":30,"DNAME":"SALES","EMPLOYEES":[{"EMPNO":7521,"ENAME":"WARD","JOB":"SALESMAN","SAL":1250},{"EMPNO":7698,"ENAME
":"BLAKE","JOB":"MANAGER","SAL":2850.3},{"EMPNO":7499,"ENAME":"ALLEN","JOB":"SALESMAN","SAL":1600.9},{"EMPNO":7900,"ENAM
E":"JAMES","JOB":"CLERK","SAL":950.99},{"EMPNO":7844,"ENAME":"TURNER","JOB":"SALESMAN","SAL":1500.95},{"EMPNO":7654,"ENA
ME":"MARTIN","JOB":"SALESMAN","SAL":1250.05}]}</code></pre><b><div><b><br /></b></div>
Related Posts </b><div>- <a href="http://nimishgarg.blogspot.com/2015/09/json-in-oracle-database-with-examples.html">JSON in Oracle Database with Examples</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2017/03/convert-xml-to-rows-and-columns-in.html">Convert XML to Rows and Columns in Oracle</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2017/02/generate-xml-data-using-sql-in-oracle.html">Generate XML data using SQL in Oracle Database</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2013/04/load-xml-file-in-oracle-table.html">Load XML File in Oracle Table</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2010/11/generate-xml-of-output-of-query.html">XML output from SQL query</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2011/09/create-csv-file-using-plsql.html">Create CSV file using PL/SQL</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2013/04/load-csv-file-in-oracle-using-plsql.html">Load CSV file in Oracle using PL/SQL</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2019/09/sqlloader-load-excelcsv-file-into.html">SQL*Loader - Load Excel/CSV file into Oracle Database Table</a> </div><div>- <a href="http://nimishgarg.blogspot.com/2018/10/sqlformat-csv-json-xml-format-output-of.html">SQLFormat - CSV, JSON, XML </a></div><div><a href="http://nimishgarg.blogspot.com/2018/10/sqlformat-csv-json-xml-format-output-of.html">- Format output of SQL in SQLDeveloper</a> </div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com5tag:blogger.com,1999:blog-8873109125023142810.post-58428485778869673112020-06-29T10:54:00.002+05:302020-08-23T14:28:40.237+05:30Parse JSON data in Oracle Database using JSON_TABLE in SQL<div dir="ltr" style="text-align: left;" trbidi="on">
Recently, I got opportunity to work on <b><span style="color: red;">JSON data in Oracle Database</span></b>. We were receiving this JSON data from Cisco Webex APIs, and the JSON data was complex (nested with json array). With this blog, I am sharing simple example <b>how to parse complex JSON data in Oracle Database using SQL</b>. <br />
<br />
For this example, I am using SCOTT schema, and my current database version is <b>Oracle 12.1</b> which was release in July 2013. With Current versions of Oracle 18c and 19c and Oracle Autonomous Databases, there has been lot of enhancements to parse JSON data. Anyways, let us <b>first create DEPT_JSON table</b>, which will have <b>JSON_VALUE column of CLOB type</b>. One JSON_VALUE field will store complete information of a department and employees data of that department in JSON format.</div><div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> CREATE TABLE DEPT_JSON
2 (
3 ID NUMBER,
4 JSON_VALUE clob
5 );
Table created.
ngarg> INSERT INTO DEPT_JSON
2 VALUES(10,
3 '{"DEPTNO": "10",
4 "DNAME": "ACCOUNTING",
5 "EMPLOYEES" : [{ EMPNO: 7839, "ENAME": "KING", SAL: 5000, "JOB": "PRESIDENT"},
6 { EMPNO: 7782, "ENAME": "CLARK", SAL: 2450, "JOB": "MANAGER"},
7 { EMPNO: 7934, "ENAME": "MILLER", SAL: 1300, "JOB": "CLERK"}
8 ]
9 }'
10 );
1 row created.
ngarg> INSERT INTO DEPT_JSON
2 VALUES(20,
3 '{"DEPTNO": "20",
4 "DNAME": "RESEARCH",
5 "EMPLOYEES" : [{ EMPNO: 7566, "ENAME": "JONES", SAL: 2975, "JOB": "MANAGER"},
6 { EMPNO: 7788, "ENAME": "SCOTT", SAL: 3000, "JOB": "ANALYST"},
7 { EMPNO: 7902, "ENAME": "FORD", SAL: 3000, "JOB": "ANALYST"},
8 { EMPNO: 7369, "ENAME": "SMITH", SAL: 800, "JOB": "CLERK"},
9 { EMPNO: 7876, "ENAME": "ADAMS", SAL: 1100, "JOB": "CLERK"}
10 ]
11 }'
12 );
1 row created.
ngarg> INSERT INTO DEPT_JSON
2 VALUES(30,
3 '{"DEPTNO": "30",
4 "DNAME": "RESEARCH",
5 "EMPLOYEES" : [{ EMPNO: 7698, "ENAME": "BLAKE", SAL: 2850, "JOB": "MANAGER"},
6 { EMPNO: 7499, "ENAME": "ALLEN", SAL: 1600, "JOB": "SALESMAN"},
7 { EMPNO: 7521, "ENAME": "WARD", SAL: 1250, "JOB": "SALESMAN"},
8 { EMPNO: 7654, "ENAME": "MARTIN", SAL: 1250, "JOB": "SALESMAN"},
9 { EMPNO: 7844, "ENAME": "TURNER", SAL: 1500, "JOB": "SALESMAN"},
10 { EMPNO: 7900, "ENAME": "JAMES", SAL: 950, "JOB": "CLERK"}
11 ]
12 }'
13 );
1 row created.
ngarg> commit;
Commit complete.
</code></pre>
<br />
As you would have noticed, I have <b>not specified any different data type to store JSON</b>, JSON is simply being stored in CLOB field. Now we can use following <b><span style="color: red;">SQL to parse Nested JSON data along with JSON Array in Oracle Database by using JSON_TABLE function</span></b>. <br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed rgb(153, 153, 153); color: black; font-family: "andale mono", "lucida console", monaco, fixed, monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> SELECT
2 DEPTNO, DNAME, EMPNO, ENAME, JOB, SAL
3 FROM
4 DEPT_JSON D,
5 JSON_TABLE
6 (
7 D.JSON_VALUE, '$' COLUMNS
8 (
9 DEPTNO NUMBER(4) PATH '$.DEPTNO',
10 DNAME VARCHAR2(15) PATH '$.DNAME',
11 NESTED path '$.EMPLOYEES[*]' COLUMNS
12 (
13 EMPNO NUMBER(5) PATH '$.EMPNO',
14 ENAME VARCHAR2(15) PATH '$.ENAME',
15 JOB VARCHAR2(15) PATH '$.JOB',
16 SAL NUMBER(7,2) PATH '$.SAL'
17 )
18 )
19 ) J
20 ORDER BY DEPTNO;
DEPTNO DNAME EMPNO ENAME JOB SAL
---------- --------------- ---------- --------------- --------------- ----------
10 ACCOUNTING 7839 KING PRESIDENT 5000
10 ACCOUNTING 7934 MILLER CLERK 1300
10 ACCOUNTING 7782 CLARK MANAGER 2450
20 RESEARCH 7369 SMITH CLERK 800
20 RESEARCH 7788 SCOTT ANALYST 3000
20 RESEARCH 7566 JONES MANAGER 2975
20 RESEARCH 7902 FORD ANALYST 3000
20 RESEARCH 7876 ADAMS CLERK 1100
30 RESEARCH 7698 BLAKE MANAGER 2850
30 RESEARCH 7499 ALLEN SALESMAN 1600
30 RESEARCH 7521 WARD SALESMAN 1250
30 RESEARCH 7900 JAMES CLERK 950
30 RESEARCH 7844 TURNER SALESMAN 1500
30 RESEARCH 7654 MARTIN SALESMAN 1250
14 rows selected.
</code></pre>
<br />
<b>JSON_TABLE can be used in Oracle Database to specify the structure of JSON data, and convert JSON data into relational</b>. As you can see, JSON_TABLE is easy to use. We simply need to define the PATH of each value in JSON and if data is nested we can use NESTED PATH, and Oracle database will automatically convert single JSON data with nested JSON into multiple relational rows. At the end of this post, I would also like to <b><span style="color: red;">recommend </span></b>to read <a href="http://nimishgarg.blogspot.com/2015/09/json-in-oracle-database-with-examples.html">JSON in Oracle Database with Examples</a> to understand the how can we simply use <b>dot (.) notation in SQL to read JSON Data</b> in Oracle database<br />
<br />
<b>Related Posts</b><br />
- <a href="http://nimishgarg.blogspot.com/2015/09/json-in-oracle-database-with-examples.html">JSON in Oracle Database with Examples</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/03/convert-xml-to-rows-and-columns-in.html">Convert XML to Rows and Columns in Oracle</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/02/generate-xml-data-using-sql-in-oracle.html">Generate XML data using SQL in Oracle Database</a><br />
- <a href="http://nimishgarg.blogspot.com/2013/04/load-xml-file-in-oracle-table.html">Load XML File in Oracle Table</a><br />
- <a href="http://nimishgarg.blogspot.com/2010/11/generate-xml-of-output-of-query.html">XML output from SQL query</a><br />
- <a href="http://nimishgarg.blogspot.com/2011/09/create-csv-file-using-plsql.html">Create CSV file using PL/SQL</a><br />
- <a href="http://nimishgarg.blogspot.com/2013/04/load-csv-file-in-oracle-using-plsql.html">Load CSV file in Oracle using PL/SQL</a><br />
- <a href="http://nimishgarg.blogspot.com/2019/09/sqlloader-load-excelcsv-file-into.html">SQL*Loader - Load Excel/CSV file into Oracle Database Table</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/10/sqlformat-csv-json-xml-format-output-of.html">SQLFormat - CSV, JSON, XML - Format output of SQL in SQLDeveloper</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com44tag:blogger.com,1999:blog-8873109125023142810.post-10277108506061163242020-05-08T17:40:00.000+05:302020-05-08T17:40:02.507+05:30Automatic Indexing in Oracle 19c Autonomous Database <div dir="ltr" style="text-align: left;" trbidi="on">
With <b><span style="color: red;">Oracle Database 19c</span></b>, The <b><span style="color: red;">Automatic Indexing</span></b> feature automates the index management by allowing Oracle Database to<b> automatically create, rebuild and drop indexes based on application workload</b>. Oracle Database runs the automatic indexing process in the background, analyzes application workload, and creates new indexes and drops redundant and extra ones.<br />
<br />
<br />
<b>The automatic indexing process runs in background every 15 minutes and performs the following operations:</b><br />1. Identifies auto index candidates based on the usage of table columns in SQL statements.<br />2. Creates invisible auto indexes for the auto index candidates<br />3. The invisible auto indexes are validated against SQL statements.<br /> a. Index are marked visible If the performance is improved<br /> b. Indexes are configured as unusable, If performance of SQL statements is not improved.<br /> c. If performance degrades for some SQLs, the indexes are marked visible except for the these SQLs (blacklisted)<br />4. The auto indexes that are not used for a long period are deleted (373 days by default).<br /><br />
<br />
<b>Note:</b><br />
1. Tables with stale statistics are not considered for auto indexing.<br />
2. Blacklisted SQL statements are not allowed to use auto indexes in future.<br />
<br />
<b><span style="color: red;">Configuring Automatic Indexing</span></b><br />
<b>1. Enable Oracle to Create Automatic Indexes in Visible Mode</b><br />
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');<br />
<br />
<b>2. Enable Oracle to Create Automatic Indexes but in Invisible Mode </b><br />
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');<br />
<br />
<b>3. Disables automatic indexing in a database</b><br />
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');<br />
<br />
<b>4. Add Schema in exclusion list for Auto Indexing</b><br />
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', FALSE);<br />
<br />
<b>5. Remove Schema from exclusion list for Auto Indexing</b><br />
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SH', NULL);<br />
<br />
<b>Also we can use hints in SQL statements to control if auto indexes should be used</b><br />
- /*+ USE_AUTO_INDEXES */ <br />
- /*+ NO_USE_AUTO_INDEXES */ <br />
<br />
<b>Related Links -</b><br />
- <a href="http://nimishgarg.blogspot.com/2018/03/top-18-features-of-oracle-18c.html">Top 18 features of Oracle 18c</a><br />
- <a href="http://nimishgarg.blogspot.com/2016/10/top-15-new-features-of-oracle-database.html">Top 15 new features of Oracle Database 12.2 for developers</a><br />
- <a href="http://nimishgarg.blogspot.com/2013/07/oracle-database-12c-new-features-for.html">Oracle Database 12c New Features for Developers</a><br />
- <a href="http://nimishgarg.blogspot.com/2015/09/json-in-oracle-database-with-examples.html">JSON in Oracle Database with Examples</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/02/oracle-cloud-performance-benchmark-by.html">Oracle Cloud Performance Benchmark by Accenture</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/10/oracle-18c-database-autonomous-database.html">Oracle 18c Database - Autonomous Database Cloud</a><br />
- <a href="http://nimishgarg.blogspot.com/2019/09/oracle-cloud-introducing-new-always.html">Oracle Cloud - Introducing New Always Free Services</a><br />
<br /></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com7tag:blogger.com,1999:blog-8873109125023142810.post-2534285285861755812020-03-15T12:29:00.000+05:302020-03-15T12:29:24.908+05:30SQL to calculate PI using Nilakantha Series<div dir="ltr" style="text-align: left;" trbidi="on">
On the <b>Pi Day</b>, I wrote a <b>SQL to calculate PI(Ď€) with accuracy upto 10 decimal digits using Nilakantha Series</b>. When I shared it with my friends on Twitter and Whatsapp, many of them suggested me write a post on it. <br />
<br />
As per <a href="https://en.wikipedia.org/wiki/Pi#Infinite_series" target="_blank">Wikipedia</a>, The calculation of PI(Ď€) was revolutionized by the development of infinite series techniques. The first written description of an <b>infinite series</b> that could be used <b>to compute PI(Ď€)</b> was laid out <b>in Sanskrit verse by Indian astronomer Nilakantha Somayaji</b> in his Tantrasamgraha, around <b>1500 AD</b>. Nilakantha Series <b>converges more quickly</b> which means that we <b>need to work out fewer terms to become closer to Pi(Ď€)</b>. <br />
<br />
<b><span style="color: red;">Nilakantha Series is described as</span></b><br />
<b>Pi(Ď€) = 3 + 4/(2*3*4) - 4/(4*5*6) + 4/(6*7*8) - 4/(8*9*10) + ...</b><br />
<br />
<br />
Now <b>lets write the SQL to calculate PI(Ď€) with accuracy upto 10 decimal digits using Nilakantha Series</b>, which is actually very simple -<br />
<br />
<b>Step 1: Generate a series of Even Numbers - </b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> select rownum*2 rn from dual connect by level <= 5;
RN
----------
2
4
6
8
10
</code></pre>
<br />
<b>Step 2: Generate each term of divisor in series e.g. (2*3*4), (4*5*6) and so on...</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> with t as
2 (select rownum*2 rn from dual connect by level <= 5)
3 select rn a, rn+1 b, rn+2 c, rn*(rn+1)*(rn+2) val from t;
A B C VAL
---------- ---------- ---------- ----------
2 3 4 24
4 5 6 120
6 7 8 336
8 9 10 720
10 11 12 1320
</code></pre>
<br />
<b>Step 3: Final Step add all the constant values 4 (dividend), SUM all the series and add 3 at end.</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> with t as
2 (select rownum*2 rn from dual connect by level <= 1500)
3 select
4 to_char(3 +
5 sum(
6 (decode(mod(rownum,2),0,-1,1) *
7 4/((rn)*(rn+1)*(rn+2))))
8 ,'9.9999999999'
9 ) pi
10 from t;
PI
-------------
3.1415926535
</code></pre>
<br />
<b>Few points on above step, </b><br />
- I calculated 1500 terms to make PI value accurate till 10 decimal digits.<br />
- I have used to_char(val,'9.9999999999') to print the value in 10 digits format after decimal.<br />
<br />
I hope you have enjoyed this post :)<br />
<br />
<b><span style="color: blue;">Related Posts: </span></b><br />
- <a href="https://nimishgarg.blogspot.com/2012/04/sql-question-answers.html">SQL Interview Question Answers</a><br />
- <a href="https://nimishgarg.blogspot.com/2020/02/sql-puzzle-calendar-of-current-year.html">Calendar of Current Year by SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2014/06/graph-shortest-path-solution-by-sql.html">Graph Shortest Path Solution by SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/07/diamond-shaped-star-pattern-by-sql.html">Diamond Shaped Star Pattern by SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html">Oracle: Some Important Date Queries</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/03/playing-with-truncate-and-date.html">Playing With Truncate and Date</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com0tag:blogger.com,1999:blog-8873109125023142810.post-1090968092533139222020-02-21T14:55:00.000+05:302020-02-21T14:55:50.466+05:30SQL Puzzle - Calendar of Current Year<div dir="ltr" style="text-align: left;" trbidi="on">
With this post, I want to share a <a href="http://nimishgarg.blogspot.com/search/label/Oracle%20Puzzle" target="_blank">SQL Puzzle</a> to <b><span style="color: red;">Generate the Complete Calendar of Current Year</span></b>. As you can see in the desired output<br />
- Month names should not be repeated<br />
- For each month we should have a separate header.<br />
<br />
The solution to generate the calendar of current year is also shared at the end of the post. I would suggest all to try to solve this problem before looking at the solution and please do post your solution in comment box. Enjoy solving the SQL Puzzle and happy SQL learning :)<br />
<br />
<span style="color: red;"><b>Desired Output:</b></span><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
January 01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
February 01
02 03 04 05 06 07 08
09 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
March 01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
April 01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
May 01 02
03 04 05 06 07 08 09
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
June 01 02 03 04 05 06
07 08 09 10 11 12 13
14 15 16 17 18 19 20
21 22 23 24 25 26 27
28 29 30
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
July 01 02 03 04
05 06 07 08 09 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30 31
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
August 01
02 03 04 05 06 07 08
09 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
September 01 02 03 04 05
06 07 08 09 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
October 01 02 03
04 05 06 07 08 09 10
11 12 13 14 15 16 17
18 19 20 21 22 23 24
25 26 27 28 29 30 31
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
November 01 02 03 04 05 06 07
08 09 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30
MONTH SU MO TU WE TH FR SA
--------------- -- -- -- -- -- -- --
December 01 02 03 04 05
06 07 08 09 10 11 12
13 14 15 16 17 18 19
20 21 22 23 24 25 26
27 28 29 30 31
</code></pre>
<br />
<br />
<span style="color: red; font-weight: bold;">Solution: </span>To create the solution, I have used some SQL*Plus formatting options.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> BREAK ON MONTH
ngarg> BREAK ON MONTH SKIP PAGE
ngarg> COL MONTH FORMAT A15
ngarg>
ngarg> with curr_year as
2 (
3 select
4 trunc(sysdate,'year') -1 + level dt
5 from dual
6 connect by level <=
7 add_months(trunc(sysdate,'year'),12) - trunc(sysdate,'year')
8 ),
9 data as
10 (
11 select
12 dt,
13 to_char(dt,'d') d,
14 sum(case when to_char(dt,'d') = 1 or to_char(dt,'dd') = 1 then 1 else 0 end) over (order by dt) week_no
15 from
16 curr_year
17 )
18 select
19 to_char(min(dt),'Month') Month,
20 max(case when d=1 then to_char(dt,'dd') end) sun,
21 max(case when d=2 then to_char(dt,'dd') end) mon,
22 max(case when d=3 then to_char(dt,'dd') end) tue,
23 max(case when d=4 then to_char(dt,'dd') end) wed,
24 max(case when d=5 then to_char(dt,'dd') end) thu,
25 max(case when d=6 then to_char(dt,'dd') end) fri,
26 max(case when d=7 then to_char(dt,'dd') end) sat
27 from
28 data
29 group by week_no
30 order by week_no;
</code></pre>
<br />
<br />
<b>Following are other SQL Puzzles which might interest you - </b><br />
- <a href="https://nimishgarg.blogspot.com/2013/12/sql-puzzle-consecutive-wins.html">SQL Puzzle - Consecutive Wins</a><br />
- <a href="https://nimishgarg.blogspot.com/2014/02/sql-puzzle-grouping-deals.html">SQL Puzzle - Grouping Deals</a><br />
- <a href="https://nimishgarg.blogspot.com/2014/02/sql-puzzle-grouping-deals.html">SQL Puzzle - Grouping Deals</a><br />
- <a href="https://nimishgarg.blogspot.com/2014/06/graph-shortest-path-solution-by-sql.html">Graph Shortest Path Solution by SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/07/diamond-shaped-star-pattern-by-sql.html">Diamond Shaped Star Pattern by SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/08/sql-puzzle-sorting-versions-stored-in.html">SQL Puzzle - Sorting Versions stored in Varchar2 Column</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/02/sql-puzzle-issue-tracker-with-solution.html">SQL Puzzle - Issue Tracker - with Solution Approach</a><br />
- <a href="https://nimishgarg.blogspot.com/2018/09/sql-puzzle-transpose-rows-and-shift.html">SQL Puzzle - Transpose Rows and Shift Values among columns</a><br />
- <a href="https://nimishgarg.blogspot.com/2019/06/sql-puzzle-jobs-taking-more-than-10.html">SQL Puzzle - Jobs taking more than 10 individual minutes in Parallel Jobs Environment</a><br />
- <a href="https://nimishgarg.blogspot.com/2012/04/sql-question-answers.html">SQL Interview Question Answers</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com5tag:blogger.com,1999:blog-8873109125023142810.post-17685044312945596492020-01-26T13:42:00.000+05:302020-01-26T13:42:32.734+05:30AVG Aggregate Function and NULL in Oracle Database<div dir="ltr" style="text-align: left;" trbidi="on">
In my previous post on <a href="http://nimishgarg.blogspot.com/2019/12/oracle-aggregate-functions-count-sum.html" target="_blank">Oracle Aggregate Functions</a>, I explained <b>Oracle Database ignores NULL values with Aggregate Functions</b>, which is perfect while calculating COUNT, MIN, MAX and SUM but sometimes it <b>leads us to wrong values if we do not focus on Oracle Database behavior and business requirements especially while calculating AVERAGE</b>.<br />
<br />
With this post I am trying to explain the <b><span style="color: red;">behavior of AVG aggregate function on column having NULL values</span></b>, and how it may lead us to incorrect output. First <b>let us check following data from EMP table</b>.<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>NGARG> select * from emp where deptno in (select deptno from dept where dname = 'SALES');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850.3 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600.9 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250.05 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500.95 0 30
7900 JAMES CLERK 7698 03-DEC-81 950.99 30
6 rows selected.
</code></pre>
<br />
<b><span style="color: red;">Now Business asks, what is the average commission for Sales department. </span></b><br />
<br />
As we can see, there are 6 employees in Sales department, 4 employees are eligible for commission, 3 employees got commission and total commission given to employees in sales department is 2200. <b>Now depending on business requirement, they might be asking for </b><br />
<b>1.</b> "total commission"/"number of employees" which gives us 366.67 average commission.<br />
<b>2.</b> "total commission"/"number of employees eligible for commission" which gives us 550 average commission.<br />
<b>3.</b> "total commission"/"number of employees who received commission" which gives us 733.33 average commission.<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>NGARG> select count(*), sum(comm), sum(comm)/count(*), avg(comm), sum(comm)/count(nullif(comm,0)) from emp
where deptno in (select deptno from dept where dname = 'SALES');
COUNT(*) SUM(COMM) SUM(COMM)/COUNT(*) AVG(COMM) SUM(COMM)/COUNT(NULLIF(COMM,0))
---------- ---------- ------------------ ---------- -------------------------------
6 2200 366.666667 550 733.333333
</code></pre>
<br />
As you might have noticed, <b>AVG function has ignored all rows having NULL values</b> in COMM column and calculated average by dividing "total commission" (2200) by number of rows having NOT NULL values in COMM column (4) and resulted in 550 average commission for department SALES. This behavior of Oracle Database might not be what business is looking for.<br />
<br />
With following statement I wanted end this post "In Oracle Database <b><span style="color: red;">NULL is represented as NO VALUE, which is very different from 0</span></b> (Zero) value." I hope you have enjoyed the post, please add your feedback in comments.<br />
<br />
<br />
<b>Related Posts - </b><br />
- <a href="http://nimishgarg.blogspot.com/2019/12/oracle-aggregate-functions-count-sum.html">Oracle Aggregate Functions - Count Sum Avg Min Max</a><br />
- <a href="http://nimishgarg.blogspot.com/2012/04/sql-question-answers.html">SQL Interview Question Answers</a><br />
- <a href="https://nimishgarg.blogspot.com/2010/02/oracle-sql-rank-denserank-rownumber.html">Oracle: Rank, Dense_Rank, Row_Number Analytic Functions</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/07/round-trunc-ceil-and-floor-in-oracle.html">ROUND, TRUNC, CEIL and FLOOR in Oracle Database</a><br />
- <a href="http://nimishgarg.blogspot.com/2019/03/difference-between-mod-and-remainder.html">Difference between MOD and REMAINDER function</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/06/order-by-and-null-values-in-oracle.html">Order By and Null values in Oracle Database</a><br />
- <a href="https://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html">Oracle: Some Important Date Queries</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/03/playing-with-truncate-and-date.html">Playing With Truncate and Date in Oracle Database</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/09/not-in-clause-with-null-is-tricky-in.html">NOT IN clause with NULL is tricky in Oracle Database</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/06/examples-of-rollup-and-grouping-sets.html">Rollup and Grouping Sets in Oracle Database</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com3tag:blogger.com,1999:blog-8873109125023142810.post-60821116595395737432019-12-15T15:21:00.000+05:302020-03-15T12:20:33.178+05:30Oracle Aggregate Functions - Count Sum Avg Min Max<div dir="ltr" style="text-align: left;" trbidi="on">
In continuation of <b>basics yet important topics of Oracle Database</b>, with this post, I am trying to explain <b>Oracle Aggregate Functions with Group By and Having clause</b>. Oracle Aggregate functions operate on multiple rows and return a single value for each group. Oracle Aggregate functions also called Group Functions.<br />
<br />
<b>Example 1</b>: <b>COUNT </b>function count number of row for each group. Count(*) and Count(1) are same, Oracle internally translate count(1) to count(*). When we pass a column in count function, it counts all the rows having non NULL values.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> select count(*), count(1), count(empno), count(mgr) from emp;
COUNT(*) COUNT(1) COUNT(EMPNO) COUNT(MGR)
---------- ---------- ------------ ----------
14 14 14 13
</code></pre>
<br />
<b>Example 2:</b> <b>SUM, AVG, MIN and MAX </b>functions calculate total, average, minimum and maximum values respectively.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> select sum(sal), avg(sal), min(sal), max(sal) from emp;
SUM(SAL) AVG(SAL) MIN(SAL) MAX(SAL)
---------- ---------- ---------- ----------
29031.2 2073.65714 800.75 5000.15
</code></pre>
<br />
<b>Example 3:</b> <b>GROUP BY</b> clause allows us to create group for doing aggregation. In this example we have added Group By clause on deptno, which creates groups, and aggregate values for each deptno. All the columns with out aggregate functions used in SELECT and ORDER BY must be part of GROUP BY clause. <br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> select deptno, count(*), sum(sal), avg(sal), min(sal), max(sal)
2 from emp
3 group by deptno;
DEPTNO COUNT(*) SUM(SAL) AVG(SAL) MIN(SAL) MAX(SAL)
---------- ---------- ---------- ---------- ---------- ----------
30 6 9403.19 1567.19833 950.99 2850.3
20 5 10877.41 2175.482 800.75 3000.6
10 3 8750.6 2916.86667 1300 5000.15
</code></pre>
<br />
<b>Example 4:</b> <b>HAVING </b>clause allows us to put filters on Aggregate function, we can not use them in WHERE clause. For example we want to get the departments where more than 3 employees are working.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> select deptno, count(*), sum(sal), avg(sal), min(sal), max(sal)
2 from emp
3 group by deptno
4 having count(*) >= 4;
DEPTNO COUNT(*) SUM(SAL) AVG(SAL) MIN(SAL) MAX(SAL)
---------- ---------- ---------- ---------- ---------- ----------
30 6 9403.19 1567.19833 950.99 2850.3
20 5 10877.41 2175.482 800.75 3000.6
</code></pre>
<br />
<br />
<b>Example 5:</b> At last, we can use aggregate functions with strings and dates also<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>ngarg> select deptno, count(ename), min(ename), max(ename)
2 from emp
3 group by deptno;
DEPTNO COUNT(ENAME) MIN(ENAME) MAX(ENAME)
---------- ------------ ---------- ----------
30 6 ALLEN WARD
20 5 ADAMS SMITH
10 3 CLARK MILLER
ngarg> select count(hiredate), min(hiredate), max(hiredate)
2 from emp
3 group by deptno;
COUNT(HIREDATE) MIN(HIRED MAX(HIRED
--------------- --------- ---------
6 20-FEB-81 03-DEC-81
5 17-DEC-80 23-MAY-87
3 09-JUN-81 23-JAN-82
</code></pre>
<br />
<br />
<b>Related Posts - </b><br />
- <a href="http://nimishgarg.blogspot.com/2012/04/sql-question-answers.html">SQL Interview Question Answers</a><br />
- <a href="https://nimishgarg.blogspot.com/2010/02/oracle-sql-rank-denserank-rownumber.html">Oracle: Rank, Dense_Rank, Row_Number Analytic Functions</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/07/round-trunc-ceil-and-floor-in-oracle.html">ROUND, TRUNC, CEIL and FLOOR in Oracle Database</a><br />
- <a href="http://nimishgarg.blogspot.com/2019/03/difference-between-mod-and-remainder.html">Difference between MOD and REMAINDER function</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/06/order-by-and-null-values-in-oracle.html">Order By and Null values in Oracle Database</a><br />
- <a href="https://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html">Oracle: Some Important Date Queries</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/03/playing-with-truncate-and-date.html">Playing With Truncate and Date in Oracle Database</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/09/not-in-clause-with-null-is-tricky-in.html">NOT IN clause with NULL is tricky in Oracle Database</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/06/examples-of-rollup-and-grouping-sets.html">Rollup and Grouping Sets in Oracle Database</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com6tag:blogger.com,1999:blog-8873109125023142810.post-13744193463674472192019-11-24T20:43:00.000+05:302019-11-24T20:47:30.746+05:30Constraint to Validate Data and Optimize SQL - Manual Partition<div dir="ltr" style="text-align: left;" trbidi="on">
I always suggest developers to <b>add constraints on tables</b> in Oracle Database, as <b><span style="color: red;">constraints not only validate the data, but they also helps Oracle Database Optimizer to choose better execution plan</span></b> and hence tune the SQL. Surprised? Let me share my experience with constraints in Oracle Database, when I first came to know the actual power of constraint. That time I was working with <a href="http://nimishgarg.blogspot.com/2013/01/new-features-for-developers-in-oracle.html" target="_blank">Oracle 11g</a> Standard Edition, as we all know in Partition feature is not available with Oracle 11g Standard Edition. I was asked to design manual partitions for "WORKORDER" as per the workorder status column having 4 values. ('New', 'WIP', 'Closed', 'Cancelled'). <br />
<br />
With this post, I am trying to explain how Oracle Database can use constraint to optimize the a complex SQL. For the demo, I am using my favorite table <b>SCOTT.EMP, and will be creating the partitions as per department name</b>. I have created 3 tables from SCOTT.EMP, each of which contains data for ACCOUNTING, RESEARCH and SALES department respectively.<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> create table emp_accounting as
2 select empno, ename, job, hiredate, sal, dname
3 from emp e, dept d
4 where e.deptno = d.deptno
5 and dname = 'ACCOUNTING';
Table created.
SQL> create table emp_research as
2 select empno, ename, job, hiredate, sal, dname
3 from emp e, dept d
4 where e.deptno = d.deptno
5 and dname = 'RESEARCH';
Table created.
SQL> create table emp_sales as
2 select empno, ename, job, hiredate, sal, dname
3 from emp e, dept d
4 where e.deptno = d.deptno
5 and dname = 'SALES';
Table created.
</code></pre>
<br />
Now, we created a view by doing UNION of all 3 tables, on which user or application will run the SELECT SQL with filter on DNAME column and the <b>Oracle Database should only use the relevant partition (table)</b>.<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> create view emp_view as
2 select * from emp_accounting
3 union all
4 select * from emp_research
5 union all
6 select * from emp_sales;
View created.
SQL> set autot trace
SQL> select * from emp_view
2 where dname = 'RESEARCH';
Execution Plan
----------------------------------------------------------
Plan hash value: 965804354
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 210 | 3 (0)| 00:00:01 |
| 1 | VIEW | EMP_VIEW | 5 | 210 | 3 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | TABLE ACCESS FULL| EMP_ACCOUNTING | 1 | 41 | 2 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMP_RESEARCH | 5 | 195 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| EMP_SALES | 1 | 37 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DNAME"='RESEARCH')
4 - filter("DNAME"='RESEARCH')
5 - filter("DNAME"='RESEARCH')
</code></pre>
<br />
As we can see in above example, <b>filter dname = 'RESEARCH' is doing FULL TABLE SCAN for all 3 tables</b>. Even if we create indexes on DNAME of all 3 tables, it will reduce the SELECT STATEMENT cost, but still touch the indexes and table, along with putting the overhead on all the DML statements. You can create indexes and test it out if you want.<br />
<br />
In process of Optimizing our SELECT STATEMENT on VIEW (custom-partition), we tested CONSTRAINT for our need, and were simply amazed. <br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> alter table emp_sales
2 add constraint emp_sales_ck
3 check (dname = 'SALES');
Table altered.
SQL> alter table emp_research
2 add constraint emp_research_ck
3 check (dname = 'RESEARCH');
Table altered.
SQL> alter table emp_accounting
2 add constraint emp_accounting_ck
3 check (dname = 'ACCOUNTING');
Table altered.
SQL> select * from emp_view
2 where dname = 'RESEARCH';
Execution Plan
----------------------------------------------------------
Plan hash value: 2539605777
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 210 | 3 (0)| 00:00:01 |
| 1 | VIEW | EMP_VIEW | 5 | 210 | 3 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| EMP_ACCOUNTING | 1 | 41 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMP_RESEARCH | 5 | 195 | 2 (0)| 00:00:01 |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS FULL| EMP_SALES | 1 | 37 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NULL IS NOT NULL)
4 - filter("DNAME"='RESEARCH')
5 - filter("DNAME"='RESEARCH')
6 - filter(NULL IS NOT NULL)
7 - filter("DNAME"='RESEARCH')
</code></pre>
<br />
As you can see in above example, <b>Oracle Database Optimizer simply added filter "NULL IS NOT NULL" for EMP_ACCOUNTING and EMP_SALES</b>, which will simply bypass these tables. <b><span style="color: red;">The CONSTRAINT on DNAME columns of each table allowed Oracle Database to rewrite the SQL, and change the access path to generate the better execution plan</span></b>.<br />
<br />
I hope you have enjoyed reading this post.<br />
<b><br /></b>
<b>Following are the other posts on Optimization of SQL statement.</b><br />
- <a href="https://nimishgarg.blogspot.com/2012/10/query-optimization-tips-for-oracle.html">Query optimization tips for Oracle</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/01/why-prefer-coalesce-over-nvl.html">Why prefer COALESCE over NVL</a><br />
- <a href="http://nimishgarg.blogspot.com/2019/10/datatype-is-important-for-good.html">Datatype is Important for Good Execution Plan and SQL Performance</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/06/why-primary-key-foreign-key.html">Why Primary Key Foreign Key Relationship and Join Elimination</a><br />
- <a href="http://nimishgarg.blogspot.com/2016/05/why-my-distinct-query-is-not-using-index.html">Why my distinct query is not using index?</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/03/avoiding-unnecessary-function-calls-to.html">Avoiding unnecessary function calls to optimize SQL statements</a><br />
- <a href="http://nimishgarg.blogspot.com/2016/06/view-pushed-predicate-powerful.html">VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/01/sql-tuning-partition-pruning-and-hash.html">SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/08/foreign-key-in-oracle-data-warehouse.html">Foreign Key in Oracle Data Warehouse - Best Practice</a><br />
- <a href="http://nimishgarg.blogspot.com/2016/11/tune-complete-refresh-of-materialized.html">Tune Complete Refresh of Materialized View by atomic_refresh=>false</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/05/oracle-sql-developer-autotrace.html">Oracle SQL Developer - Autotrace - Insufficient Privileges</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com2tag:blogger.com,1999:blog-8873109125023142810.post-66499597408196534452019-10-20T13:27:00.000+05:302019-10-21T20:45:38.204+05:30ORA-01843: not a valid month - NLS_DATE_FORMAT<div dir="ltr" style="text-align: left;" trbidi="on">
<b><span style="color: red;">"ORA-01843: not a valid month"</span></b> is a very common exception which we all might have faced while working with Dates in Oracle Database. <b>ORA-01843 occurs due to implicit date conversion</b> which is <b>Basics yet Important</b> feature of Oracle Database. With this blog I am trying to explain the reason of ORA-01843 and how we can avoid it.<br />
<br />
<b>Following is the EMP table in SCOTT schema.</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7839 KING PRESIDENT 17-NOV-81 5000.15 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850.3 30
7782 CLARK MANAGER 7839 09-JUN-81 2450.45 10
7566 JONES MANAGER 7839 02-APR-81 2975.5 20
7788 SCOTT ANALYST 7566 19-APR-87 3000.55 20
7902 FORD ANALYST 7566 03-DEC-81 3000.6 20
7369 SMITH CLERK 7902 17-DEC-80 800.75 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600.9 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250.05 1400 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500.95 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100.01 20
7900 JAMES CLERK 7698 03-DEC-81 950.99 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
</code></pre>
<br />
In my database, If I try to <b>compare the HIREDATE with a string in either MM/DD/YYYY or DD/MM/YYYY format, it fails with ORA-01843</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select * from emp where hiredate > '31/12/1985';
select * from emp where hiredate > '31/12/1985'
*
ERROR at line 1:
ORA-01843: not a valid month
SQL> select * from emp where hiredate > '12/31/1985';
select * from emp where hiredate > '12/31/1985'
*
ERROR at line 1:
ORA-01843: not a valid month
</code></pre>
<br />
<b>In above examples Oracle Database tried implicit date conversion</b>, which is only successful when String values are in default date format. We can <b><span style="color: red;">find the default date format of our Oracle database using NLS_DATE_FORMAT</span></b> -<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select sys_context ('USERENV', 'NLS_DATE_FORMAT') from dual;
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
---------------------------------------------------
DD-MON-RR
</code></pre>
<br />
Now if we try to <b>compare the HIREDATE with a string in DD-MON-RR</b> (default) format, it should work.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select * from emp where hiredate > '31-Dec-85';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000.55 20
7876 ADAMS CLERK 7788 23-MAY-87 1100.01 20
</code></pre>
<br />
We should remember that <b>Default date format might get changed</b>, so <b><span style="color: red;">we should be never dependent on implicit date conversion</span></b>, and convert very string with date value using <b><span style="color: red;">TO_DATE</span></b> function.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select * from emp where hiredate > to_date('12/31/1985','mm/dd/yyyy');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000.55 20
7876 ADAMS CLERK 7788 23-MAY-87 1100.01 20
</code></pre>
<br />
At last, I wanted to add, we can also <b>change NLS_DATE_FORMAT at session level </b>-<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> alter session set NLS_DATE_FORMAT = 'mm/dd/yyyy';
SQL> select * from emp where hiredate > '12/31/1985';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 04/19/1987 3000.55 20
7876 ADAMS CLERK 7788 05/23/1987 1100.01 20
</code></pre>
<br />
As you might have noticed, by setting NLS_DATE_FORMAT, we can not only control the implicit conversion while running our SQL, but the <b>output of DATE values is also as per NLS_DATE_FORMAT</b>. I use above trick while debugging only, but when I am writing database code or sql, I always use TO_DATE<br />
<br />
I hope you have enjoyed reading this article. Please do post your comments.<br />
<br />
<b>Related Posts -</b><br />
- <a href="https://nimishgarg.blogspot.com/2014/04/ora-01830-date-format-picture-ends.html">ORA-01830 date format picture ends before converting entire input string</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/03/prefer-oracle-native-date-arithmetic.html">Why do I Prefer Oracle Native Date Arithmetic over ANSI INTERVAL</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/03/playing-with-truncate-and-date.html">Playing With Truncate and Date in Oracle Database</a><br />
- <a href="https://nimishgarg.blogspot.com/2010/11/oracle-some-important-date-queries.html">Oracle: Some Important Date Queries</a><br />
- <a href="https://nimishgarg.blogspot.com/2010/08/oracle-sql-date-difference-in-days.html">Oracle SQL: Date Difference in Days, Months and Year</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com3tag:blogger.com,1999:blog-8873109125023142810.post-85008023849590323782019-10-06T11:10:00.000+05:302019-10-06T12:49:18.643+05:30Datatype is Important for Good Execution Plan and SQL Performance<div dir="ltr" style="text-align: left;" trbidi="on">
In application programming, choosing the correct data-type is very important for the performance. It is also true with Oracle Database, a <b>correct datatype for column in Oracle Database Table is very important for Good Execution Plan and SQL Performance</b>. With this blog, I am trying to explain why it is important to store data in correct data type column on an Oracle Database Table. <br />
<br />
<b><span style="color: red;">Step 1: Creating a table, to store date values in VARCHAR2, NUMBER and DATE datatype.</span></b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> CREATE TABLE MYTABLE
2 (
3 MYSTR VARCHAR2(100),
4 MYNUM NUMBER,
5 MYDT DATE
6 );
Table created.
SQL> INSERT INTO MYTABLE
2 SELECT TO_CHAR(DT + (DBMS_RANDOM.value*3650),'YYYYMMDD') MYSTR,
3 TO_NUMBER(TO_CHAR(DT+(DBMS_RANDOM.value*3650),'YYYYMMDD')) MYNUM,
4 TRUNC(DT+(DBMS_RANDOM.value*3650)) MYDT
5 FROM (SELECT TO_DATE('01-JAN-2010','DD-MM-YYYY') DT FROM DUAL) D,
6 (SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= 999) A,
7 (SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= 999) B
8 ORDER BY DBMS_RANDOM.RANDOM;
998001 rows created.
SQL> commit;
Commit complete.
</code></pre>
<br />
<br />
As you can see I have inserted 998,001 rows into MYTABLE. In MYSTR and MYNUM has date values in YYYYMMDD format.<br />
<br />
<b><span style="color: red;">Step 2: Create B-Tree indexes on all 3 columns and gather the stats of the table.</span></b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> create index MYTABLE_MYSTR_idx on MYTABLE(MYSTR);
Index created.
SQL> create index MYTABLE_MYNUM_idx on MYTABLE(MYNUM);
Index created.
SQL> create index MYTABLE_MYDT_idx on MYTABLE(MYDT);
Index created.
SQL> EXEC dbms_stats.gather_table_stats( NULL, 'MYTABLE');
PL/SQL procedure successfully completed.
</code></pre>
<br />
<b><span style="color: red;">Step 3: Now let's try to check the performance on each datatype</span></b> for DATE type values by executing our SQL, which is only going to select 2 days of data.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> SELECT * FROM MYTABLE
2 WHERE MYSTR BETWEEN '20111231' AND '20120101';
520 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1015944200
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97688 | 2194K| 1064 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYTABLE | 97688 | 2194K| 1064 (2)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> SELECT * FROM MYTABLE
2 WHERE MYNUM BETWEEN 20111231 AND 20120101;
556 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1015944200
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 97688 | 2194K| 1066 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| MYTABLE | 97688 | 2194K| 1066 (2)| 00:00:01 |
-----------------------------------------------------------------------------
SQL> SELECT * FROM MYTABLE
2 WHERE MYDT BETWEEN TO_DATE('20111231','YYYYMMDD')
3 AND TO_DATE('20120101','YYYYMMDD');
569 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 684440235
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 820 | 18860 | 798 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| MYTABLE | 820 | 18860 | 798 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MYTABLE_MYDT_IDX | 820 | | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
</code></pre>
<br />
As you can see, Oracle Database did <b>Full Table Scan for NUMBER and VARCHAR2 columns</b>, but <b>for DATE datatype Oracle Database performed INDEX RANGE SCAN</b>. <b><span style="color: red;">For Date datatype, Oracle Database is aware that in range of 20111231 and 20120101 dates actually includes only 2 dates, so it was able to perform INDEX RANGE SCAN</span></b>. In cases of other datatype, for example numbers, Oracle estimated near 9000 values between 20111231 and 20120101, so it went for FULL TABLE SCAN.<br />
<br />
I hope you have enjoyed reading this article. You might like to look into following SQL Optimization Articles - <br />
- <a href="https://nimishgarg.blogspot.com/2019/09/efficient-way-to-update-bulk-of-records.html">Efficient way to UPDATE bulk of records in Oracle Database</a><br />
- <a href="https://nimishgarg.blogspot.com/2018/11/steps-to-debug-sql-performance-issue-in.html">Steps to debug SQL Performance issue in Oracle Database</a><br />
- <a href="https://nimishgarg.blogspot.com/2017/08/foreign-key-in-oracle-data-warehouse.html">Foreign Key in Oracle Data Warehouse - Best Practice</a><br />
- <a href="https://nimishgarg.blogspot.com/2017/06/why-primary-key-foreign-key.html">Why Primary Key Foreign Key Relationship and Join Elimination</a><br />
- <a href="https://nimishgarg.blogspot.com/2017/01/sql-tuning-partition-pruning-and-hash.html">SQL Tuning: Partition Pruning and HASH JOIN - Real Life Example</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/06/view-pushed-predicate-powerful.html">VIEW PUSHED PREDICATE - A Powerful Optimizer Transformation feature</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/05/why-my-distinct-query-is-not-using-index.html">Why my distinct query is not using index?</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/04/reduce-database-calls-by-posting.html">Reduce database calls by posting Multiple Records from Application to Database</a> <br />
- <a href="https://nimishgarg.blogspot.com/2016/03/avoiding-unnecessary-function-calls-to.html">Avoiding unnecessary function calls to optimize SQL statements</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/09/how-to-get-execution-plan-and.html">How to Get Execution Plan and Statistics of SQL Query</a><br />
- <a href="https://nimishgarg.blogspot.com/2012/10/query-optimization-tips-for-oracle.html">Basic Query optimization tips for Oracle</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com1tag:blogger.com,1999:blog-8873109125023142810.post-81022155659817258432019-09-17T14:09:00.000+05:302019-09-17T14:09:39.959+05:30Oracle Cloud - Introducing New Always Free Services<div dir="ltr" style="text-align: left;" trbidi="on"><br />
In <b><span style="color: red;">Oracle Open World 2019</span></b>, <b>Larry Ellison</b>, executive chairman and chief technology officer of <b>Oracle Corporation</b>, has made a major announcement and introduced, <b><span style="color: red;">Oracle Cloud Free Tier</span></b>, which is <b><span style="color: red;">New Always Free Services</span></b>. Oracle Cloud Free Tier allows you to sign up for an <b><span style="color: red;">Oracle Cloud</span></b> account which provides a number of <b><span style="color: red;">Always Free services and a Free Trial with US$300 of free credit</span></b> to use on all eligible Oracle Cloud Infrastructure services for up to 30 days. The Always Free services are available for an unlimited period of time. The Free Trial services may be used until your US$300 of free credits are consumed or the 30 days has expired, whichever comes first.<br />
<br />
<br />
<b><span style="color: red;">Oracle Cloud Free Tier includes -</span></b><br />
<ol style="text-align: left;"><li><b>New Always Free</b></li>
<ol><li><b>Databases </b>- Your choice of Autonomous Transaction Processing or Autonomous Data Warehouse. 2 databases total, each with 1 OCPU and 20 GB storage.</li>
<li><b>Compute </b>- Two virtual machines with 1/8 OPCU and 1 GB memory each.</li>
<li><b>Storage </b>- Two Block Volumes, 100 GB total. 10 GB Object Storage. 10 GB Archive Storage.</li>
<li><b>Integrated Developer Platform </b>- SQL Developer, APEX, Machine Learning, Auto REST API generation.</li>
<li><b>Additional Services </b>- Load Balancer, 1 instance, 10 Mbps bandwidth. Monitoring, 500 million ingestion datapoints, 1 billion retrieval datapoints. Notifications, 1 million delivery options per month, 1,000 emails sent per month. Outbound Data Transfer, 10 TB per month.</li>
</ol></ol><ol style="text-align: left;">2. <b>30-day Free Trial (300$ Free Credit)</b> <ol><li>Access to a wide range of Oracle Cloud services for 30 days, including Databases, Analytics, Compute, and Container Engine for Kubernetes</li>
<li>Up to eight instances across all available services</li>
<li>Up to 5 TB of storage</li>
</ol></ol>New Always Free is for everyone including Students, Developers and even for Enterprises (which they can later upgrade to Paid version).<br />
<br />
Here is the link for complete details - <a href="https://www.oracle.com/cloud/free/">https://www.oracle.com/cloud/free/</a><br />
<br />
<br />
<b>Related Links -</b><br />
- <a href="http://nimishgarg.blogspot.com/2017/10/oracle-18c-database-autonomous-database.html">Oracle 18c Database - Autonomous Database Cloud</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/10/oracle-database-18c-xe-express-edition.html">Oracle Database 18c XE (Express Edition) is available for download - Top Features</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/07/oracle-database-18c-on-premises-is.html">Oracle Database 18c (on-premises) is available for download</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/03/top-18-features-of-oracle-18c.html">Top 18 features of Oracle 18c</a><br />
- <a href="http://nimishgarg.blogspot.com/2015/09/json-in-oracle-database-with-examples.html">JSON in Oracle Database with Examples</a><br />
- <br />
<b><br />
</b></div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com1tag:blogger.com,1999:blog-8873109125023142810.post-22623929783011871082019-09-14T20:31:00.000+05:302019-09-14T20:31:13.326+05:30SQL*Loader - Load Excel/CSV file into Oracle Database Table<div dir="ltr" style="text-align: left;" trbidi="on">In the age of various GUI tool available to load an Excel file into Oracle Database, it is always fun to use <b>SQL*Loader</b> command line tool. With this blog I am trying to show how I loaded an Excel file into Oracle Database. <br />
<br />
My <b>Current Working Directory is "C:\Users\ngarg\Desktop\load"</b>, all files are located in "C:\Users\ngarg\Desktop\load" and also all commands will be executed from "C:\Users\ngarg\Desktop\load".<br />
<br />
I had a file <b>AUG.xlsx</b>, which had <b>14816 rows + 1 header</b> row as following Image -<br />
<br />
<b><span style="color: red;">STEP1: I saved this file as CSV using Excel - Save As - "CSV UTF-8 (Comma Delimited)(*.csv)"</span></b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>EmployeeID,Worker,ELCode,ReportEffectiveDate
37069,Nimish Garg (37069),USRMTMO,8/31/2018 0:00
4013,Anuj Sharma (4013),ITMILAN,8/31/2018 0:00
4041,Mamta Sharma (4041),USSTM,8/31/2018 0:00
6931,Krishan Tripathi (6931),GBRMT,8/31/2018 0:00
..
..
..
</code></pre><br />
<b><span style="color: red;">STEP2: Created a new table in my database</span></b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> create table aug_csv
2 (
3 EmployeeID number,
4 Worker varchar2(4000),
5 ELCode varchar2(4000),
6 ReportEffectiveDate date
7 );
Table created.
</code></pre><br />
<b><span style="color: red;">STEP3: Created following control file with name "load.ctl"</span></b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>options (
skip=1,
PARALLEL=true,
DIRECT=true
)
load data
infile 'aug.csv'
append into table aug_csv
fields terminated by ","
optionally enclosed by '"'
(
EmployeeID,
Worker,
ELCode,
ReportEffectiveDate DATE "mm/dd/YYYY HH24:MI"
)
</code></pre><br />
<b><span style="color: red;">STEP4: Load started with SQL*Loader</span></b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>C:\Users\ngarg\Desktop\load> sqlldr ngarg@meshdb control='load.ctl' log='Results.log'
Password:
SQL*Loader: Release 18.0.0.0.0 - Production on Thu Sep 12 08:56:06 2019
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
Load completed - logical record count 14816.
Table AUG_CSV:
14816 Rows successfully loaded.
Check the log file:
Results.log
for more information about the load.
</code></pre><br />
It is as simple as this, we are done :)<br />
<br />
As we can see here all the <b>14816 Rows successfully loaded into the table</b>. We can validate it as<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> select count(*) from aug_csv;
COUNT(*)
----------
14816
</code></pre><br />
<b><span style="color: red;">Here is the explanation of keywords used in Control file of SQL*Loader</span></b><br />
<b>1. SKIP=1</b> - Skip the first line of CSV, as it contains header<br />
<b>2. PARALLEL=true</b> - specifies that loads can operate in multiple parallel to load data<br />
<b>3. DIRECT=true</b> - specifies the load with use data path. A direct path load uses multiblock asynchronous I/O to writing the data blocks directly to the database files, and can usually load data at near disk speed.<br />
<b>4. DATE "mm/dd/YYYY HH24:MI"</b> - need to provide format of dates of the date column of CSV. <br />
<br />
<b><span style="color: red;">SQL*Loader has also created a file "Results.log"</span></b> in "C:\Users\ngarg\Desktop\load" directory during execution.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL*Loader: Release 18.0.0.0.0 - Production on Thu Sep 12 08:56:06 2019
Version 18.4.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Control File: load.ctl
Data File: aug.csv
Bad File: aug.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 1
Errors allowed: 50
Continuation: none specified
Path used: Direct - with parallel option.
Table AUG_CSV, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
EMPLOYEEID FIRST * , O(") CHARACTER
WORKER NEXT * , O(") CHARACTER
ELCODE NEXT * , O(") CHARACTER
REPORTEFFECTIVEDATE NEXT * , O(") DATE mm/dd/YYYY HH24:MI
Table AUG_CSV:
14816 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Date cache:
Max Size: 1000
Entries : 1
Hits : 14815
Misses : 0
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 1
Total logical records read: 14816
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 3
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Thu Sep 12 08:56:06 2019
Run ended on Thu Sep 12 08:56:52 2019
Elapsed time was: 00:00:46.22
CPU time was: 00:00:01.58
</code></pre><br />
<br />
<b>In case of failure at some records, it might create another file with ".bad" extension</b>, listing out the records which got failed.<br />
<br />
<b>Related Posts:</b><br />
- <a href="https://nimishgarg.blogspot.com/2018/07/sql-loader-express-mode-loading-data-in.html">SQL Loader Express Mode - Loading data in Oracle database can't be more easy</a><br />
- <a href="https://nimishgarg.blogspot.com/2013/04/load-csv-file-in-oracle-using-plsql.html">Load CSV file in Oracle using PL/SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2012/06/create-external-table-from-csv-file.html">Create External Table from CSV File</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/09/create-csv-file-using-plsql.html">Create CSV file using PL/SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2018/10/sqlformat-csv-json-xml-format-output-of.html">SQLFormat - CSV, JSON, XML - Format output of SQL in SQLDeveloper</a><br />
<br />
</div>Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com4tag:blogger.com,1999:blog-8873109125023142810.post-81184610413539955462019-09-01T12:38:00.000+05:302019-09-01T12:38:25.749+05:30Efficient way to UPDATE bulk of records in Oracle Database<div dir="ltr" style="text-align: left;" trbidi="on">
I have received many requests to write on what is the <b>efficient way to UPDATE bulk of records</b> in Oracle Database. With is blog I am trying to compare various ways to UPDATE one table (ROSTER) from another table (EMPLOYEE). Both tables has 999999 records and there is a PRIMARY KEY on EMPLOYEE_CODE in both tables. <br />
<br />
<b><span style="color: red;">With this blog I am covering following methods to UPDATE data-</span></b><br />
1. Update each record individually and COMMIT in FOR LOOP<br />
2. Update each record individually in FOR LOOP but COMMIT after the loop<br />
3. BULK UPDATE using BULK COLLECT and FOR ALL<br />
4. DIRECT UPDATE SQL<br />
5. MERGE STATEMENT<br />
6. UPDATE using INLINE View Method<br />
<br />
Lets execute all and check the performance <br />
<br />
<b>1. Update each record individually and COMMIT in FOR LOOP</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>nimish@garg> begin
2 for c in (select * from employee)
3 loop
4 update roster
5 set
6 job = c.job,
7 position = c.position,
8 organisation = c.organisation
9 where
10 employee_code = c.employee_code;
11 commit;
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:03:52.36
</code></pre>
<br />
<b>2. Update each record individually in FOR LOOP but COMMIT after the loop</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>nimish@garg> begin
2 for c in (select * from employee)
3 loop
4 update roster
5 set
6 job = c.job,
7 position = c.position,
8 organisation = c.organisation
9 where
10 employee_code = c.employee_code;
11 end loop;
12 commit;
13 end;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:39.53
</code></pre>
<br />
<b>3. BULK UPDATE using BULK COLLECT and FOR ALL</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>nimish@garg> declare
2 type employee_t is table of employee%rowtype index by pls_integer;
3 l_employee_data employee_t;
4 begin
5 select *
6 bulk collect into l_employee_data
7 from employee;
8
9 forall indx in 1 .. l_employee_data.count
10 update roster r
11 set r.job = l_employee_data(indx).job,
12 r.position = l_employee_data(indx).position,
13 r.organisation = l_employee_data(indx).organisation
14 where r.employee_code = l_employee_data(indx).employee_code;
15
16 commit;
17 end;
18 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:43.53
</code></pre>
<br />
<b>4. DIRECT UPDATE SQL</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>nimish@garg> begin
2 update roster r
3 set (job, position, organisation) = (
4 select job, position, organisation from employee e
5 where e.employee_code = r.employee_code
6 )
7 where r.employee_code in (select employee_code from employee);
8 commit;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:38.22
</code></pre>
<br />
<b>5. MERGE STATEMENT</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>nimish@garg> begin
2 merge into roster r
3 using employee e
4 on (r.employee_code = e.employee_code)
5 when matched then
6 update set
7 r.job = e.job,
8 r.position = e.position,
9 r.organisation = e.organisation;
10
11 commit;
12 end;
13 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.23
</code></pre>
<br />
<b>6. UPDATE using INLINE View Method</b><br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>nimish@garg> begin
2 update
3 (
4 select r.employee_code, r.job, r.position, r.organisation,
5 e.job jval, e.position pval, e.organisation oval
6 from roster r, employee e
7 where r.employee_code = e.employee_code
8 )
9 set job = jval,
10 position = pval,
11 organisation = oval;
12
13 commit;
14 end;
15 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:26.97
</code></pre>
<br />
<b><span style="color: red;">Key Take Away :- </span></b><br />
A common misconception of many PL/SQL developer is that BULK COLLECT + FOR ALL is always better when we are doing a bulk operation. I always say <b>BULK COLLECT + FOR ALL is better than the operation in loop</b>, but if we can write the logic in a flat UPDATE SQL, a direct <b>UPDATE SQL would be almost always better than the BULK COLLECT + FOR ALL</b>. I also suggest to wrap the SQL code in a PL/SQL API. <br />
<br />
I have not covered BULK COLLECT + FOR ALL with LIMIT clause here, as there would be some context switches and it would be little slower (very marginal depending on LIMIT Size) than BULK COLLECT + FOR ALL.<br />
<br />
As we can see in above examples, <b>"MERGE STATEMENT" or "UPDATE using INLINE View Method" are usually the fastest way</b> to bulk update data, followed by a direct UPDATE STATEMENT. I usually prefer "MERGE STATEMENT" over other approaches.<br />
<br />
<b><span style="color: red;">Related Posts -</span></b><br />
- <a href="https://nimishgarg.blogspot.com/2016/03/avoiding-unnecessary-function-calls-to.html">Avoiding unnecessary function calls to optimize SQL statements</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/12/bulk-collect-with-limit-clause-and.html">Bulk Collect with Limit Clause and %NOTFOUND</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/11/plsql-tuning-bulk-collect-with-dynamic.html">PLSQL Tuning: Bulk Collect with Dynamic SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2019/08/execute-immediate-returning-bulk.html">Execute Immediate Returning Bulk Collect - Dynamic DML Collection</a><br />
- <a href="https://nimishgarg.blogspot.com/2012/03/ora-01555-snapshot-too-old.html">ORA-01555: snapshot too old</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/08/ora-01779-cannot-modify-column-which.html">ORA-01779: cannot modify a column which maps to a non key-preserved table</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com32tag:blogger.com,1999:blog-8873109125023142810.post-14440346781707836482019-08-18T18:11:00.000+05:302019-08-18T18:11:12.761+05:30Execute Immediate Returning Bulk Collect - Dynamic DML Collection <div dir="ltr" style="text-align: left;" trbidi="on">
One of our team member wanted to execute a <b><span style="color: red;">dynamic DML using EXECUTE IMMEDIATE and return updated rows into a PL/SQL Collection</span></b>. For the purpose of demonstration and ease I have rewritten the examples on EMP table. The code written by the team member was as follows -<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> DECLARE
2 TYPE emp_record IS RECORD
3 (
4 EMPNO NUMBER(4),
5 ENAME VARCHAR2(10),
6 HIREDATE DATE,
7 SAL NUMBER(7,2),
8 DEPTNO NUMBER(2)
9 );
10 TYPE l_emp IS TABLE OF emp_record;
11 bonus_amt number := 100;
12 sql_stmt VARCHAR(200);
13 BEGIN
14 sql_stmt := 'UPDATE emp SET sal = sal + :1
15 RETURNING EMPNO, ENAME, HIREDATE, SAL, DEPTNO
16 INTO :2, :3, :4, :5, :6';
17 EXECUTE IMMEDIATE sql_stmt
18 USING bonus_amt RETURNING BULK COLLECT INTO l_emp;
19 END;
20 /
USING bonus_amt RETURNING BULK COLLECT INTO l_emp;
*
ERROR at line 18:
ORA-06550: line 18, column 51:
PLS-00321: expression 'L_EMP' is inappropriate as the left hand side of an assignment statement
ORA-06550: line 17, column 4:
PL/SQL: Statement ignored
</code></pre>
<br />
<br />
<b><span style="color: red;">BULK COLLECT and RETURNING clause with EXECUTE IMMEDIATE</span></b> could be little tricky to handle and if not done correctly. In my experience I have seen people doing some <b>common mistakes</b> which may lead to <b>following ORA EXCEPTION and PLS Errors</b>. <br />
<b>1. PLS-00321</b>: expression 'L_EMP' is inappropriate as the left hand side of an assignment statement<br />
<b>2. ORA-01008</b>: not all variables bound<br />
<b>3. PLS-00429</b>: unsupported feature with RETURNING clause<br />
<br />
<b>PLS-00321</b> signifies that the variable or collection in which we are returning the values is not actually a variable or collection but the TYPE itself.<br />
<b>ORA-01008</b> might occur when we do not have appropriate number of bind variables in INTO or USING clause.<br />
<b>PLS-00429</b> with returning clause with bulk collect usually occurs when the RECORD type is not defined at SQL Engine level but at PL/SQL level itself<br />
<br />
<b><span style="color: red;">The solution to the above code is as following</span></b>, In which I have done 3 changes<br />
- Created TYPE emp_t AS OBJECT<br />
- Collection type is created in PL/SQL and variable is defined for collection type<br />
- Type Cast the multiple values into Object type in returning clause<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>SQL> create type emp_t as object
2 (
3 EMPNO NUMBER(4),
4 ENAME VARCHAR2(10),
5 HIREDATE DATE,
6 SAL NUMBER(7,2),
7 DEPTNO NUMBER(2)
8 );
9 /
Type created.
SQL> DECLARE
2 type emp_tt is table of emp_t index by binary_integer;
3 l_emp emp_tt;
4 bonus_amt number := 100;
5 sql_stmt VARCHAR(200);
6 BEGIN
7 sql_stmt := 'UPDATE emp SET sal = sal + :1
8 RETURNING emp_t(EMPNO, ENAME, HIREDATE, SAL, DEPTNO)
9 INTO :2';
10 EXECUTE IMMEDIATE sql_stmt
11 USING bonus_amt RETURNING BULK COLLECT INTO l_emp;
12 END;
13 /
PL/SQL procedure successfully completed.
</code></pre>
<br />
<br />
I hope I was able to explain the key points of <b>Dynamic DML returning collection using BULK COLLECT and EXECUTE IMMEDIATE</b>. I also hope that you have enjoyed reading this blog post. Please check out following related post too<br />
<br />
<b>Related Posts:</b><br />
- <a href="https://nimishgarg.blogspot.com/2015/11/plsql-tuning-bulk-collect-with-dynamic.html">PLSQL Tuning: Bulk Collect with Dynamic SQL</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/11/plsql-tuning-bind-variables-and-execute.html">PLSQL Tuning: Bind Variables and execute immediate</a><br />
- <a href="https://nimishgarg.blogspot.com/2015/12/bulk-collect-with-limit-clause-and.html">Bulk Collect with Limit Clause and %NOTFOUND</a><br />
- <a href="https://nimishgarg.blogspot.com/2013/03/dbmsprofiler-how-to-analyze-plsql.html">DBMS_PROFILER: How to analyze pl/sql performance</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/04/reduce-database-calls-by-posting.html">Reduce database calls by posting Multiple Records from Application to Database</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/03/avoiding-unnecessary-function-calls-to.html">Avoiding unnecessary function calls to optimize SQL statements</a><br />
- <a href="https://nimishgarg.blogspot.com/2016/01/cursor-with-parameters-in-oracle.html">Passing Parameter to CURSOR in Oracle</a><br />
- <a href="https://nimishgarg.blogspot.com/2011/11/two-dimension-array-in-plsql.html">Two Dimension Array in PL/SQL</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com5tag:blogger.com,1999:blog-8873109125023142810.post-68019430990392855942019-08-04T11:21:00.000+05:302019-08-04T11:21:32.519+05:30ORA-02030: can only select from fixed tables/views<div dir="ltr" style="text-align: left;" trbidi="on">
<b>ORA-02030</b>: can only select from fixed tables/views<br />
<b>Cause</b>: An attempt is being made to perform an operation other than a retrieval from a fixed table/view. <br />
<b>Action</b>: You may only select rows from fixed tables/views.<br />
<br />
The request came from a team member that he wanted execute following SQL on V$SQLTEXT, V$SESSION AND V$PROCESS, from a "READONLYUSER" user, to look at the currently Active SQLs on the database.<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>readonlyuser@xe> select
2 s.username,
3 s.machine,
4 s.module,
5 spid,
6 s.LAST_CALL_ET Duration,
7 substr(s.sid || ',' || s.serial#,0,15) sid,
8 sql_text
9 from
10 v$sqltext,
11 v$session s,
12 v$process p
13 where
14 address=sql_address
15 and hash_value=sql_hash_value
16 and paddr=addr
17 and status='ACTIVE'
18 order by s.sid,piece;
v$process p
*
ERROR at line 12:
ORA-00942: table or view does not exist
</code></pre>
<br />
This seems to be an access issue, so team <b>tried to grant SELECT on V$SQLTEXT, V$SESSION AND V$PROCESS to his user from SYS, but it got failed with ORA-02030 exception</b>. <br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>sys@ngarg> grant select on V$SESSION to READONLYUSER;
grant select on V$SESSION to READONLYUSER
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
</code></pre>
<br />
As <b>ORA-02030 says, that we can only select from fixed tables/views</b>, which means V$SESSION is not a table or view but is of some other object type? <br />
<br />
We first checked the <b>object type of V$SQLTEXT, V$SESSION AND V$PROCESS</b>.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>sys@ngarg> select object_name, object_type from dba_objects
2 where object_name in ('V$SQLTEXT', 'V$SESSION', 'V$PROCESS');
OBJECT_NAME OBJECT_TYPE
------------------------ ------------
V$PROCESS SYNONYM
V$SESSION SYNONYM
V$SQLTEXT SYNONYM
</code></pre>
<br />
As we can see here that <b>V$SQLTEXT, V$SESSION AND V$PROCESS are actually synonyms</b>, We need to find out to which tables or views they are pointing to -<br />
<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>sys@ngarg> select table_owner, table_name from dba_synonyms
2 where synonym_name in ('V$SQLTEXT', 'V$SESSION', 'V$PROCESS');
TABLE_OWNER TABLE_NAME
------------------------------ ------------------------------
SYS V_$PROCESS
SYS V_$SESSION
SYS V_$SQLTEXT
</code></pre>
<br />
Now we have base tables/views names, let us <b>try to grant select on base tables</b> again.<br />
<pre style="background-color: #eeeeee; border: 1px dashed #999999; color: black; font-family: "andale mono" , "lucida console" , "monaco" , "fixed" , monospace; font-size: 12px; line-height: 14px; overflow: auto; padding: 5px; width: 100%;"><code>sys@ngarg> grant select on V_$PROCESS to READONLYUSER;
Grant succeeded.
sys@ngarg> grant select on V_$SQLTEXT to READONLYUSER;
Grant succeeded.
sys@ngarg> grant select on V_$SESSION to READONLYUSER;
Grant succeeded.
</code></pre>
<br />
Great! The grant was given to READONLYUSER, and the person was able to execute the SQL from READONLYUSER.<br />
<br />
Before ending the post, I wanted to add - Oracle Database maintains a set of <b>virtual tables that record current database activity</b>. These views are dynamic because they are <b>continuously updated while a database is open and in use</b>. The views are sometimes called <b>V$ views</b> because their names begin with V$.<br />
<br />
<br />
<b>Related Posts:</b><br />
- <a href="https://nimishgarg.blogspot.com/2012/10/query-optimization-tips-for-oracle.html">Query optimization tips for Oracle</a><br />
- <a href="https://nimishgarg.blogspot.com/2013/03/dbmsprofiler-overview-and-how-to-install.html">DBMS_PROFILER: Overview and How to Install</a><br />
- <a href="https://nimishgarg.blogspot.com/2010/10/oracle-tkprof-simple-steps.html">Oracle : TKPROF simple steps</a><br />
- <a href="http://nimishgarg.blogspot.com/2017/05/oracle-sql-developer-autotrace.html">Oracle SQL Developer - Autotrace - Insufficient Privileges</a><br />
- <a href="http://nimishgarg.blogspot.com/2018/11/steps-to-debug-sql-performance-issue-in.html">Steps to debug SQL Performance issue in Oracle Database</a></div>
Nimish Garghttp://www.blogger.com/profile/15795821721808548808noreply@blogger.com0