Order of execution plan

Below reference clearly explains the order of execution plan.

http://www.dba-oracle.com/t_order_sequence_sql_execution_explain_plans_steps.htm


Cross verify results with below examples:

Example:
Query:
select /*+ gather_plan_statistics */
e.first_name,d.department_name,j.job_title,j.job_id
from
hr.employees e,
hr.departments d,
hr.jobs j
where
e.job_id=j.job_id
and e.department_id=d.department_id
and j.job_title in ('Purchasing Clerk','Marketing Manager','Administration Assistant')
--and e.first_name in ('Sigal','Alexander','Jennifer','Susan')
;
SELECT *  FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;

Plan:

Id	Operation	Name	Starts	E-Rows	A-Rows	A-Time	Buffers	OMem	1Mem	Used-Mem
0	SELECT STATEMENT		1		7	00:00.0	19
*  1	HASH JOIN		1	17	7	00:00.0	19	880K	880K	629K (0)
2	NESTED LOOPS		1		7	00:00.0	12
3	NESTED LOOPS		1	17	7	00:00.0	9
*  4	TABLE ACCESS FULL	JOBS	1	3	3	00:00.0	7
*  5	INDEX RANGE SCAN	EMP_JOB_IX	3	6	7	00:00.0	2
6	TABLE ACCESS BY INDEX ROWID	EMPLOYEES	7	6	7	00:00.0	3
7	TABLE ACCESS FULL	DEPARTMENTS	1	27	27	00:00.0	7

order of id execution: 4,5,3,6,2,7,1,0

It follows "Left-Right-Center" at every node from leaf to top in tree.

It follows post order traversal. Refer this.
https://www.tutorialspoint.com/python_data_structure/python_tree_traversal_algorithms.htm

Viewing the actual explain plan of Query

step1: add /*+ gather_plan_statistics */ hint in select statement as shown below.

select /*+ gather_plan_statistics */
e.first_name,d.department_name,j.job_title,j.job_id
from
hr.employees e,
hr.departments d,
hr.jobs j
where
e.job_id=j.job_id
and e.department_id=d.department_id
and j.job_title in ('Purchasing Clerk','Marketing Manager','Administration Assistant')
--and e.first_name in ('Sigal','Alexander','Jennifer','Susan')
;

step2: execute
 SELECT *  FROM  TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;
A-rows shows actually resulted query where as E-rows shows estimated row count.

Pivot, Unpivot in Oracle SQL

Below is a sample table.

select * from olympic_medal_winners;

Pivot: Convert rows to columns.

select * from (
select noc,medal from olympic_medal_winners)
pivot (
  count(*) for medal
  in ( 'Gold' Gold, 'Silver' Silver , 'Bronze'   Bronze)

)
order by 1 desc
fetch first 6 rows only;

Note: Always maintain pivot in outer most subquery with "select * from" clause.

Below is a query which results total events,sports, gender where gold , silver, bronze is won.

select * from (
 select noc, medal, sport, event, gender
 from   olympic_medal_winners
)
pivot (
 count(distinct sport ||'#'|| event ||'#'||gender )
 for medal in ( 'Gold' gold, 'Silver' silver, 'Bronze' bronze )
)
order  by 2 desc, 3 desc, 4 desc
fetch first 5 rows only;

Note: The outer grouping will occur only if that column is not used in Pivot braces. In above case sport,gender, event columns are used in Pivot braces() so, that wouldn't be grouped in result. Only NOC column is used to group.

Using multiple measure(count, sum) and filter pivoted data.

select * from (
  select noc, medal, sport, event, gender, athlete
  from   olympic_medal_winners
)
pivot  (
  count( distinct sport ||'#'|| event ||'#'|| gender ) medals,
  count( distinct sport ) sports,
  listagg( athlete, ',') within group (order by athlete) athletes
  for medal in ( 'Gold' gold )
)

where  noc like 'D%'

Unpivot: Convert Columns to rows.

Below is an another data set representing medals count.


select * from olympic_medal_tables

Below is an example of unpivoting the data.
select * from olympic_medal_tables
unpivot (medal_count for medal_colour in (
  gold_medals as 'GOLD',
  silver_medals as 'SILVER',
  bronze_medals as 'BRONZE'
))
order  by noc
fetch  first 6 rows only;

NOC  MEDAL_COLOUR  MEDAL_COUNT  
ALG  GOLD                                  0            
ALG  BRONZE                             0            
ALG  SILVER                                2            
ARG  GOLD                                  3            
ARG  BRONZE                             0            
ARG  SILVER                                1

Note: Observe that each column is denoted with a data set name GOLD, SILVER, BROZE etc to consider each of them in one column.

Generating Calendar using Connect Prior by

Note: Replace $Start_dt, $End_dt with the actual start,end dates

SELECT

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'Day') DAY_NAME,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM) DAY_DT,

            TO_NUMBER(TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') ) CAL_WEEK_NUM,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') CAL_WEEK_START_DT,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'WW') + 6 CAL_WEEK_END_DT,

            TO_NUMBER(TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'DDD') ) CAL_DAY_OF_YEAR,

            TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM) - TRUNC(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'Q'

) CAL_DAY_OF_QTR,

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'DD') CAL_DAY_OF_MONTH,

            TO_CHAR(TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - ROWNUM,'D') CAL_DAY_OF_WEEK

        FROM

            DUAL

        CONNECT BY

            ROWNUM <= TO_DATE('$End_dt','MM/DD/YYYY HH24:MI:SS') - TO_DATE('$Start_Dt','MM/DD/YYYY HH24:MI:SS')

        ORDER BY

            DAY_DT;

Gather stats of a table sytax

begin

DBMS_STATS.GATHER_TABLE_STATS('owner', 'table_name',
                                       estimate_percent=>50,
                                       block_sample=>TRUE,
                                       degree=>4) ;

end  ;

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 1000 ROWS;

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS 50 PERCENT;

SQL to find Monday and Friday of the week of a given date

select next_day (sysdate-7,'FRIDAY') Last_Friday, next_day (sysdate-7, 'MONDAY') Last_Monday from dual;

Query to delete duplicate records in oracle

delete from
   customer
where rowid in
 (select rowid from
   (select
     rowid,
     row_number()
    over
     (partition by custnbr order by custnbr) dup
    from customer)
  where dup > 1)

How to Import from Excel to Oracle with SQL Developer

Step 0: The Empty Oracle Table and your Excel File

You have an Oracle table and you have one or more Excel files.

Data here but not there!?!

You do know how to view multiple objects at once in SQL Developer, right?

Step 1: Mouse-right click – Import Data

Yes, it's that easy.

Step 2: Select your input (XLS) file

Yes, we also support XLSX, CSV, etc

Step 3: Verify the data being read

Mind the headers!

Does your Excel file have column headers? Do we want to treat those as a row to the table? Probably not. The default options take care of this. You can also choose to preview more than 100 rows.
Here’s what it looks like if you uncheck the ‘Header’ box

Sometimes you may want the column headers as a row in the table?

Sometimes your Excel file has multiple headers, or you may need to only import a certain subset of the spreadsheet. Use the ‘Skip Rows’ option to get the right data.

Step 4: Create a script or import automatically

Script or do it for me?

For this exercise the ‘Insert’ method will be used.

Step 5:

Choose the Excel columns to be imported

You may have an Excel file with 100 columns but your table only has 30. This is where you tell SQL Developer what columns are to be used for the import. You can also change up the column order, which may make the next step a bit easier.

Step 6:

If you’re not paying attention and just letting the wizard guide you home, then now is the time to wake up. There’s a good chance the column order of the Excel file won’t match the definition of your table. This is where you will tell SQL Developer what columns in the spreadsheet match up to what columns in the Oracle table.

Step 7: Verify your settings

Hit the ‘verify’ button. Fix any mistakes.

Ruh roh raggy!

SQL Developer is telling you it doesn’t know how to reconcile the data for this DATE column. We need to know what the DATE FORMAT is.
So we need to go back to the Column definition wizard and inspect the HIRE_DATE column settings.

You need to look at how the dates are stored in the spreadsheet and write them in terms that Oracle can understand. This will be used on the INSERTs via a TO_DATE() function that will turn your Excel string into an actual DATE value.
After correcting this, go back to the Verification screen and see if that fixes the problem.

Step 8:

Everything looks right!

Click on the ‘Finish’ button.

Step 9: Verify the import look at your new table data

The data is there and the dates look right!

Note the ‘Log’ panel. SQL Developer is processing the records in batches of 50. No errors and the data is there!