SQL> select level, e.employee_id, e.manager_id, e.department_id, e.first_name, e.last_name
2 from employees e, departments d, jobs j
3 where e.department_id = d.department_id
4 and e.job_id = j.job_id
5 and d.department_id = 100
6 start with e.manager_id is null
7 connect by prior e.employee_id = e.manager_id;
LEVEL EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID FIRST_NAME LAST_NAME
———- ———– ———- ————- ——————– ————————-
3 108 101 100 Nancy Greenberg
4 109 108 100 Daniel Faviet
4 110 108 100 John Chen
4 111 108 100 Ismael Sciarra
4 112 108 100 Jose Manuel Urman
4 113 108 100 Luis Popp
6 rows selected.
As you can see first row begins with level 3.
However, in my experience, most of the time when I wrote hierarchical queries, I needed the evaluation of the hierarchy to be performed after the where clause. In this case, I recommend inner queries with NO_MERGE hint. Using NO_MERGE hint will prevent Oracle SQL Optimizer to merge the inner query with the hierarchical one. Of course, to prevent merge, developers should be careful about not passing any references from inner query to the outer (hierarchical) one:
SQL> select level, e2.*
2 from (select /*+ NO_MERGE */ e.employee_id, e.manager_id, e.department_id, e.first_name, e.last_name
3 from employees e, departments d, jobs j
4 where e.department_id = d.department_id
5 and e.job_id = j.job_id
6 and d.department_id = 100) e2
7 start with e2.employee_id = 108 –I know the root employee of my query
8 connect by prior e2.employee_id = e2.manager_id;
LEVEL EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID FIRST_NAME LAST_NAME
———- ———– ———- ————- ——————– ————————-
1 108 101 100 Nancy Greenberg
2 109 108 100 Daniel Faviet
2 110 108 100 John Chen
2 111 108 100 Ismael Sciarra
2 112 108 100 Jose Manuel Urman
2 113 108 100 Luis Popp
6 rows selected.
Same result except level values. This is because SQL Optimizer first evaluated inner query (joins and other where clause filters) and then evaluated the hierarchy. Furthermore, if you compare SQL plans of the two queries, you will see a full table access for the first one while second one has no full table access.
Uğur Koçak
Using Where Clause with Hierarchical Queries
Running ant javac inside Eclipse
You may often want to use javac task in your ant build files. In order to execute javac tasks inside Eclipse, you need to do a little bit of work. Below are the steps I follow to get javac executed succesfully. I hope it helps.
1. Define build.compiler property in your ant build file.
<project basedir="." default="compile" name="Test Builder">
<property name="build" location="C:\Users\Administrator\Desktop\build"/>
<property value="org.eclipse.jdt.core.JDTCompilerAdapter" name="build.compiler"/>
<target name="compile" >
<javac srcdir="." destdir="${build}" target="1.5" source="1.5" debug="true">
</target>
</project>
2. You should have a file named org.eclipse.jdt.core_{version}.jar located at plugins directory under your
Eclipse distribution. Extract this file into a desired location. You should see “jdtCompilerAdapter.jar”
at the extracted location.
3. Select Run->External Tools->External Tools Configuration from the Eclipse menu.
a- Open the Classpath tab. Click on the Add External Jar button and select the jdtCompilerAdapter.jar file
that you obtained from step 2.
b- Open the JRE tab. Make sure to check “Run in the same JRE as the workspace” box.
Query optimization in MySQL with Subqueries
Recently I had to fetch data from several tables using ORDER BY and LIMIT statements in MySQL. The problem with LIMIT statement is that it’s applied in the last step after all the rows are fetched. I needed to fetch rows that fit in boundaries specified by x1,y1,x2,y2 as well as information about the writer of these entries. Tables used in the process were:
CREATE TABLE `location` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `lat` double NOT NULL, `lng` double NOT NULL, PRIMARY KEY (`id`), KEY `idx_latLng` (`lat`,`lng`), KEY `idx_lat` (`lat`), KEY `idx_lng` (`lng`) ) ENGINE=InnoDB CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `fullName` varchar(255) DEFAULT NULL, `gender` varchar(1) DEFAULT NULL, `birthdate` date DEFAULT NULL, `email` varchar(255) DEFAULT NULL, `username` varchar(255) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `language` varchar(3) DEFAULT 'en', `createdate` datetime NOT NULL, `lastaccessdate` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `email_uk` (`email`), UNIQUE KEY `userName_uk` (`username`) ) ENGINE=InnoDB CREATE TABLE `entry` ( `id` int(11) NOT NULL AUTO_INCREMENT, `content` text NOT NULL, `title` varchar(4000) DEFAULT NULL, `location` int(11) NOT NULL, `user` int(11) NOT NULL, `createdate` datetime NOT NULL, `lastmodifieddate` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `entry_user_fk` (`user`), KEY `entry_location_fk` (`location`), KEY `idx_createdate` (`createdate`), CONSTRAINT `entry_location_fk` FOREIGN KEY (`location`) REFERENCES `location` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `entry_user_fk` FOREIGN KEY (`user`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB
Location table has ~250 records, entry table has ~13K records, and user table has ~50 records. This is auto-generated test data that we’ve used for development purposes.
The initial query was:
select e.*, u.*, l.* from entry e, user u, location l where e.user = u.id and e.location = l.id and ((l.lat between x1 and x2) and (l.lng between y1 and y2)) order by e.id desc limit 0,10
It took ~1.5 second to complete. It is not very very bad but it must be improved. Then I decided to use location as a derived table:
select e.*, u.*, l.* from entry e, user u, (select l2.* from location l2 where (l2.lat between x1 and x2) and (l2.lng between y1 and y2)) l where e.user = u.id and l.id = e.location order by e.id desc limit 0,10
This last one took ~1.5-2 seconds. The last change clearly didn’t help very much!
I only need 10 rows from thousands so why to join thousands of rows then limit to 10? LIMIT statement can be moved into a derived table too:
select e.*, u.*, l.*
from (
select * from e2
where e2.location in (select l2.id from location l2 where (l2.lat between x1 and x2) and (l2.lng between y1 and y2))
order by e2.id desc
limit 0,10
) e, user u, location l
where e.user = u.id and l.id = e.location
order by e.id desc
The last query took ~0.2 second to complete. Query times were obtained from MySQL profiler. Difference between queries can also be viewed using EXPLAIN statement:
Query 1:
+----+-------------+-------+--------+------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+-------+--------+------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+
| 1 | SIMPLE | l | range | PRIMARY,idx_latLng,idx_lat,idx_lng | idx_latLng | 16 | NULL | 68 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | e | ref | entry_user_fk,entry_location_fk | entry_location_fk | 4 | test.l.id | 149 |
|
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | test.e.user | 1 |
|
+----+-------------+-------+--------+------------------------------------+--------------------+---------+--------------------+------+----------------------------------------------+
Query 2:
+----+-------------+------------+--------+-----------------------------------+--------------------+---------+----------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+-------------+------------+--------+-----------------------------------+--------------------+---------+----------------+------+---------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 23 | Using temporary;
Using filesort |
| 1 | PRIMARY | e | ref | entry_user_fk,entry_location_fk | entry_location_fk | 4 | l.id | 149 |
|
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | test.e.user | 1 |
|
| 2 | DERIVED | l2 | ALL | idx_latLng,idx_lat,idx_lng | NULL | NULL | NULL | 268 | Using where
|
+----+-------------+------------+--------+-----------------------------------+--------------------+---------+----------------+------+---------------------------------+
Query 3:
+----+--------------------+------------+-----------------+------------------------------------+---------+---------+---------------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
+----+--------------------+------------+-----------------+------------------------------------+---------+---------+---------------+------+----------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 10 | Using filesort |
| 1 | PRIMARY | u | eq_ref | PRIMARY | PRIMARY | 4 | e.id | 1 |
|
| 1 | PRIMARY | l2 | eq_ref | PRIMARY | PRIMARY | 4 | e.id | 1 |
|
| 2 | DERIVED | e2 | index | NULL | PRIMARY | 4 | NULL | 10 | Using where
|
| 3 | DEPENDENT SUBQUERY | l | unique_subquery | PRIMARY,idx_latLng,idx_lat,idx_lng | PRIMARY | 4 | func | 1 | Using where
|
+----+--------------------+------------+-----------------+------------------------------------+---------+---------+---------------+------+----------------+
M. Serdar Biçer @msbicer
Passing function parameters from JavaScript to JavaFX
Most of the time, you won’t notice a delay while calling JavaFX functions from JavaScript. However, there are times you may have performance issues. Recently, we had such an issue in one of our projects: A JavaScript function was consuming a considerable amount of time. When we looked into the problem, we noticed that we were passing a big JavaScript String array to a JavaFX function as a parameter. Obviously, there was an issue about passing the array object. As an alternative, we concatenated the values in the String array and passed them to JavaFX as a single String object. And it worked! Our application ran much faster after this change.
I have created a simple JavaFX application for testing purposes. The code has two different functions which take different parameters:
import javafx.scene.*;
// This function takes a String array as parameter
function passStringArray(value: String[]){
}
// This function takes a String as parameter
function passString(value : String){
var stringArr = value.split(",");
}
And I call the JavaFX applet from JavaScript like this:
function passStringArray(){
var stringArr = [];
//Create a String array of size 5000 for test purpose
for(i=0; i<5000;i++){
stringArr.push(i + "");
}
var t1 = new Date();
var test = document.getElementById("test");
//Pass the String array as parameter
test.script.passStringArray(stringArr);
//Output the measured time
document.getElementById("output").innerHTML = "Measured Time: " + (new Date()-t1) + " ms";
}
function passString(){
var stringArr = [];
//Create a String array of size 5000 for test purpose
for(i=0; i<5000;i++){
stringArr.push(i + "");
}
var joinedString = stringArr.join(',');
var t1 = new Date();
var test = document.getElementById("test");
//Pass the String as parameter
test.script.passString(joinedString);
//Output the measured time
document.getElementById("output").innerHTML = "Measured Time: " + (new Date()-t1) + " ms";
}
You can download all the files needed to test the application from here.
Follow these steps to test this application:
1. Extract the package you downloaded.
2. Run “Test.html” file in your browser.
3. Click on the “Pass String Array” and “Pass String” buttons to see the performance difference.
Yücel Ünlü
Adding CLOB values to XML as CDATA
The solution is to divide large CLOB values into multiple smaller chunks. The size of each chunk we create is 8000. We are not using the maximum length allowed for VARCHAR2 data type (32767 bytes) because we have a multi-lingual database and some characters are stored in 4 bytes.
We are using CDATA nodes to store these chunks because our CLOB values generally contain data as XML and we don’t want to deal with escape characters.
The following function takes a CLOB and an XMLDOM.domNode as parameters and returns an XMLDOM.domNode which contains the CLOB value. If the length of the CLOB is bigger than the defined maximum length (8000 in our case), the method divides the CLOB into several chunks prior including it to the domNode.
function addValueToXMLNode(in_parent_node xmldom.domNode, in_value_cl CLOB)
return xmldom.domNode
is
v_offset_nr number;
v_left_nr number;
v_partNo_nr number;
v_buffer_tx varchar2(32767);
v_maxdatalength_nr number := 8000;
v_parent_doc xmldom.domDocument;
v_part_node xmldom.domNode;
v_bufferedValue_cdata xmldom.DOMCDATASection;
v_temp_node xmldom.domNode;
v_value_node xmldom.domNode;
begin
-- get the owner document of the parent node
v_parent_doc := xmldom.getownerdocument(in_parent_node);
-- create a new node named "value" and add it to the parent node
v_value_node := xmldom.makeNode(xmldom.createelement(v_parent_doc, 'value'));
v_temp_node:= xmldom.appendchild(in_parent_node, v_value_node);
-- get the length of CLOB value
v_left_nr := dbms_lob.getlength(in_value_cl);
-- if CLOB is bigger then the defined maximum value,
-- divide it, otherwise add it directly to the parent node
if v_left_nr > v_maxdatalength_nr then
-- set multi part attribute to Yes
xmldom.setattribute(xmldom.makeelement(v_value_node), 'multiPart', 'Y');
v_offset_nr := 1;
v_partNo_nr := 1;
loop
exit when v_left_nr <= 0;
if v_left_nr > v_maxdatalength_nr then
v_buffer_tx := dbms_lob.substr(in_value_cl, v_maxdatalength_nr, v_offset_nr);
v_left_nr := v_left_nr - v_maxdatalength_nr;
v_offset_nr := v_offset_nr + v_maxdatalength_nr;
else
v_buffer_tx := dbms_lob.substr(in_value_cl, v_left_nr, v_offset_nr);
v_left_nr := 0;
end if;
-- create a node for each chunk and give it a number
v_part_node := xmldom.makenode(xmldom.createelement(v_parent_doc,'part'));
xmldom.setattribute(xmldom.makeelement(v_part_node), 'no', v_partNo_nr);
v_partNo_nr := v_partNo_nr + 1;
-- create a CDATA node with buffered value and add it to the v_part_node
v_bufferedValue_cdata:=xmldom.createcdatasection(v_parent_doc,v_buffer_tx);
v_temp_node := xmldom.appendchild(v_part_node, xmldom.makeNode(v_bufferedValue_cdata));
-- add v_part_node to the value node
v_temp_node := xmldom.appendchild(v_value_node, v_part_node);
end loop;
else
-- set multi part attribute to No
xmldom.setattribute(xmldom.makeelement(v_value_node), 'multiPart', 'N');
-- create a CDATA node with buffered value and add it to the value node directly
v_bufferedValue_cdata:=xmldom.createcdatasection(v_parent_doc, to_char(in_value_cl));
v_temp_node := xmldom.appendchild(v_value_node, xmldom.makeNode(v_bufferedValue_cdata));
end if;
return in_parent_node;
end;
As the next step, we need to extract the chunks from the XML and rebuild the CLOB..
The following function accepts an XMLDOM.domElement as its input parameter, extracts the CLOB inside the domElement and returns it.
function readValueFromXMLNode(in_parent_node xmldom.domNode)
return clob
is
v_value_cl clob;
v_multiPart_yn varchar2(1);
v_parent_doc xmldom.domDocument;
v_part_node xmldom.domNode;
v_children_nodeList xmldom.domNodeList;
v_child_node xmldom.domNode;
v_value_node xmldom.domNode;
begin
-- get owner document
v_parent_doc := xmldom.getownerdocument(in_parent_node);
-- get value node
v_value_node := xmldom.getfirstchild(in_parent_node);
-- check if this is multipart value or not
v_multiPart_yn := xmldom.getAttribute(xmldom.makeelement(v_value_node), bdf_constant.attrmultipart);
if v_multiPart_yn = 'Y' then
-- get the list of the nodes that have the parts
v_children_nodeList := xmldom.getchildnodes(v_value_node);
-- empty clob value
v_value_cl := '';
-- read all values and append to clob
for index_nr in 0..xmldom.getlength(v_children_nodeList)-1 loop
v_child_node := xmldom.item(v_children_nodeList, index_nr);
v_part_node := xmldom.getfirstchild(v_child_node);
v_value_cl := v_value_cl || xmldom.getnodevalue(v_part_node);
end loop;
else
-- if it is not multi-part value, get the CDATA node and read the value
v_child_node := xmldom.getfirstchild(v_value_node);
v_value_cl := xmldom.getnodevalue(v_child_node);
end if;
return v_value_cl;
end;
Here is sample code which calls both functions:
declare
v_value_cl clob;
v_returned_cl clob;
v_parent_doc xmldom.domDocument;
v_parent_node xmldom.domNode;
begin
-- create new xml document
v_parent_doc := xmldom.newdomdocument('
');
-- get parent noode
v_parent_node := xmldom.makenode(xmldom.getdocumentelement(v_parent_doc));
-- create sample clob value
v_value_cl :=
'<root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="http://sinop/fs_smt/bdf_ide_dev.xsd">
<mainframe title="Formspider" width="500" height="500" maximizeOnStartUp="Y" icon="/icons/mainframe/fs.png" resizable="Y" close="Y" minimize="Y" maximize="Y">
<events>
<keyEvents></keyEvents>
</events>
</mainframe>
</root>';
-- add value to XML
v_parent_node := addValueToXMLNode(v_parent_node, v_value_cl);
-- read value from XML
v_returned_cl := readValueFromXMLNode(v_parent_node);
-- compare CLOB values
if dbms_lob.compare(v_value_cl,v_returned_cl) = 0 then
dbms_output.put_line('successful');
end if;
end;
Samet Basaran
How to get DDL from Oracle dictionary programmatically
While you are happily developing PL/SQL with your favorite tool be it TOAD, SQL Navigator or SQL Developer, all of a sudden your boss comes and tells you that you have to extract all stored programs in your Oracle Database Schema and save them to the file system. Since you have 45678457 PL/SQL packages out there you know you have to automate this process. You know Oracle saves these stored programs somewhere, but how do you get them back? As most of the time, Oracle has a built-in solution for this need as well: the DBMS_METADATA. package in the SYS schema.
DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;
The function works as advertised but there are some tricks that you might need to know.
First of all, the resulting DDL script is schema specific. When you extract the DDL of a package named “Package1″ in schema HR, the generated DDL starts with;
CREATE OR REPLACE PACKAGE "HR"."Package1" .
This is not always desired. Here is a small trick that removes schema name,
select replace(dbms_metadata.get_ddl('PACKAGE','PACKAGE1'),'"'||user||'"."PACKAGE1"','PACKAGE1') from dual
Below is a function that removes schema name from DDL scripts;
CREATE OR REPLACE
FUNCTION GETDDL ( in_object_type_tx IN VARCHAR2,
in_name_tx IN VARCHAR2
) return clob is
v_username_tx varchar2(255);
v_cl clob;
v_number_nr number;
begin
select user into v_username_tx from dual;
v_cl := dbms_metadata.get_ddl(in_object_type_tx,in_name_tx,v_username_tx);
-- remove schema name...
v_cl := replace(v_cl,upper('"'||v_username_tx||'"."'||in_name_tx||'"'),upper(in_name_tx));
return v_cl;
end;
Another trick that you should remember is that, Oracle stores all object types and object names in upper case. If you do not use upper case you will get an error even though object name you provide seems correct.This tip may seem obvious to PL/SQL Developers but not so much if you are primarily a Java Developer who happens to maintain some PL/SQL code.
Final advice is about the object_type input parameter of the GET_DDL function. As you can guess, the valid values for this input parameter are the object types that are available in Oracle. The most commonly used ones are;
FUNCTION
INDEX
PACKAGE
PACKAGE_BODY
PROCEDURE
SEQUENCE
TABLE
TRIGGER
TYPE
VIEW
Well, the problem is, when you extract ‘PACKAGE’, resulting DDL includes the package specification and the package body. However, if you need to extract only the package specification, the types above will not get the job done.
There is another object type that is not well documented; ‘PACKAGE_SPEC’. If you need to extract only Package specification, you should use ‘PACKAGE_SPEC’ as object type.
Serkan Kasapbaşı
FormattedTextField (with MaskFormatter) on Javascript
While working on the Formspider framework (which has both Java and JavaScript renderers), we needed to implement a FormattedTextField component. It was easy to implement on Java side by using JFormattedTextField with a MaskFormatter. For JavaScript renderer we implemented a new MaskFormatter class as similar as possible to java.
Here is the source code that also includes an example:
JavascriptMaskFormatter.rar
In handlers.js, you can see event handlers for key inputs (including navigation keys), paste operation and validation on blur.
(###) ###-##-## mask is used in the example. Available masks are { # U L A ? * H }. See Java MaskFormatter page for more information.
Tested on IE8, FF3 and Chrome6.
Öskan Şavlı
Installing Oracle 10g R2 Using Oracle Public Yum Repository on 64 Bit Oracle Enterprise Linux
binutils-2.15.92.0.2-10.EL4 compat-db-4.1.25-9 control-center-2.8.0-12 gcc-3.4.3-9.EL4 gcc-c++-3.4.3-9.EL4 glibc-2.3.4-2 glibc-common-2.3.4-2 gnome-libs-1.4.1.2.90-44.1 libstdc++-3.4.3-9.EL4 libstdc++-devel-3.4.3-9.EL4 make-3.80-5 pdksh-5.2.14-30 sysstat-5.0.5-1 xscreensaver-4.18-5.rhel4.2
Go to yum.repos.d folder by typing
# cd /etc/yum.repos.d
[el5_u5_base]
name=Enterprise Linux $releasever U5 - $basearch - base
baseurl=http://public-yum.oracle.com/repo/EnterpriseLinux/EL5/5/base/$basearch/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
[ol5_u5_base]
name=Oracle Linux $releasever - U5 - x86_64 - base
baseurl=http://public-yum.oracle.com/repo/OracleLinux/OL5/5/base/x86_64/
gpgkey=http://public-yum.oracle.com/RPM-GPG-KEY-oracle-el5
gpgcheck=1
enabled=1
Save and exit from the editor.
# yum install oracle-linux
#yum install ./oracle-validated-1.0.0-22.el5.x86_64.rpm
#sysctl-p.
3. Disable SELinux
Due to two internal bugs in the Oracle Database (Bug 6140224, Bug 6079461), SELinux has to be disabled on RHEL/OEL 5 to work with Oracle database 10.2.
To permanently disable SELinux go to /etc/selinux/ folder and edit the config file. Change SELINUX = enforcing to permissive or disabled
4. Create Directories
Set the ownership:
Create oraInventory under /u01/app/oracle
5. Modify Oracle User’s Bash Profile
Add the following lines to the /home/oracle/.bash_profile file
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2/db_1; export ORACLE_HOME
ORACLE_SID=ORCL; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
This completes the preinstallation tasks.
Install the Oracle Database
$ gunzip 10201_database_linux_x86_64.cpio.gz
$ cpio -idmv < 10201_database_linux_x86_64.cpio
Login as the oracle user and run:
# ./runInstaller
Post Installation
At this point your Oracle database should be up and running.
Aziz Ünsal
Be Careful When Using xmlDom.getElementsByTagName
In the code below, new elements are added to an XML Document. We get the parent node with xmldom.getElementsByTagName API. In the first example, we get the parent node each time when a new node is added. The code executes in 0.27 seconds. In the second example, we find the parent node only once and reuse it. The same operation now takes only 0.05 seconds.
exec test_xml.initxml;
/
select test_xml.getxmltype from dual
/
-- add new action elements (slow way)
declare
v_doc_xml xmldom.domdocument;
v_newActionElement_xml xmldom.domelement;
v_newActionNode_xml xmldom.domnode;
v_newActionCount_nr number := 1000;
begin
v_doc_xml := test_xml.getxml;
for i in 1..v_newActionCount_nr loop
v_newActionElement_xml := xmldom.createelement(v_doc_xml, 'action');
xmldom.setAttribute(v_newActionElement_xml, 'atrib1', 'a');
-- find actions node using xmldom.getElementsByTagName
-- it is executed 1000 times
v_newActionNode_xml := xmldom.appendchild(xmlDom.item(xmldom.getelementsbytagname(v_doc_xml, 'actions'),0),
xmldom.makeNode(v_newActionElement_xml));
end loop;
end;
/
-- add new action elements (fast way)
declare
v_doc_xml xmldom.domdocument;
v_actionsnode_xml xmldom.domnode;
v_newActionElement_xml xmldom.domelement;
v_newActionNode_xml xmldom.domnode;
v_newActionCount_nr number := 1000; ---TODO bu degiskenin ismini degistir
begin
v_doc_xml := test_xml.getxml;
-- find actions node using xmldom.getElementsByTagName
-- it is executed once only
v_actionsnode_xml := xmlDom.item(xmldom.getelementsbytagname(v_doc_xml, 'actions'),0);
for i in 1..v_newActionCount_nr loop
v_newActionElement_xml := xmldom.createelement(v_doc_xml, 'action');
xmldom.setAttribute(v_newActionElement_xml, 'atrib2', 'b');
v_newActionNode_xml := xmldom.appendchild(v_actionsnode_xml, xmldom.makeNode(v_newActionElement_xml));
end loop;
end;
/
select test_xml.getxmltype from dual
/
Uğur Koçak
Using Code Optimization Software to reduce the size of JavaFX Applications
You may increase the efficiency of your Java applications by applying some optimization methods on the Java byte-code. Shrinking and obfuscation are commonly used for this purpose. Shrinking is the process of removing unused classes, methods and fields, resulting in the processed byte-code having smaller size. Obfuscation is primarily used to make the code harder to debug and reverse-engineer. As an additional feature, obfuscation also reduces the size of the byte-code, since this process replaces the identifers in class files with shorter ones.
Reduction in the size of byte-code often implies an improvement in load time. Applet developers could especially benefit from such optimizations, since the transfer of compiled code accross a network is an important bottleneck.
In this article, I will show the use of optimization methods on a sample JavaFX application. I will use ProGuard 4.5 as the optimization tool. I choosed this tool, because it is available under the GPL free licence, easy to use and has a few more additional features than the other options out there. For more information about ProGuard, please see http://proguard.sourceforge.net/ .
Here is the JavaFX code:
package test;
import javafx.stage.Stage;
import javafx.scene.Scene;
import javafx.scene.text.Text;
import javafx.scene.text.Font;
import java.applet.Applet;
import javafx.scene.layout.VBox;
import org.apache.tools.ant.taskdefs.Concat;
import org.apache.tools.ant.taskdefs.Cvs;
import org.apache.tools.ant.taskdefs.Delete;
import org.apache.tools.ant.taskdefs.GZip;
import org.apache.tools.ant.taskdefs.Length;
import org.apache.tools.ant.taskdefs.Move;
import org.apache.tools.ant.taskdefs.SendEmail;
import org.apache.tools.ant.types.resources.selectors.Date;
// Create some random objects which reference the Ant.jar
var objList = new java.util.ArrayList();
objList.add(new Concat());
objList.add(new Move());
objList.add(new Cvs());
objList.add(new Delete());
objList.add(new GZip());
objList.add(new Length());
objList.add(new SendEmail());
objList.add(new Date());
for(obj in objList){
println(obj);
}
var startTime = FX.getArgument("startTime");
var loadTime = 0.0;
try {
var endTime = java.lang.System.currentTimeMillis();
var startTimeL = Long.parseLong(startTime.toString());
loadTime = (endTime - startTimeL);
} catch (e : java.lang.Exception) {
e.printStackTrace();
}
Stage {
scene: Scene {
width: 400
height: 250
content: [
Text { content: "Load Time: {loadTime} ms"
y: 150 x: 100 font: Font { size: 25 } }
]
}
}
The only task this code carries out is to create some random objects referencing the Ant library. This may not mean much for an applet, but the purpose of this example is to demonstrate the difference between the load times of optimized and non-optimized byte code. Now, since we have references to ant.jar file from JavaFX code, we may expect a faster loading time after making some optimizations on ant.jar. Here are the steps to follow:
- Download the ProGuard distribution and extract it to a location on your computer.
- Run proguardgui.bat which resides in ${PROGUARD_HOME}\bin directory. This will open ProGuard in graphical mode. (When you click the buttons in the menu on the left side, appropriate pages will be opened to allow you make configurations.)
- Input/Output configuration: As input, we need to add the ant.jar file, since this is the one we want to process. We also need to specify the output path to which the ProGuard will generate the final jar. In the libraries section, ProGuard allows us to specify referenced libraries. Java runtime library is added in this section by default, so we don’t need to make further changes here.
- Shrinking configuration: We may specify which classes to shrink or keep at this page. In our case, we should keep the classes that is referenced from JavaFX. Down at the page, the section titled “Keep additional classes and class members” allows us to do that.
- Obfuscation configuration: Similar to the shrinking step, we should keep the JavaFX referenced classes from being obfuscated.
- I did not make any changes in Optimization Page. Finally, I had to check “Ignore warnings about possibly erronous input” box in Information Page, since ProGuard could not produce the output jar without this option checked. In our example, this change does not cause any problems, however you should be careful with this option when you are working on your own projects.
- Now that we made the necessary configurations, we are ready to process the file. Clicking on the Process button on the Process page will produce the output jar for us.
You can download the source code and binaries from here. When you extract the downloaded package, you will see two folders named as optimized_test and nonoptimized_test. You can compare the load times by opening Main.html files in these folders. Please note that, you should clear the Java cache between consecutive executions of the JavaFX application. Otherwise, the results will not be healthy.
Below are my results of load time comparison :
| Internet Explorer 8.0 | Google Chrome 5.0 | |
| Non-Optimized | 1045 ms | 1250 ms |
| Optimized | 776 ms | 958 ms |
My system properties are:
Processor: Intel Core 2 Duo T9550 2.67GHz
Operating System: Windows 7 Ultimate 32 bit
Memory: 3 GB
Yücel Ünlü
