Home » SQL & PL/SQL » SQL & PL/SQL » select - various count (Oracle 10)
select - various count [message #407947] Fri, 12 June 2009 08:44 Go to next message
zbittner
Messages: 5
Registered: June 2009
Junior Member
Hello,
strange behaviour:
table ttt (c1,c2,23) with primary key (c1,c2)

select count(*) from ttt .... returns 10000 rows
select c1,c2 from ttt .... returns 10000 rows
select * from ttt .... returns 14000 rows
select c1,c2,c3 from ttt .... returns 14000 rows

Can anyone explain it to me?

Regards
zdenek
Re: select - various count [message #407949 is a reply to message #407947] Fri, 12 June 2009 09:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Not that we don't believe you, but can you run those 4 queries in SQL*Plus and cut and paste the results back here.

There's something else going on here.
Re: select - various count [message #407950 is a reply to message #407949] Fri, 12 June 2009 09:35 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:
Not that we don't believe you
I read it as Note that...../fa/1987/0/

By
Vamsi
Re: select - various count [message #407953 is a reply to message #407950] Fri, 12 June 2009 10:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

select count(*) from ttt .... returns 10000 rows



That can't be. That can only return ONE row. (with MAYBE a value of 10000)

Re: select - various count [message #408741 is a reply to message #407949] Wed, 17 June 2009 08:32 Go to previous messageGo to next message
zbittner
Messages: 5
Registered: June 2009
Junior Member
Pasted and copied examples of SQLPlus commands:
Command 1:
SQL> select count(*) from sapr3.T156T;

COUNT(*)
----------
10901


Command 2:
select MANDT,SPRAS,BWART,SOBKZ,KZBEW,KZZUG,KZVBR from sapr3.T156T;
MANDT SPR BWART SOB KZB KZZ KZV
--------- --- --------- --- --- --- ---
001 C XP2 E X E
001 C XP2 K
001 C XP2 K X
001 C XP2 M
001 C XP2 M X
001 C XP2 Q
001 C XP2 Q P
001 C XP2 Q X
001 C XP2 Q X P
001 C Z01
001 C Z02
......lots rows of data......

10901 rows selected.



Command 3:
SQL> select * from sapr3.T156T;
MANDT SPR BWART SOB KZB KZZ KZV
--------- --- --------- --- --- --- ---
BTEXT
------------------------------------------------------------
001 E XB4 Q
TR project to own

001 E XB4 Q P
TR project to own
...........
..........lots rows with data
..........
..........

14687 rows selected.



Re: select - various count [message #408743 is a reply to message #407947] Wed, 17 June 2009 08:46 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've either got a serious bug or there's something else going on you haven't told us about.
Can you post the following:
Version of oracle are you using.
The exact definition of T156T.
Explain plans for each query.
Re: select - various count [message #408962 is a reply to message #407947] Thu, 18 June 2009 08:12 Go to previous messageGo to next message
zbittner
Messages: 5
Registered: June 2009
Junior Member
Explain plan and table structure are:

****************************************************************************************
select * from sapr3.T156T; ........ 14687 rows
****************************************************************************************
EXPLAIN PLAN FOR
2 select * from sapr3.T156T;
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 14177 | 512K| 25 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T156T | 14177 | 512K| 25 (0)| 00:00:01 |
------------------------------------------------------------



************************************************************************************************
select MANDT,SPRAS,BWART,SOBKZ,KZBEW,KZZUG,KZVBR from sapr3.T156T; .... 10901rows
************************************************************************************************
explain plan for
2 select MANDT,SPRAS,BWART,SOBKZ,KZBEW,KZZUG,KZVBR from sapr3.T156T;
--------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14177 | 249K| 16 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| T156T~0 | 14177 | 249K| 16 (0)| 00:00:01 |
---------------------------------------------------------------



***********************************************************************************************************
select MANDT,SPRAS,BWART,SOBKZ,KZBEW,KZZUG,KZVBR,BTEXT from sapr3.T156T; ..... 14687 rows
***********************************************************************************************************
---------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 14177 | 512K| 25 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T156T | 14177 | 512K| 25 (0)| 00:00:01 |
----------------------------------------------------------



*************************************************************************************************************
select count(*) from sapr3.T156T; .... returns value 10901
*************************************************************************************************************
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | INDEX FAST FULL SCAN| T156T~0 | 14177 | 16 (0)| 00:00:01 |
----------------------------------------------------------


**************************************************************************************************************
Description of table and index
*************************************************************************************************************
CREATE TABLE "SAPR3"."T156T"
( "MANDT" VARCHAR2(9) DEFAULT '000' NOT NULL ENABLE,
"SPRAS" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
"BWART" VARCHAR2(9) DEFAULT ' ' NOT NULL ENABLE,
"SOBKZ" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
"KZBEW" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
"KZZUG" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
"KZVBR" VARCHAR2(3) DEFAULT ' ' NOT NULL ENABLE,
"BTEXT" VARCHAR2(60) DEFAULT ' ' NOT NULL ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 229376 NEXT 1048576 MINEXTENTS 1 MAXEX
TENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEF
AULT)
TABLESPACE "PSAPSR3"

CREATE UNIQUE INDEX "SAPR3"."T156T~0" ON "SAPR3"."T156T" ("MANDT", "SPRAS",
"BWART", "SOBKZ", "KZBEW", "KZZUG", "KZVBR")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 278528 NEXT 1048576 MINEXTENTS 1 M
AXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT)
TABLESPACE "PSAPSR3"

Re: select - various count [message #408978 is a reply to message #407947] Thu, 18 June 2009 10:07 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
You forgot to post your oracle version.

Looks like you might have a corrupted index.

Have a look at metalink note 457021.1
icon8.gif  Re: select - various count [message #409061 is a reply to message #407947] Fri, 19 June 2009 02:23 Go to previous messageGo to next message
zbittner
Messages: 5
Registered: June 2009
Junior Member
Sorry - Oracle version is 10.2.0.4.

The note in metaling says: "if an index is corrupted, rebuild it - alter index name rebuild online".

But I cannot rebuild this index, because in the table there are DUPLICATE ROWS (even though there exist UNIQUE index on the table, so these duplicities should not be inserted - dont ask me, how they has been inserted).

So the questions are:
1. how could the duplicities been inserted, when there an unique index exists?
2. how can we repair it?

thanks a lot

Zdenek
Re: select - various count [message #409065 is a reply to message #409061] Fri, 19 June 2009 02:44 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
how could the duplicities been inserted, when there an unique index exists?

Can you prove that? Please, post SQL*Plus session which displays columns that are used in UNIQUE INDEX - a few duplicate records will be enough.

Just a question: do those "duplicates" have something to do with NULL values in "unique" columns? Because, nulls don't count when uniqueness is in question. Something like this:
SQL> create table test
  2  (id number,
  3   name varchar2(20)
  4  );

Table created.

SQL> create unique index iun_test on test (id, name);

Index created.

SQL> insert into test (id, name) values (1, 'Little');

1 row created.

SQL> insert into test (id, name) values (1, null);

1 row created.

SQL> insert into test (id, name) values (1, null);
insert into test (id, name) values (1, null)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IUN_TEST) violated


SQL> insert into test (id, name) values (2, null);

1 row created.

SQL> select * from test order by id;

        ID NAME
---------- --------------------
         1 Little
         1                
         2

SQL>
Re: select - various count [message #409071 is a reply to message #409065] Fri, 19 June 2009 02:54 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Disabling constraints and enabling them with the NOVALIDATE option can do that.
Used often for "lets-do-this-fix-quickly-I-am-sure-its-ok" kind of fixes.
See here for an example
Re: select - various count [message #409074 is a reply to message #407947] Fri, 19 June 2009 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you've got duplicate data in your table that shouldn't be there then you've going to have to delete it.
Re: select - various count [message #409078 is a reply to message #409065] Fri, 19 June 2009 04:20 Go to previous messageGo to next message
zbittner
Messages: 5
Registered: June 2009
Junior Member
Hello,
the NULL values are disabled in index-fields.

There is an example of duplicities:
select * from sapr3.T156T a 
where a.MANDT='001' 
AND a.BWART='551' 
AND  a.rowid > ANY (select b.ROWID from sapr3.T156T b 
                    where a.MANDT=b.MANDT 
                    AND a.SPRAS=b.SPRAS 
                    AND a.BWART=b.BWART 
                    AND a.SOBKZ=b.SOBKZ 
                    AND a.KZBEW=b.KZBEW 
                    AND a.KZZUG=b.KZZUG 
                    AND a.KZVBR=b.KZVBR 
                    AND a.BTEXT=b.BTEXT
                   );


MANDT     SPR BWART     SOB KZB KZZ KZV
--------- --- --------- --- --- --- ---
BTEXT
------------------------------------------------------------
001       C   551       Q
VM sezarotov. proj.

001       C   551       Q
VM sezarotov. proj.

001       C   551       Q
VM sezarotov. proj.

001       C   551       Q
VM sezarotov. proj.

001       C   551       Q
VM sezarotov. proj.

I tried to run analyse table - following results:
analyze table sapr3.T156T validate structure;
  Table analyzed.
analyze table sapr3.T156T validate structure cascade;
  ORA-01499: table/index cross reference failure - see trace file


[EDITED by LF: slightly formatted and applied [code] tags]

[Updated on: Fri, 19 June 2009 04:38] by Moderator

Report message to a moderator

Re: select - various count [message #409088 is a reply to message #409078] Fri, 19 June 2009 05:21 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That looks pretty conclusive - your index is stuffed.

I think you need to find out which you need to keep - the Unique constraint on the index, or the duplicate data.

Once you've decided which is important, delete any duplicate data (if you want an unique index) and then drop and recreate the index.
Previous Topic: partition by
Next Topic: Help with parsing a full name field
Goto Forum:
  


Current Time: Sun Dec 04 23:11:15 CST 2016

Total time taken to generate the page: 0.06568 seconds