Dave Hannum Ohio University, Athens, OH

1
ORACLE Tricks and Techniques Dave
Hannum ColdFusion Programmer Ohio
University Athens, OH
Dave Hannum Ohio University, Athens, OH
2
10 years as a ColdFusion Programmer 14 years
experience with SQL

• DB2
• Informix
• SQL Server
• MySQL
• Oracle

Dave Hannum Ohio University, Athens, OH
3
For the purpose of this presentation, we are
assuming that
a) You know what a RDBMS is
b) You know what SQL is
c) You regularly use some RDBMS and write SQL
d) You are familiar with Oracle as a database
Dave Hannum Ohio University, Athens, OH
4
Dave Hannum Ohio University, Athens, OH
5
Tip 1
Establish a good working relationship with your
DBA!
Create complex views and materialized views!
Write stored procedures for you!
Analyze and debug slow running queries!
Bump up the priority of your application!
Dave Hannum Ohio University, Athens, OH
6
Tip 2
Allow Oracle to cache as many of your SQL
statements as possible
1) Use a common pattern when writing your SQL
2) Always use the CFQUERYPARAM tag !!!!!
select emp_name from employees where emp_id
ltcfqueryparam cfsqltype"cf_sql_integer"
value"form.id"gt
Dave Hannum Ohio University, Athens, OH
7
Tip 2 (cont)
3) Using the CFQUERYPARAM tag allows Oracle to
make use of Bind Variables to improve
performance
BIND VARIABLES are substitution variables that
are used in place of literals
They allow Oracle to duplicate the SQL every time
the query is executed, thus saving the overhead
of hard parsing
Dave Hannum Ohio University, Athens, OH
8
Tip 3
Understand when you should and should not use
full table scans
Full table scans are faster on small tables
Full table scans can be faster on very large
tables
When retrieving 10 or more of the data
Can be more efficient when retrieving as little
as 1
Always use indexes when retrieving one row of data
Dave Hannum Ohio University, Athens, OH
9
Tip 4
Use sequence tables for auto numbering of records
Pros and Cons of sequence tables
Con Not as easy as Auto Numbering insert into
ltmyTablegt (ltmyColumngt) values (ltmySequencegt.nex
tval)
Dave Hannum Ohio University, Athens, OH
10
Tip 4 (cont)
Use sequence tables for auto numbering of
records
Pros and Cons of sequence tables
Pro Greater control over the sequencing
Save I/O on heavy hit queries by caching blocks
of sequence numbers.
Can use the same sequence number on multiple
tables insert into ltmyTablegt (ltmyColumngt) val
ues (ltmySequencegt.currval)
Dave Hannum Ohio University, Athens, OH
11
Tip 5
Make use of the Oracle dual table
dual is the name of a dummy table in Oracle
that allows you to run SQL that does not
logically have a table name.
select  sysdate from dual
SYSDATE ---------- 19-MAY-08
Dave Hannum Ohio University, Athens, OH
12
Tip 6
Make use of Oracle subqueries in the FROM clause
of your SQL statements
Oracle allows you to use datasets like tables
SELECT e.FROM emp e ,(SELECT
d.deptno              FROM dept
d              WHERE d.dname 'SALES') dWHERE
e.deptno d.deptno
Dave Hannum Ohio University, Athens, OH
13
Tip 7
Learn to use Oracle hints to control query
behaviors
Hints allow you to make or override decisions
usually reserved for the Oracle Optimizer
EX / FULL(table) / Explicitly chooses a
full table scan for the specified table
SELECT / FULL / FROM EMPLOYEE WHERE ID1
Dave Hannum Ohio University, Athens, OH
14
Tip 8
Do NOT treat an empty string the same as a NULL
Use a NULL when
A column in a row HAS NO VALUE
Use a NULL when the actual value is unknown or a
value would not be meaningful
Remember that a NULL and zero are not equivalent
Dave Hannum Ohio University, Athens, OH
15
Tip 8 (cont)
Avoid allowing NULL values to break your query
Oracle provides built in functionality to make
NULLS easier to handle
In a WHERE clause, use is NULL vs. NULL
Use NVL to tell Oracle to return a value even if
a NULL is found
SELECT stu_name, NVL(grade,NG) as qtrGrade
FROM studentGrades WHERE stu_id S10043226
and stu_class HS225 and class_qtr
FALL2008
Dave Hannum Ohio University, Athens, OH
16
Tip 9
Use a CFTRY / CFCATCH statement to capture
Oracle errors
The infameous Oracle Error ORA-00942 table
or view does not exist
ltcftrygtlt!--- CFQUERY goes here ---gtltcfcatch
type"database"gt  ltcfif cfcatch.nativeerrorcode
is "942"gt    lt!--- Your custom notification goes
here ---gt  lt/cfifgtlt/cfcatchgtlt/cftrygt
Dave Hannum Ohio University, Athens, OH
17
Tip 10
Let the database do the work whenever possible
How many times have you seen or done this?
ltcfquery name"qryGetSalesData"
datasource"myDSN"gt select customer_number,
sale_amount from customer_data where
customer_number 12345 lt/cfquerygt
ltcfset total_sales 0gt ltcfloop
query"qryGetSalesData"gt ltcfset total_sales
total_sales sale_amountgt lt/cfloopgt ltcfoutputgt
total_sales lt/cfoutputgt
Dave Hannum Ohio University, Athens, OH
18
Tip 10 (cont)
Let Oracle do the work its better and faster
at it!
ltcfquery name"qryGetSalesData"
datasource"myDSN"gt select sum(sale_amount) as
total_sales from customer_data where
customer_number 12345 lt/cfquerygt
ltcfoutputgt total_sales lt/cfoutputgt
Dave Hannum Ohio University, Athens, OH
19
Tip 11
Use Stored Procedures for complex transactions
where practical
Stored Procedures are compiled SQL in the
database that, can be shared between applications
Dave Hannum Ohio University, Athens, OH
20
Tip 11 (cont)
ltcfquery name"getList" datasource"request.siteD
SN"gt SELECT A1.CALL_NUMBER, A1.SECTION_CODE,
A1.COURSE_PREFIX, A1.COURSE_NUMBER,
A1.SECTION_CODE, A1.CLASS_S_NAME,
A1.CAMPUS_NAME, A1.INST1_PID, A1.INST1_L_NAME,
A1.INST1_F_NAME, A1.ENROLLED, A1.MAX_SIZE,
A1.TERM_YR, AL2.PINK_SLIP_FLAG, A1.TERM,
AL2.FINAL_GRADE, AL2.TOOK_NUM_UNITS,
AL2.GRADE_SYSTEM_CD, AL2.TRANSCRIPT_GRADE AS
GRADE, AL2.REVIS_EFFECT_DATE, AL2.ORIG_EFFECT_DAT
E, AL3.SSN_FORMATTED, AL3.NAME_FULL,
AL3.SCHOOL_CODE, AL3.STUD_EMAIL_ADDR,
AL3.CURRENT_STANDING, AL3.Person_ID,
AL3.BILLING_PROGRAM, AL4.PROGRAM_NAME,
AL3.VET_BENEFIT_FLAG, ADDRESS_LINE1,
ADDRESS_LINE2, CITY_NAME, STATE_CODE, ZIP_CODE,
DAY_PHONE_AREA_CD, DAY_PHONE_EXCHANGE,
DAY_PHONE_NUMBER, DAY_PHONE_EXTEN FROM
STUD_COURSE_HIST AL2, PERSONAL_INFO AL3,
XREF_PROGRAM AL4, ADDRESSES AL5, (SELECT
C.CALL_NUMBER, C.COURSE_PREFIX, C.COURSE_NUMBER,
C.SECTION_CODE, C.CLASS_NAME AS CLASS_S_NAME,
X.CAMPUS_SHORT_NAME AS CAMPUS_NAME,
I.INSTRUCTOR_PID AS INST1_PID, R.LAST_NAME AS
INST1_L_NAME, R.FIRST_NAME AS INST1_F_NAME,
C.NUM_ENROLLED AS ENROLLED, C.MAX_CLASS_SIZE AS
MAX_SIZE, C.TERM_YR, C.TERM_QTR AS TERM,
C.TERM_YR_QTR AS ACADEMIC_YR_QTR FROM
CLASS_INSTRUCTORS I, CLASSES C, XREF_CAMPUS X,
COURSE_INSTRUCT R WHERE ltcfif term is
"W"gti.term_yr ltcfqueryparam cfsqltype"cf_sql_va
rchar" value"term_yr"gt and i.term_qtr
ltcfqueryparam cfsqltype"cf_sql_varchar"
value"2"gt and ltcfelsegt i.term_yr ltcfqueryparam
cfsqltype"cf_sql_varchar" value"term_yr"gt
and i.term_qtr ltcfqueryparam cfsqltype"cf_sql_v
archar" value"term"gt and lt/cfifgt
i.instructor_pid ltcfqueryparam
cfsqltype"cf_sql_varchar" value"FORM.Inst_PID"
gt and c.term_yr i.term_yr and c.term_qtr
i.term_qtr and c.course_prefix
i.course_prefix and c.course_number
i.course_number and c.section_code
i.section_code and x.campus_code
c.campus_code AND r.inst_pid i.instructor_pid)
A1 WHERE ltCFIF Term IS "W"gt AL2.TOOK_YR_QTR
ltcfqueryparam cfsqltype"cf_sql_varchar"
value"term_yr2"gt and ((al2.class_begin_dt
between ltcfqueryparam cfsqltype"cf_sql_varchar"
value"listFirst(form.beginDates, "")"gt and
ltcfqueryparam cfsqltype"cf_sql_varchar"
value"listLast(form.beginDates, "")"gt) and
(al2.class_end_dt between ltcfqueryparam
cfsqltype"cf_sql_varchar" value"listFirst(form.
endDates, "")"gt and ltcfqueryparam
cfsqltype"cf_sql_varchar value"listLast(form.
endDates, "")"gt)) and ltCFELSEgt AL2.TOOK_YR_QTR
A1.ACADEMIC_YR_QTR AND lt/CFIFgt
AL2.COURSE_PREFIX A1.COURSE_PREFIX AND
AL2.COURSE_NUMBER A1.COURSE_NUMBER AND
AL2.SECTION_CODE A1.SECTION_CODE AND
AL3.PERSON_ID AL2.PERSON_ID AND
AL3.BILLING_PROGRAMAL4.PROGRAM_CODE ltcfif
isDefined("form.address")gtAND AL5.PERSON_ID
AL3.PERSON_ID AND AL5.ADDRESS_CODE
ltcfqueryparam cfsqltype"cf_sql_varchar"
value"form.address"gt ltcfelsegt AND
AL5.PERSON_ID AL3.PERSON_ID AND
AL5.ADDRESS_CODE 'LO lt/cfifgt ltCFIF
form.call_number IS NOT ""gt AND A1.CALL_NUMBER
ltcfqueryparam cfsqltype"cf_sql_varchar"
value"FORM.CALL_NUMBER"gt lt/CFIFgt ORDER BY
A1.Call_Number, A1.Course_Prefix,
A1.Course_Number, AL3.Name_Full lt/cfquerygt
Dave Hannum Ohio University, Athens, OH
21
Tip 11 (cont)
ltcfstoredproc procedure"pkg_classlist.getList"
datasource"request.siteDSN"gt ltcfprocparam
value"term_yr" cfsqltype"cf_sql_varchar"gt ltcf
procparam value"term_yr" cfsqltype"cf_sql_varc
har"gt ltcfprocparam value"form.inst_pid"
cfsqltype"cf_sql_integer"gt ltcfprocresult
name"getList" lt/cfstoredprocgt
Dave Hannum Ohio University, Athens, OH
22
Tip 12
Use Connection Pooling whenever possible to make
queries faster
Dave Hannum Ohio University, Athens, OH
23
Tip 13
Get Ben Fortas book
Dave Hannum Ohio University, Athens, OH
24
Tip 14
Nobody HAS all the answers, but you can know
where to GET them
Eddie Awads Blog http//awads.net/wp/
Oracle HINTS http//askanantha.blogspot.com/2007/
08/oracle-hints.html
Steve Reas Oracle Tips, Tricks Scripts
http//www.uaex.edu/srea/
Rene Nyffenegger http//www.adp-gmbh.ch/ora/sql
/index.html
Adobe ColdFusion Best Practices For Oracle
http//www.adobe.com/devnet/server_archive/articl
es/cf_best_practices_oracle.html
Dave Hannum Ohio University, Athens, OH
25
Tip 15
Attend
Athens, OH Tuesday, June 3, 2008 http//www.sbco
nference.com
Dave Hannum Ohio University, Athens, OH
26
The End Dave Hannum ColdFusion Programmer Ohio
University Athens, OH hannum_at_ohio.edu
Dave Hannum Ohio University, Athens, OH