{"id":1820,"date":"2018-01-17T22:45:03","date_gmt":"2018-01-17T21:45:03","guid":{"rendered":"http:\/\/62.131.51.129\/?p=1820"},"modified":"2018-01-17T22:45:03","modified_gmt":"2018-01-17T21:45:03","slug":"explain-plan-in-oracle","status":"publish","type":"post","link":"http:\/\/archief.van-maanen.com\/?p=1820","title":{"rendered":"Explain plan in Oracle"},"content":{"rendered":"<p>I want to show the usage of an explain plan in Oracle. This will be shown in Oracle 12c. I will create a table and a sql statement. This sql statement will be analysed in a so-called explained plan that shows how Oracle will process the query. To do so, I first created <a href=\"http:\/\/62.131.51.129\/2015\/11\/09\/generate-table-in-oracle\/\"> a big table &#8220;Groot&#8221; in Oracle<\/a>. Upon that I created an index &#8220;GrootIndex&#8221; with create index grootIndex on groot(n).<br \/>The explain plan is created with (select * from C##HR.GROOT where n=600000; being the sql statement) :<\/p>\n<pre>explain plan for\nselect * from C##HR.GROOT\nwhere n=600000;\n<\/pre>\n<p>The results are then shown with:<\/p>\n<pre>select object_name, operation, options, object_type, optimizer, id <br \/>from (<br \/>select   object_name, operation, options, object_type, optimizer, id, dense_RANK() OVER (PARTITION BY OPERATION order by   PLAN_ID DESC) rn<br \/>from plan_table)A <br \/>where rn=1<br \/>order by id;\n<\/pre>\n<p>The results are:<\/p>\n<pre>OBJECT_NAME\tOPERATION\t    OPTIONS\n\t        SELECT STATEMENT\t\nGROOT\t        TABLE ACCESS\t    BY INDEX ROWID BATCHED\nGROOTINDEX\tINDEX\t            RANGE SCAN\n<\/pre>\n<p>One might see that the sql uses the index. Based on the rowid =found herein, the sql accesses table Groot with the rid to retrieve the rows that were requested.<\/p>\n<p>Or &#8211; alternatively, you may use:<\/p>\n<p>select * from table(dbms_Xplan.display());<\/p>\n<p>that provides something like:<\/p>\n<p>\u00a0<\/p>\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2020\/02\/Capture-6-2-1-1.png\" alt=\"\" class=\"wp-image-3292\"\/><\/figure>\n\n\n\n<p class=\"wp-block-paragraph\">A final remark: these results can also be seen in SQL developer. In the tool bar, one may notice small buttons that allow such direct analysis. <\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><img decoding=\"async\" src=\"http:\/\/van-maanen.com\/wp-content\/uploads\/2020\/02\/Capture-6-3-1-1.png\" alt=\"\" class=\"wp-image-3294\"\/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>I want to show the usage of an explain plan in Oracle. This will be shown in Oracle 12c. I will create a table and a sql statement. This sql statement will be analysed in a so-called explained plan that shows how Oracle will process the query. To do so, I first created a big [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1820","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1820","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1820"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/1820\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1820"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1820"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1820"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}