The Oracle Lite Blog

Performance is often overlooked on a mobile database. Users are generally only dealing with thousands of rows instead of millions of rows, so not a lot of thought is spent on tuning a client database. I will show you a scenario where querying a table with 3600 hundred records can cause a fair amount of grief to a users device.

First, we create our table:
CREATE TABLE HIERARCHY
(
LEVEL_ID NUMBER ,
CHILD_ID VARCHAR2(40),
PARENT_ID VARCHAR(40)
);
Then we populate this recursive table with data.

Using msql, we will execute the following:
SQL> set timing on;
SQL> SELECT count(1)
FROM HIERARCHY START
WITH LEVEL_ID = 1
1> 2> 3> CONNECT BY PRIOR CHILD_ID = PARENT_ID;
COUNT(1)
--------
3681

1 row(s) returned

Elapsed: 0:0:18.343

Here is a screen shot of what my CPU was doing while I executed the code:

Figure 1

This small query consumed a fair amount of resources for something that is going against 3681 rows. This example uses the very powerful CONNECT BY clause. In many field based applications, where data is distributed via some type of organizational hierarchy, these types of queries are quite common. Imagine what the user would experience on their laptop or hand held device if something like this wasn’t addressed. Most users would attribute it to “my computer is running slow.” Something like this could even go unnoticed and over time, become a huge waste of time for users causing loss of productivity resulting in loss of revenue.

Executing Explain Plans in Oracle Lite
Here is what you need to do to execute explain plans in Oracle Lite:
Step 1: Using msql, connect to your Oracle Lite database.
Step 2: Execute the following create table statement:
create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_owner varchar2(30),
object_name varchar2(30),
id int,
parent_id int,
Position int,
cost int,
cardinality int,
text varchar(4096));

commit;

Note: In Oracle Lite, it is important to execute commits after DDL operations as well DML operations.

Step 3: Execute your explain plan:
EXPLAIN PLAN FOR SELECT LPAD(LEVEL_ID,LENGTH(TO_CHAR(LEVEL_ID))+LEVEL*2,' '), CHILD_ID , PARENT_ID
FROM HIERARCHY
START WITH LEVEL_ID = 1
CONNECT BY PRIOR CHILD_ID = PARENT_ID;

Step 4: Your explain plan information is stored in the plan_table:

Figure 2

You can see from the explain plan that the query is doing two full table scans on the HIERARCHY table. Even though this is just one table, the CONNECT BY clause behind the scenes joins HIERARCHY to itself and since no indexes exist, full table scans are performed on both joins (the CHILD_ID and PARENT_ID).

To improve performance here, we will add two indexes, one for CHILD_ID and one for PARENT_ID.
CREATE INDEX IDX_HIERARCHY_1 ON HIERARCHY(CHILD_ID);
CREATE INDEX IDX_HIERARCHY_2 ON HIERARCHY(PARENT_ID);

Now, lets see if the performance of the query improved:
SQL> SELECT count(1)
FROM HIERARCHY START
WITH LEVEL_ID = 1
1> 2> 3> CONNECT BY PRIOR CHILD_ID = PARENT_ID;
COUNT(1)
--------
3681

1 row(s) returned

Elapsed: 0:0:0.63

The query has gone from just over 18 seconds to under 1 second. Lets take a look at the new explain plan:

You can see from the explain plan that we have been reduced to one full table scan (can’t do without this because of the nature of the recursive relationship) and we now replaced the full table scan with an ACCESS BY ROWID scan on IDX_HIERARCHY_2. We would still need IDX_HIERARCHY_1 down the road because users will most likely be accessing this table by CLIENT_ID. So, this is a safe guard.

Don’t underestimate performance tuning. Don’t just rely on your DBA or your users to tell you something is slow. Have all your developers spend some time learning how to execute explain plans and SQL performance tuning techniques so that your application is better prepared for the real world.