Re:Default Primary Key Indexes not created...

What apparently happened is that the developers applied unique indexes in their DDL before altering the table to create primary key constraints. Since you can't index a column more than once (at least in this case), the primary key index is not created. If you try
create table x(a NUMER); and then create unique index xidx on x (a); and then alter table x add constraint xpk primary key (a) - and then look at user_segments, there will be no segment for the primary key index, but if you do the primary key first, you will, and they you will get a message back from Oracle saying you can't double index the column. What performance problems can this cause?

Kevin A Lewis wrote:

> Although there is no data in the dependent tables Oracle still has to check
> for content dependent on the parent. I can;t comment on the missing primary
> index segment though
>
> Regards
>
> --
> Kevin A Lewis (BOCM PAULS LTD - Animal Feed Manufacturer - Ipswich England)
> <KevinALewis_at_HotMail.com>
>
> The views expressed herein by the author of this document
> are not necessarily those of BOCM PAULS Ltd.
> Doug Cowles <dcowles_at_bigfoot.com> wrote in message
> news:37670260.EF9B18E0_at_bigfoot.com...
> > Actually, it appears that it was something much stranger. I noticed that
> even
> > on very small
> > deletes, there was an enormous increase in v$filestat on many different
> files,
> > and I tracked it down to tables with foreign keys on the table. Even
> though the
> > tables were empty, massive I/O was occuring. I then noticed that the
> segment
> > for the automatically created primary key index was missing. I re-created
> it,
> > and the delete of the entire table was about 5 seconds or less. Does
> anyone
> > have any experience or knowledge of A) How someone or something could have
> > gotten rid of the primary key default index and B) What sort of strange
> trips
> > Oracle would go on if it was gone?
> >
> > Thanks,
> > Dc.
> >
> > Ulrik Hoffmann wrote:
> >
> > > Hi Doug,
> > >
> > > did you check the ORCLALRT.LOG ? Are there
> > > any 'CHECKPOINT NOT COMPLETES' or
> > > Errors?
> > > Maybe it has to do with the redo-log-files.
> > > Bye
> > > Uli
> > >
> > > Doug Cowles <dcowles_at_bigfoot.com> schrieb in im Newsbeitrag:
> > > 3766A565.88201DCA_at_bigfoot.com...
> > > > Hi, I have a table with about 14,000 rows in it, and after watching a
> > > > delete on the table
> > > > hang, and hang, and hange again, I bounced the database, (after
> checking
> > > > for blockers)
> > > > and saw no improvement. I was the only one on the system, and began
> > > > deleting 10 or
> > > > 20 rows at a time, each time this would take about 30 seconds. It is
> a
> > > > dimension table,
> > > > there are many tables with foreign keys aimed at this table, but they
> > > > are all empty. I
> > > > did an alter table deallocate unused on all the tables. - I flush the
> > > > shared pool - I looked
> > > > at my BSTAT ESTAT and noticed the buffer cache could be much bigger,
> but
> > > > I don't think that's enough to explain this. No locks, no nothing. I
> > > > have some tkprof output of
> > > > the actual delete of 20-30 rows itself, and it looks normal except for
> > > > the recursive stuff.
> > > > Despite the CPU stats reporting relatively low, and the range scan
> using
> > > > the primary key index, this query took 30 seconds to run. Any
> > > > thoughts? Here's the tkprof output.....
> > > >
> > > >
> > > > TKPROF: Release 8.0.5.0.0 - Production on Tue Jun 15 14:24:42 1999
> > > >
> > > > (c) Copyright 1998 Oracle Corporation. All rights reserved.
> > > >
> > > > Trace file: delete.trc
> > > > Sort options: default
> > > >
> > > >
> > >
> ****************************************************************************
> > > ****
> > > >
> > > > count = number of times OCI procedure was executed
> > > > cpu = cpu time in seconds executing
> > > > elapsed = elapsed time in seconds executing
> > > > disk = number of physical reads of buffers from disk
> > > > query = number of buffers gotten for consistent read
> > > > current = number of buffers gotten in current mode (usually for
> update)
> > > >
> > > > rows = number of rows processed by the fetch or execute call
> > > >
> > >
> ****************************************************************************
> > > ****
> > > >
> > > > alter session set sql_trace = TRUE
> > > >
> > > >
> > > > call count cpu elapsed disk query
> > > > current rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 0 0.00 0.00 0 0
> > > > 0 0
> > > > Execute 1 0.00 0.05 0 0
> > > > 0 0
> > > > Fetch 0 0.00 0.00 0 0
> > > > 0 0
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 1 0.00 0.05 0 0
> > > > 0 0
> > > >
> > > > Misses in library cache during parse: 0
> > > > Misses in library cache during execute: 1
> > > > Optimizer goal: CHOOSE
> > > > Parsing user id: 25 (MMS)
> > > >
> > >
> ****************************************************************************
> > > ****
> > > >
> > > > delete from geography_dim where geography_dim_key < 3900
> > > > and geography_dim_key > 3980
> > > >
> > > > call count cpu elapsed disk query
> > > > current rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 1 0.00 0.00 0 0
> > > > 0 0
> > > > Execute 1 0.01 0.01 0 300
> > > > 0 0
> > > > Fetch 0 0.00 0.00 0 0
> > > > 0 0
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 2 0.01 0.01 0 300
> > > > 0 0
> > > >
> > > > Misses in library cache during parse: 1
> > > > Optimizer goal: CHOOSE
> > > > Parsing user id: 25 (MMS)
> > > >
> > > > Rows Execution Plan
> > > > ------- ---------------------------------------------------
> > > > 0 DELETE STATEMENT GOAL: CHOOSE
> > > > 0 DELETE OF 'GEOGRAPHY_DIM'
> > > > 1 INDEX (RANGE SCAN) OF 'XPKGEOGRAPHY_DIM' (UNIQUE)
> > > >
> > > >
> > >
> ****************************************************************************
> > > ****
> > > >
> > > > delete from geography_dim where geography_dim_key < 4000
> > > > and geography_dim_key > 3980
> > > >
> > > > call count cpu elapsed disk query
> > > > current rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 1 0.01 0.01 0 0
> > > > 0 0
> > > > Execute 1 0.07 0.06 1 2
> > > > 59 19
> > > > Fetch 0 0.00 0.00 0 0
> > > > 0 0
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 2 0.08 0.07 1 2
> > > > 59 19
> > > >
> > > > Misses in library cache during parse: 1
> > > > Optimizer goal: CHOOSE
> > > > Parsing user id: 25 (MMS)
> > > >
> > > > Rows Execution Plan
> > > > ------- ---------------------------------------------------
> > > > 0 DELETE STATEMENT GOAL: CHOOSE
> > > > 0 DELETE OF 'GEOGRAPHY_DIM'
> > > > 20 INDEX (RANGE SCAN) OF 'XPKGEOGRAPHY_DIM' (UNIQUE)
> > > >
> > > >
> > >
> ****************************************************************************
> > > ****
> > > >
> > > > commit
> > > >
> > > >
> > > > call count cpu elapsed disk query
> > > > current rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 1 0.00 0.00 0 0
> > > > 0 0
> > > > Execute 1 0.00 0.00 0 0
> > > > 1 0
> > > > Fetch 0 0.00 0.00 0 0
> > > > 0 0
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 2 0.00 0.00 0 0
> > > > 1 0
> > > >
> > > > Misses in library cache during parse: 1
> > > > Optimizer goal: CHOOSE
> > > > Parsing user id: 25 (MMS)
> > > >
> > >
> ****************************************************************************
> > > ****
> > > >
> > > > alter session set sql_Trace = false
> > > >
> > > >
> > > > call count cpu elapsed disk query
> > > > current rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 1 0.00 0.00 0 0
> > > > 0 0
> > > > Execute 1 0.00 0.00 0 0
> > > > 0 0
> > > > Fetch 0 0.00 0.00 0 0
> > > > 0 0
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 2 0.00 0.00 0 0
> > > > 0 0
> > > >
> > > > Misses in library cache during parse: 1
> > > > Optimizer goal: CHOOSE
> > > > Parsing user id: 25 (MMS)
> > > >
> > > >
> > > >
> > > >
> > >
> ****************************************************************************
> > > ****
> > > >
> > > > OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
> > > >
> > > > call count cpu elapsed disk query
> > > > current rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 4 0.01 0.01 0 0
> > > > 0 0
> > > > Execute 5 0.08 0.12 1 302
> > > > 60 19
> > > > Fetch 0 0.00 0.00 0 0
> > > > 0 0
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 9 0.09 0.13 1 302
> > > > 60 19
> > > >
> > > > Misses in library cache during parse: 4
> > > > Misses in library cache during execute: 1
> > > >
> > > >
> > > > OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
> > > >
> > > > call count cpu elapsed disk query
> > > > current rows
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > Parse 5 0.00 0.00 0 0
> > > > 0 0
> > > > Execute 95 0.01 0.02 0 0
> > > > 0 0
> > > > Fetch 95 30.47 30.48 126576 154337
> > > > 228 95
> > > > ------- ------ -------- ---------- ---------- ---------- ----------
> > > > ----------
> > > > total 195 30.48 30.50 126576 154337
> > > > 228 95
> > > >
> > > > Misses in library cache during parse: 0
> > > >
> > > > 5 user SQL statements in session.
> > > > 5 internal SQL statements in session.
> > > > 10 SQL statements in session.
> > > > 2 statements EXPLAINed in this session.
> > > >
> > >
> ****************************************************************************
> > > ****
> > > >
> > > > Trace file: delete.trc
> > > > Trace file compatibility: 7.03.02
> > > > Sort options: default
> > > >
> > > > 1 session in tracefile.
> > > > 5 user SQL statements in trace file.
> > > > 5 internal SQL statements in trace file.
> > > > 10 SQL statements in trace file.
> > > > 10 unique SQL statements in trace file.
> > > > 2 SQL statements EXPLAINed using schema:
> > > > MMS.prof$plan_table
> > > > Default table was used.
> > > > Table was created.
> > > > Table was dropped.
> > > > 280 lines in trace file.
> > > >
> >
> >
> >
Received on Wed Jun 16 1999 - 10:40:01 CDT