-
How to count # of records in a table
Is there a better way to count # of records in a table than doing count(*)?
Sometimes it takes forever and consumes lots of resources to count records in a huge table, say with more than 200+ millions records.
-
nop, no better way to get an exact number of records
-
How do you access to this huge table? do you have no PK for it?
-
I did the following to find primary key on the table and got nothing back.
select * from user_constraints where constraint_type='P' and table_name='GL_BALANCES';
The table is analyzed once a week.
So I could do
SELECT num_rows FROM user_tables where table_name=’GL.GL_BALANCES’;
It comes back in a a few seconds. While select count(*) runs for a long time, before I kiiled it.
-
Originally posted by pcotten
So I could do
SELECT num_rows FROM user_tables where table_name=’GL.GL_BALANCES’;
It comes back in a a few seconds. While select count(*) runs for a long time, before I kiiled it.
That will giv you nearly correct results but not accurate .. as Pando said count(*) is the accurate way to get num of recs.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
If an estimate is good enough, then use the sample clause ...
Code:
Select
Count(*)*100
From
GL.GL_BALANCES Sample (1);
-
So is it correct that you have a table with 200+ millions rows but no index on it? If so I'm just wondernig how you query this big table? Are you doing fts each time you query it?
-
Originally posted by mike9
So is it correct that you have a table with 200+ millions rows but no index on it? If so I'm just wondernig how you query this big table? Are you doing fts each time you query it?
The table is 400 million+ rows in size. I don't query it. The developers and online batch jobs do. We do have indexes on it.
SQL> select index_name from dba_indexes where table_name='GL_BALANCES';
INDEX_NAME
------------------------------
GL_BALANCES_N4
GL_BALANCES_N3
GL_BALANCES_N1
GL_BALANCES_N2
SQL> desc gl_balances
Name Null? Type
----------------------------------------- -------- ----------------------------
SET_OF_BOOKS_ID NOT NULL NUMBER(15)
CODE_COMBINATION_ID NOT NULL NUMBER(15)
CURRENCY_CODE NOT NULL VARCHAR2(15)
PERIOD_NAME NOT NULL VARCHAR2(15)
ACTUAL_FLAG NOT NULL VARCHAR2(1)
LAST_UPDATE_DATE NOT NULL DATE
LAST_UPDATED_BY NOT NULL NUMBER(15)
BUDGET_VERSION_ID NUMBER(15)
ENCUMBRANCE_TYPE_ID NUMBER(15)
TRANSLATED_FLAG VARCHAR2(1)
REVALUATION_STATUS VARCHAR2(1)
PERIOD_TYPE VARCHAR2(15)
PERIOD_YEAR NUMBER(15)
PERIOD_NUM NUMBER(15)
PERIOD_NET_DR NUMBER
PERIOD_NET_CR NUMBER
PERIOD_TO_DATE_ADB NUMBER
QUARTER_TO_DATE_DR NUMBER
QUARTER_TO_DATE_CR NUMBER
QUARTER_TO_DATE_ADB NUMBER
YEAR_TO_DATE_ADB NUMBER
PROJECT_TO_DATE_DR NUMBER
PROJECT_TO_DATE_CR NUMBER
PROJECT_TO_DATE_ADB NUMBER
BEGIN_BALANCE_DR NUMBER
BEGIN_BALANCE_CR NUMBER
PERIOD_NET_DR_BEQ NUMBER
PERIOD_NET_CR_BEQ NUMBER
BEGIN_BALANCE_DR_BEQ NUMBER
BEGIN_BALANCE_CR_BEQ NUMBER
TEMPLATE_ID NUMBER(15)
ENCUMBRANCE_DOC_ID NUMBER(15)
ENCUMBRANCE_LINE_NUM NUMBER(15)
Can I get a count by doing something like this:
select count(CODE_COMBINATION_ID) from gl_balances;
I don't want the developers doing select count(*) from gl_balances as that query ends up taking long time and is typically the most resource intensive SQL run. That is why I am looking for a better way to get count of records in the table.
-
Originally posted by pcotten
I don't want the developers doing select count(*) from gl_balances as that query ends up taking long time and is typically the most resource intensive SQL run.
To the matter of fact, Count(*) is the most efficient way than any thing esle .. coz if u have PK/Unique Key Indexes/Indexes On NOT NULL columns, Oracle use Index_FFS of the Index which has least blocks & I cant think of any other operation much efficient that this ..
Any ways jus check the Plan
Last edited by abhaysk; 03-29-2004 at 11:09 AM.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
...I heard count(1) was faster...
Jeff Hunter
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|