RE: Case of the Missing Rows

[Fink, Dan]

Title: Message

I'm thinking it is related to the execution plan. When you compute stats, you don't seem to have a problem. Run the same tests and check the execution plans in autotrace.

-----Original Message-----
From: Weiss, Rick [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 28, 2003 10:09 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Case of the Missing Rows

Dan:

 

Thanks, I think I got through it, here's what I've found (explanations would be greatly appreciated)

 

1) Created table as before from Designer/2000 scripts with indexes and constraints

2) SQLLDR to create initial data list

3) SELECT COUNT(*) FROM TB - yields 88640 rows

4) RENAME TB TO TB_HOLD (takes indexes along)

5) SELECT COUNT(*) FROM TB_HOLD - yields only 87257 rows (ahhh nuts)

6) TRUNCATE TABLE TB_HOLD

7) CREATE TB AS SELECT * FROM TB_HOLD - two empty tables

8) SQLLDR into TB

9) SELECT COUNT(*) FROM TB - yields 88640 rows

10) INSERT INTO TB_HOLD (SELECT * FROM TB) - inserts 88640 rows

11) ANALYZE both tables COMPUTE STATISTICS

12) SELECT COUNT(*) FROM TB - yields 88640 rows

13) SELECT COUNT(*) FROM TB - yields 88640 rows

14) EXP - both tables now export 88640 rows

 

What in the world is going on??

 

Great puzzler for the group to mull over

 

No jobs scheduled except for RMAN level 0 on Friday mornings@ 5:30 and other daily level 1 cumulatives and a weekly full DB export on Fridays @ 2:30

 

Rick Weiss

Oracle DBA

-----Original Message-----
From: Fink, Dan [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 28, 2003 7:45 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Case of the Missing Rows

Rick,

    Try the following to see which rows are missing. It the same rows are missing each time, perhaps there is a common thread. If not, well....

 

    After Step 2, do a create table as select or sql*plus copy. This will create a backup version. Do a count(*) from each to make sure the numbers agree.

    After Step 4, select * from table1 minus select * from backup_copy to locate the missing rows.

 

Dan Fink

-----Original Message-----
From: Weiss, Rick [mailto:[EMAIL PROTECTED]]
Sent: Monday, January 27, 2003 4:54 PM
To: Multiple recipients of list ORACLE-L
Subject: Case of the Missing Rows

I have a recurring, repeatable problem I was wondering about its cause.

Oracle 9.2.0.1 on W2K Professional (SP2) Dell Optiplex workstation Pentium 4

Step 1 - I do an SQLLDR process that loads 88640 rows to a table
Step 2 - SQL*Plus session - SELEC COUNT(*) from the table returns 88640 rows
Step 3 - Do an EXP on the table (to allow fall back to this point) - only exports 87257 rows
Step 4 - SQL*Plus session again - SELEC COUNT(*) from the table returns 87257 rows

No one else has access to the database.  There are no unusual entries in the alert log.
There is nothing I have found in the UDUMP or BDUMP directories that would help.

Has anyone else experienced this??

Thanks

Rick Weiss