Printable View
Hi, My table 'A' has 11 million rows . I created temp table 'B' same as table A and inserted rows upto 4 million from table 'A' using where caluse.
The data was inserted fine without any errors, But when Iam trying to do a row count on table 'b'.
selct count(*) from b.
But it is not giving me any output. It has been more than 30 min but still there is no output. Table A rowcount gives me in few seconds. Can any one let me know what the problem could be??
if you count from other session and you have not issue commit then for your counting session there is no rows
if your table b has NO primary key it will use Full Table Scan which is slower if you are counting the whole table
I did give the commit after inserting. I am logged into the same user in which I created the table, but still I am not able to get the row count. THe primary keys are disabled for table A and table B.
Table B do not have any indexes or any constraints.
I created table B as ..
Create table B (col1 number, col2 varchar(10)........ 15 columns)
TABLESPACE TS012 PCTUSED 75
PCTFREE 20
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 2000048k
NEXT 40000K
MINEXTENTS 3
MAXEXTENTS 1024
PCTINCREASE 0
FREELISTS 8
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
LOGGING
NOCACHE
NOPARALLEL;
if you dont have PK then obviously it is going to take much longer!
THe table A does not have primary key which has 11 million rows but can get the row count in seconds, but the table B which has 4 million rows do not give the row count.
Thanks
reading index is faster than table, why you dont try to use autotrace and see why
If u don't have primary key then I would suggest you to analyze the table then do count(*), it would start using FFS instead of FTS.
HTH
Do u know wat is FFS? and when will it be used ?Quote:
Originally posted by bhallar
If u don't have primary key then I would suggest you to analyze the table then do count(*), it would start using FFS instead of FTS.
HTH
he probably means IFFS