Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

Re: Table Joins

stephen booth

2006-05-24

Replies:
On 24/05/06, genegurevich@(protected)
<genegurevich@(protected):
> One of my developers insists that joins by numerical fields result in
> better preformance than the joins by
> character fields. I don't remember reading much on this point. Is there any
> truth in it?

I don't recall reading anything about that either. I suppose there's
a certain logic to it, at the processor level you're comparing numbers
and there's probably fewer steps when you're already dealing with
numbers rather than character strings. I suspect that, in a real
world system, any time savings from using numbers instead of
charcaters would be swamped by the other things that take time.

I'd be tempted to say tot eh developer: "OK, prove it."

It shouldn't be too hard to generate a test case to compare. Create a
pair of tables each with a common key column and a content column.

e.g.

create table a1
(
thekey varchar2(10),
content varchar2(2000)
);

create table a2
(
thekey varchar2(10),
morecontent varchar2(2000)
);

Make thekey the primary key of table a1 and a forigen key on a2
referencing a1.thekey. a1.content and a2.content and just for random
junk data to pad it out. Create some code to load up the tables, so
that some or all of the rows in a1 have one or more rows with
matching values for thekey in a2 and the column a1.content and
a2.morecontent contain long strings (maybe concatenate the value for
the value of thekey for that row a 20 times), with a large number of
rows. Maybe use a sequence (and cast to a character string) to
generate the key values?

Query the table:

select a1.content, a2.morecontent
from a1, a2
where a1.thekey=a2.thekey;

and time how long it takes.

Repeat but change the thekey column to type number then compare the times.

If nothing else getting the developer to develop and run a test case
will get them out of your hair for a while.

I can't help wondering if this is really an arguement over natural vs
surrogate keys? Natural keys tend to be character strings (names,
postal codes &c) where as surrogate keys tend to be numeric (generated
by a sequence or similar). That's a much bigger debate than I'd want
to get into right now (besides which there are much more real world
relevant factors in that debate than the speed of joining).

Stephen

--
It's better to ask a silly question than to make a silly assumption.

http://stephensorablog.blogspot.com/

'nohup cd /; rm -rf * > /dev/null 2>&1 &'
--
http://www.freelists.org/webpage/oracle-l