Real-World Performance Training SQL Reference

Real-World Performance Team

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

SQL Sub-query ( correlated ) select empno, ename, sal Sub-query from emp e1 where sal > ( select avg(sal) from emp e2 where e2.deptno = e1.deptno group by deptno )

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

SQL Inline View select empno, ename, sal, avg_sal from emp e join ( select deptno , avg(sal) avg_sal from emp group by deptno ) v on e.deptno = v.deptno

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Inline View

SQL Common Table Expression (CTE) with avg_sal as ( select deptno , avg(sal) avg_sal from emp group by deptno ) select empno, ename, sal, avg_sal from emp e join avg_sal a on e.deptno = a.deptno

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Common Table Expression

SQL Window Function

analytic function select dname, ename, sal , rank() over ( partition by e.deptno order by sal desc ) sal_rank from emp e join dept d on e.deptno = d.deptno order order by dname, sal_rank

partition by clause

by clause for function

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Data Loading Anatomy of an External Table

External Table Definition

create table FAST_LOAD ( Reference the column definition list ... Uncompress the data ) Mount Point organization external using a secure wrapper ( type oracle_loader default directory SPEEDY_FILESYSTEM The Character set must preprocessor exec_file_dir:’zcat.sh’ characterset ‘ZHS16GBK’ match the Character set badfile ERROR_DUMP:’FAST_LOAD.bad’ of the Files logfile ERROR_DUMP:’FAST_LOAD.log’ ( Note Compressed Files file column mapping list ... ) location Parallel should match (file_1.gz, file_2.gz, file_3.gz, file_4.gz ) or be less than the reject limit 1000 parallel 4 number of Files /

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Gathering Statistics Gathering Table Statistics (Default) Begin DBMS_STATS.GATHER_TABLE_STATS(USER,'TABLE_NAME'); End; /

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Gathering Statistics Extended Statistics

• Use the dbms_stats.create_extended_stats function to create extended statistics Select dbms_stats.create_extended_stats(USER,'CARS','(MAKE,MODEL)') from dual; Select dbms_stats.create_extended_stats(USER,'EMP','(UPPER(EMP_LAST_NAME))') from dual;

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Data Processing Techniques Set based processing insert select from where

/*+ append */ into west * emp deptno = 20;

commit;

insert select from where

/*+ append */ into east * emp deptno != 20;

commit; Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Data Processing Techniques Multiple ways to get the same result insert /*+ append */ first when deptno = 20 then into west values … else into east values … select * from emp;

commit;

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Data Validation SQL Duplicate Rows Simply Check the Data

Obtain one of the ROWIDs of duplicates to investigate

Query the rows you wish to keep eliminating duplicates based on the load time

select pk,count(*) from DIRTY_DATA group by pk having count(*)>1;

select pk, count(*), max(rowid) from DIRTY_DATA group by pk having count(*)>1;

select column_list from ( select a.*,row_number() over ( partition by pk order by load_time desc ) rowno from DIRTY_DATA a ) where rowno=1

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Data Validation SQL Orphaned Row Check Look For Orphans

Look for Parents with no Children

select C.rowid from PARENT P right outer join CHILD C on P.pk = C.fk where P.pk is null;

select P.rowid from PARENT P left outer join CHILD C on P.pk = C.fk where C.fk is null;

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Rewriting DML Delete alter session enable parallel dml; delete from tx_log where symbol = ‘JAVA’; commit;

The predicate is the compliment of the DELETE, it selects the rows to keep

alter session enable parallel dml; insert /*+ append */ into tx_log_new select * from tx_log where symbol != ‘JAVA’; alter table tx_log rename to tx_log_old; alter table tx_log_new rename to tx_log; or alter table tx_log exchange partition part_201409 with table tx_log_new; Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Rewriting DML Update alter session enable parallel dml;

alter session enable parallel dml;

update sales_ledger set tax_rate = 9.9 where tax_rate = 9.3 and sales_date > ‘01-Jan-09’;

insert /*+ append */ into tx_log_new select , case sales_date>‘01-Jan-09’ and tax_rate=9.3 then 9.9 else tax_rate end, from sales_ledger;

commit;

The UPDATE predicates are moved to the SELECT list in a CASE statement to transform the rows

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Rules and Framework NOT NULL Constraints • For each row in lineorder, how many rows are returned from customer? • Without constraints, what if lo_custkey is NULL? • Even if lo_custkey is NOT NULL, how many rows will join with customer? 0? 1? More than 1? • NOT NULL constraints are essentially free, no sense not to implement • Several optimizations depend on this information!

FROM JOIN

lineorder customer ON lo_custkey = c_custkey

SQL> desc lineorder Name Null? ----------- ------... LO_CUSTKEY NOT NULL ...

SQL> desc customer Name Null? ----------------C_CUSTKEY NOT NULL ....

Type -------NUMBER

Type -------NUMBER

ALTER TABLE tname MODIFY (cname NOT NULL)

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Rules and Framework Primary Key and Foreign Key Constraints • There must be a primary key on the dimension table • There must be a foreign key on the fact table

• The state of the constraint depends on trust in the ETL process and volume of data • Constraints must be in RELY state • It is not necessary to enforce constraints on the fact table • You need to tell the optimizer you can trust constraints in the RELY state

alter table customer add constraint customer_pk primary key (c_custkey) RELY; alter table lineorder add constraint lo_customer_pk foreign key (lo_custkey) references customer (c_custkey) RELY DISABLE NOVALIDATE; alter system set query_rewrite_integrity=TRUSTED;

With PK/FK constraints, exactly 1 row is returned from dimension table for a fact row

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Rules and Framework Partition the Fact Table on the Time Dimension

• Example: Interval partitioning

CREATE TABLE LINEORDER ( "LO_ORDERKEY" NUMBER NOT NULL ENABLE ,"LO_LINENUMBER" NUMBER ... other columns ) partition by range ( LO_ORDERDATE ) interval (numtoyminterval(1, 'MONTH')) ( partition R199201 values less than (to_date('19920201', 'YYYYMMDD')) ) ;

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Configure Table for In-Memory Column Store SQL> alter table lineorder 2 inmemory memcompress for query 3 duplicate all; Table altered.

• Use MEMCOMPRESS FOR QUERY for

performance • Use DUPLICATE ALL to ensure extents are loaded

in all RAC instances on Exadata

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Populate the In-Memory Column Store SQL> select /*+ full */ count(*) 2 from lineorder;

• Query the table for on-demand population

• Ensure the optimizer is choosing a full scan

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Validate Population Status set lines 150 column name format a30 col pname format a30 column owner format a20 column segment_name format a30 column populate_status format a20 column bytes_not_populated format 999,999,999,999.99 set echo on

• Query GV$IM_SEGMENTS

• Look for populate_status=‘COMPLETED’ and

bytes_not_populated=0

SELECT

v.inst_id,v.owner, v.segment_name name, v.partition_name pname, v.populate_status status, v.bytes_not_populated FROM gv$im_segments v order by 3,1,2 /

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |

Copyright © 2014, Oracle and/or its affiliates. All rights reserved. |