Still using Oracle 7 or 8 and wondering whether or not to upgrade to 8i? In this article, I will discuss the top reasons to upgrade to 8i, which are: Simplified dynamic SQL syntax, bulk binds, the Nocopy option in procedures, the Rollup, Cube, Top N operators, temporary tables, and materialized views.
6. Simplified dynamic SQL. In Oracle 8 you gained the ability to build and submit SQL at run time. This was useful if not all the information was available at compile time. The downside was that the statement had to be built using the DBMS_SQL package, which could be difficult to use. In 8i this has been greatly streamlined. Consider the procedure shown in Listing A and B.Listing A: Procedure using Oracle 7.3 or 8
methodsProcedure analyze_stats ( table_name varchar2, means varchar2) iscursor_handle integer; statement_str varchar2; beginstatement_str:='analyze table '||table_name||' '||means||' statistics'; cursor_handle:=dbms_sql.open_cursor; dbms_sql.parse(cursor_handle,statement_str, dbms_sql.native); dbms_sql..execute(cursor_handle); dbms_sql.close_cursor(cursor_handle); end;Listing B: Procedure using Oracle 8i
method Procedure analyze_stats ( table_nme varchar2, <>B> means varchar2) isstatement_str varchar2; beginstatement_str:='analyze table '||table_name||''||means||' statistics'; execute immediate statement_str; end;
5. Bulk binds. Previously, if you executed SQL statements within
For x in 1 .. 500 loopinsert into table1 values (x,custid(x), ....); end loop;
Oracle 8i method the Oracle 8i method:
Forall x in 1 .. 500 insert into table1 values (x, custid(x), ....);
A corollary to this is the bulk collect option. This allows the binding of data before returning to the PL/SQL engine. If numtab is a table of integers and chartab of varchars, the statement
Select col1, col2 bulk collect into numtab, chartab from table1;
Would bind all the values from col's 1 and 2 into these PL/SQL tables. This results in one pass returning the desired information. You can use a similar method with fetches.
4. NocopyParameters in PL/SQL with in or in/out have been passed by value. This requires getting the value from memory, passing it, and updating it upon return, which is burdensome with large structures. In 8i, however, you may now pass by reference (for example, a pointer to the value is passed instead of the actual value). This greatly reduces the overhead incurred in memory and cpu. A sample call would be
Procedure snafu( strng in out nocopy varchar2) is ....
3. Rollup, Cube, Top N. Before 8i, if you needed a control break report you had to cobble together select statements with group by statements. The rollup command will provide this with subtotals on columns listed. Cube is similar, but will provide subtotals on all combinations. Top N provides a method to select the top n of something (for example, top five districts by sales). Suppose table1 had three columns: district, qtr, and sales. A rollup, cube, and top n query might produce
Select qtr,district, sum(sales) total from table1 Group by rollup(qtr,district);
The report would be:
Qtr district total 1st central 10,000 1st central 20,000 1st 30,000 1st west 5,000 1st 5,000 1st 35,000
Select qtr,district, sum(sales) total from table1 group by cube(qtr,district);
...would produce a report with subtotals for all combinations on district and qtr. The example is lengthy so we won't show it here. As another example,
Select rownum rank, district, sales from (select district, sum(sales) total from table1 group by districtorder by sum(sales) desc) where rownum <= 5;
Would return the top five districts by sales:
1 central 60,000 2 east 50,000 3 southeast 45,000 4 northwest 40,000
2. Temporary tables. Developers have often expressed a need for a scratch table to store preliminary results. This entails creating the table, granting rights, and ensuring the table is cleaned upon completion. In 8i, you can create a global temporary table that's either session- or transaction-specific determined by the on commit clause. On commit delete rows deletes the rows upon committing. On commit preserve rows deletes at session termination. The data is session private, meaning that each session can see and modify only its own data. These tables may be indexed, but constraints aren't allowed. For example:
Create global temporary table table1 (col1 int, col2 char(1)) on commit preserve rows;
1. Materialized views. Data warehouses exist to provide analysts with information. This may involve complex joins and aggregations. Unfortunately, these kill performance. Your best fix is to create summary tables. It's difficult, however, to ensure that the summary table is current and meaningful. Oracle 8i addresses this by providing the materialized view. In simplest terms, this is a summary table that may be updated when the fact table changes. The SQL compiler is aware of the view and will transparently rewrite queries to take advantage. There are two init.ora parameters that must be set: job_queue_processes, and job_queue_interval. These have to be nonzero values. This allows the snp background processes to activate. The procedure shown in Listing C produces a materialized view that summarizes activity by date per userid. The enable query rewrite option allows the compiler to utilize it so it doesn't need to be known to analysts and pre-written queries won't need rewriting.Listing C: Materialized view that summarizes activity by date per userid
create materialized view session_summary build immediaterefresh fast on demand enable query rewrite as select userid,create_dt,count(*) from session_tracking_historygroup by create_dt,userid;
Since this a single table view, you can use the fast option. This indicates an incremental versus a complete refresh. On demand indicates that synchronization with the basis table will be performed manually with the DBMS_MVIEW package. The other option is on commit. This will update the view whenever a transaction on the basis table commits. Finally, you must update the basis table statistics.
For More Information
- What do you think about this tip? E-mail the Edtior at firstname.lastname@example.org with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2001