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

SV: How to get query to use an index

Michael Garfield Sørensen

2006-04-13

Replies:

I would suggest a modified UNION-ALL version (a solution I have myself used
on occasion (3VL may - as always - bite you though)):

---
SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product

PL/SQL Release 10.2.0.1.0 - Production

CORE  10.2.0.1.0  Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production


SQL>
SQL> drop table mgsx;
drop table mgsx
      *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table mgsx(c1 varchar2(10),c2 varchar2(10));

Table created.

SQL> insert into mgsx values('x','y');

1 row created.

SQL> insert into mgsx values('x','x');

1 row created.

SQL> insert into mgsx values('y','x');

1 row created.

SQL>
SQL> REM Additional insert as suggested by Gints
SQL> insert into mgsx values('x','x');

1 row created.

SQL>
SQL> REM OR-version
SQL> select * from mgsx where c1 like 'x%' or c2 like 'x%';

C1      C2

---------- ----------

x       y

x       x

y       x

x       x


SQL>
SQL> REM UNION-ALL-version
SQL> select * from mgsx where c1 like 'x%'
2 union all
3 select * from mgsx where c2 like 'x%';

C1      C2

---------- ----------

x       y

x       x

x       x

x       x

y       x

x       x


6 rows selected.

SQL>
SQL> REM UNION-version
SQL> select * from mgsx where c1 like 'x%'
2 union
3 select * from mgsx where c2 like 'x%';

C1      C2

---------- ----------

x       x

x       y

y       x


SQL>
SQL> REM Modified UNION-ALL-version
SQL> select * from mgsx where c1 like 'x%'
2 union all
3 select * from mgsx
4  where c2 like 'x%'
5  and c1 not like 'x%';

C1      C2

---------- ----------

x       y

x       x

x       x

y       x


SQL>
SQL>
SQL> REM To the best of my knowledge, the
SQL> REM modified UNION-ALL-version is
SQL> REM equivalent to the OR-version if
SQL> REM (and only if) you rule out NULLs
SQL> REM (Yes, Lex, you're not forgotten)
SQL> REM i.e.
SQL> alter table mgsx modify (c1 not null, c2 not null);

Table altered.

SQL>
SQL> spool off;
---

Regards,
Michael Garfield S?rensen, CeDeT






-----Oprindelig meddelelse-----
Fra: oracle-l-bounce@(protected)?
vegne af Gints Plivna
Sendt: 13. april 2006 14:17
Til: tim@(protected)
Cc: oracle-l
Emne: Re: How to get query to use an index

Yea and BTW there are cases (though in normal applications rare) when
you have to use OR, because neither UNION nor UNION ALL works.
You can easily take the example provided by Michael and simply insert row
insert into mgsx values('x','x'); two times - so you got different
results for each of three variations. So you simply have to be careful
and know your data and know what you really need as output.

SQL> select * from mgsx where c1 like 'x%' or c2 like 'x%';

C1      C2
---------- ----------
x       y
y       x
x       x
x       x

4 rows selected.

SQL> select * from mgsx where c1 like 'x%'
2   union all
3  select * from mgsx where c2 like 'x%'
4 /

C1      C2
---------- ----------
x       y
x       x
x       x
y       x
x       x
x       x

6 rows selected.

SQL> select * from mgsx where c1 like 'x%'
2   union
3  select * from mgsx where c2 like 'x%'
4 /

C1      C2
---------- ----------
x       x
x       y
y       x

3 rows selected.

Gints

2006/4/13, Gints Plivna <gints.plivna@(protected)>:
> Trying to remeber something from set theory ....
>
> Let's imagine you get set A from the first satement in UNION and set B
> from the second statement in UNION. If intersection of A and B is
> empty set then it makes no difference either to use UNION or UNION ALL
> (except that oracle anyway performs sort unique in case of UNION). But
> if intersection of A and B is not empty set as in example where row
> with both c1 and c2 = 'x', then it is important because ORed
> expression gives back only one instance of this particular row but
> UNION ALL gives us two.
> So if you are sure that always will be only one true either c1 = 'x'
> or c2='x' then you can use UNION ALL and it should perform better
> because of lack of sort unique.
> But if you aren't sure and there may be cases when both c1 = 'x' and
> c2 = 'x' then you have to use just UNION.
>
> Gints
>
> 2006/4/12, Tim Gorman <tim@(protected)>:
> > Michael,
> >
> > Very interesting! Expanding your test to include just using plain UNION
> > operator (which performs a DISTINCT), the results become correct.
> >
> > So, I had always thought UNION-ALL was equivalent to an OR'd expression;
is
> > it really UNION?
> >
> > Thanks!!!
> >
> > -Tim
> >
>
--
http://www.freelists.org/webpage/oracle-l





--
http://www.freelists.org/webpage/oracle-l