Re: What this is?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 13 Nov 2004 08:15:54 -0000
When a statement fails because due to a constraint
problem, the error messages reports the name
and owner of the constraint. Since these are
a waste of space in the dictionary cache (and should
never be needed ;) they have to be looked up
every time.
Typical cause is code like:
loop
insert into table value (......)
exception
when duplicate key then
update table where ...
end loop
As a general rule, updating and finding
no rows is a lot cheaper than inserting
and failing unless the number of duplicates
is known to be a very small fraction of the
data.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th
: Subject: What this is?
: Date: Fri, 12 Nov 2004 14:46:03 -0600
: From: "Stephen Lee" <Stephen.Lee@xxxxxxxx>
:
: select c.name, u.name
: from con$ c, cdef$ cd, user$ u=20
: where c.con# =3D cd.con# and cd.enabled =3D :1 and c.owner# =3D u.user#;
:
: After some concern about a database running slower than desired and
: slower than normal, the statement that shows up as being the biggest for
: the number executions and buffer gets (but NOT buffer gets per
: execution) is above. I know that a large table is getting data
: inserted, and there are two unique constraints, one primary key, three
: foreign keys, and a few not null constraints on the table. The table
: has 321 million rows, and sum(bytes) from dba_segments comes back with
: 44,669,337,600.
:
--
//www.freelists.org/webpage/oracle-l
Other related posts:
