Problem Description
Problem Description
When attempting to run the packages UTL_SMTP, and UTL_TCP, an error is returned indicating that the class oracle/plsql/net/TCPConnection does not exist. This class, along with others that support SQLJ functionality, should normally be loaded when the "initjvm.sql" script is run.
However, that script has already been run successfully, to completion.
ORA-29540: class oracle/plsql/net/TCPConnection does not exist
Solution Description
The initjvm.sql script (located in the ORACLE_HOME/javavm/install directory) is run automatically during normal installation of the database, except during custom installation. In any event, thescript can be run manually (as SYS) at a later time. When this script is run, approximately 8000+ java classes are loaded into the database.
Unfortunately, there is another script, initplsj.sql (located in ORACLE_HOME/rdbms/admin) which is supposed to be called by the initjvm.sql script. This does not occur. To fix the problem, runthe initplsj.sql script (as SYS). You will note that 200+ classes get loaded into the database as a result of this script.
This should then allow you to use the UTL_SMTP and UTL_TCP packages.
This script also needs to be run:
$ORACLE_HOME/javavm/install/init_security.sql
Source: Oracle Metalink
Index skip scan Hint Usage
Index skip scans improve index scans by nonprefix columns since it is often faster to scan index blocks than scanning table data blocks.
In this case a composite index is split logically into smaller subindexes. The number of logical subindexes depends on the cardinality of the initial column. Hence it is now possible to use the index even if the leading column is not used in a where clause.
Example:
SQL> create table t1(a number,b number);
Table created.
SQL> begin
2 for i in 1..1000
3 loop
4 insert into t1 values(i, 56);
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> create index t1_i on t1(a,b);
Index created.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> explain plan for select /*+ index_ss(t1 t1_i) */ * from t1 where b=56;
Explained.
SQL> start ?\rdbms\admin\utlxpls.sql
-----------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
-----------------------------------------------------------------
| SELECT STATEMENT | 1K| 4K| 1001 | | |
| INDEX SKIP SCAN T1_I| 1K| 4K| 1001 | | |-----------------------------------------------------------------
Well, the two are processed very differently
Select * from T1 where x in ( select y from T2 )
is typically processed as:
select *
from t1, ( select distinct y from t2 ) t2
where t1.x = t2.y;
Sub query is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table - typically.
As opposed to:
select * from t1 where exists (select null from t2 where y = x)
That is processed more like:
for x in (select * from t1)
loop
if (exists (select null from t2 where y = x.x)
then
OUTPUT THE RECORD
end if
end loop
It always results in a full scan of T1 whereas the first query can make use of an index on T1(x).
So, when is where exists appropriate and in appropriate? Lets say the result of the sub query:
(select y from T2)
is "huge" and takes a long time. But the table T1 is relatively small and executing ( select null from t2 where y = x.x ) is very very fast (nice index on t2(y)). Then the exists will be faster as the time to full scan T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery we need to distinct on.
Let’s say the result of the sub query is small -- then IN is typically more appropriate.
If both the sub query and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors.
The ROT (rule of thumb) here is :
BIG outer query and SMALL inner query = IN. SMALL outer query and BIG inner query = WHERE EXISTS.