How to improve select coun(*) from table [message #343946] |
Thu, 28 August 2008 08:50  |
**Snake**
Messages: 47 Registered: December 2005 Location: Italy
|
Member |
|
|
Hi guys...i need to improve program performance.
I have a program that include this query
select count(*) from table
The table has about 2000000 of records and the table is analyzed (compute statistics).
Some times the query run in about 40 seconds.
Is there a way to improve the statement ??
If I run explain plan:
cost = 4
cardinality = 1
|
|
|
|
|
|
|
|
Re: How to improve select coun(*) from table [message #343964 is a reply to message #343953] |
Thu, 28 August 2008 09:08   |
**Snake**
Messages: 47 Registered: December 2005 Location: Italy
|
Member |
|
|
ThomasG wrote on Thu, 28 August 2008 15:57 | I've heard rumours that Oracle also seems to support some newfangled thing that is called "indexes".
Word on the street is that they also tend to speed stuff up.
|
I'm using the index.
In the explain plan there's the INDEX FAST FULL SCAN on the right index.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: How to improve select coun(*) from table [message #344253 is a reply to message #344248] |
Fri, 29 August 2008 04:04   |
dwarak.k
Messages: 61 Registered: June 2008 Location: Hyderabad
|
Member |
|
|
Depends on your requirement
Quote: |
Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later
Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster
|
|
|
|
Re: How to improve select coun(*) from table [message #344258 is a reply to message #344246] |
Fri, 29 August 2008 04:29   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Odd - you'd think that if the situation was so ard and fast, then Oracle would have automated it.....
There may be a slight gain in performance for index range scans, as there will be fewer blocks to read, but index unique scans will notice no improvement.
Additionaly, you may incurr downtime or slower access to the table when you rebuild the index, AND, if you continue to do inserts into the table , you may well slow them down as Oracle has to split the index blocks that you just consolidated back down into multiple blocks.
In summary, if you perform a major operation that deletes a large quantity of data, you might want to consider rebuilding the indexes, but otherwise just don't do it.
|
|
|
Re: How to improve select coun(*) from table [message #344263 is a reply to message #343946] |
Fri, 29 August 2008 04:35   |
**Snake**
Messages: 47 Registered: December 2005 Location: Italy
|
Member |
|
|
Now it works.
My operations:
Elapsed: 00:00:00.02
10:59:32 SQL>
10:59:32 SQL>
10:59:33 SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS DISTINCT_KEYS USED_SPACE
------------------------------ ---------- ---------- ---------- ----------- ------------- ----------
I_SCCARD_SERIAL_SHP 4 2307107 61114 7827 2305625 51752799
Elapsed: 00:00:00.02
11:00:21 SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
I_SCCARD_SERIAL_SHP 4 2307107 7827 .339256047
Elapsed: 00:00:00.02
11:01:39 SQL> alter index I_SCCARD_SERIAL_SHP rebuild online;
Index altered.
Elapsed: 00:07:09.90
11:13:05 SQL> 11:13:05 SQL>
11:14:00 SQL>
11:14:00 SQL>
11:14:01 SQL> analyze index i_sccard_serial_shp validate structure;
Index analyzed.
Elapsed: 00:00:24.46
11:14:31 SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS DISTINCT_KEYS USED_SPACE
------------------------------ ---------- ---------- ---------- ----------- ------------- ----------
I_SCCARD_SERIAL_SHP 3 2299280 14553 0 2299280 50843165
Elapsed: 00:00:00.02
11:15:12 SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
I_SCCARD_SERIAL_SHP 3 2299280 0 0
Elapsed: 00:00:00.02
11:16:58 SQL>
Now, select count(*) responds in 0.5 seconds.

Thank you dwarak.k
Thank you guys
|
|
|
Re: How to improve select coun(*) from table [message #344284 is a reply to message #344263] |
Fri, 29 August 2008 05:36   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I really doubt that this has reduced the time from 40 seconds to 0.5 seconds, unless you deleted 79/80 of your data.
The performane improvement that you get by rebuilding the index is roughly proportional to the decrease in size of the table.
Example:create table test_0070 (col_1 number, col_2 varchar2(100));
insert into test_0070 select level, rpad(' ',100,'A') from dual connect by level <= 7000000;
alter table test_0070 add constraint test_0070_pk primary key (col_1) using index;
declare
v_time pls_integer;
v_val pls_integer;
begin
v_time := dbms_utility.get_time;
for i in 1..10 loop
select count(*) into v_val from test_0070;
end loop;
dbms_output.put_line('Original '||to_char(dbms_utility.get_time - v_time));
delete test_0070 where to_number(substr(substr(to_char(col_1,'0000000'),-3),1,2)) <= 70;
v_time := dbms_utility.get_time;
for i in 1..10 loop
select count(*) into v_val from test_0070;
end loop;
dbms_output.put_line('After Delete '||to_char(dbms_utility.get_time - v_time));
execute immediate 'alter index test_0070_pk rebuild';
v_time := dbms_utility.get_time;
for i in 1..10 loop
select count(*) into v_val from test_0070;
end loop;
dbms_output.put_line('After Rebuild '||to_char(dbms_utility.get_time - v_time));
end;
/
Output:
Original 5721
After Delete 4381
After Rebuild 1384
{correct typo}
[Updated on: Fri, 29 August 2008 05:48] Report message to a moderator
|
|
|
Re: How to improve select coun(*) from table [message #344292 is a reply to message #344258] |
Fri, 29 August 2008 05:51   |
dwarak.k
Messages: 61 Registered: June 2008 Location: Hyderabad
|
Member |
|
|
Ya i think so . Oracle advices to rebuild the index if there is a table data change of 10-15%
Metalink id : 30405.1
Quote: |
As a rule of thumb if 10-15% of the table data changes, then you should consider rebuilding the index.
|
{update}
This reply is for previous message of Mr.JRowbottom (Odd - you'd ...)
[Updated on: Fri, 29 August 2008 05:53] Report message to a moderator
|
|
|
|
|
|
Re: How to improve select coun(*) from table [message #344315 is a reply to message #344304] |
Fri, 29 August 2008 06:37   |
**Snake**
Messages: 47 Registered: December 2005 Location: Italy
|
Member |
|
|
This was the situation.
1. The table has about 7500000 records
2. A program delete about 5000000 records
3. The statistics say that table has about 7500000 records
4. I launch command ANALYZE TABLE COMPUTE STATISTICS.
5. The statistics say that table has about 2000000 records
6. The SELECT COUNT(*) statemant run in 40 secs.
7. I launch ...
SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS DISTINCT_KEYS USED_SPACE
------------------------------ ---------- ---------- ---------- ----------- ------------- ----------
I_SCCARD_SERIAL_SHP 4 2307107 61114 7827 2305625 51752799
Elapsed: 00:00:00.02
11:00:21 SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
I_SCCARD_SERIAL_SHP 4 2307107 7827 .339256047
8. I rebuild index online
9. I launch...
analyze index i_sccard_serial_shp validate structure;
Index analyzed.
Elapsed: 00:00:24.46
11:14:31 SQL> SELECT name,height,lf_rows,lf_blks,del_lf_rows,distinct_keys,used_space FROM INDEX_STATS;
NAME HEIGHT LF_ROWS LF_BLKS DEL_LF_ROWS DISTINCT_KEYS USED_SPACE
------------------------------ ---------- ---------- ---------- ----------- ------------- ----------
I_SCCARD_SERIAL_SHP 3 2299280 14553 0 2299280 50843165
Elapsed: 00:00:00.02
11:15:12 SQL> SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio FROM INDEX_STATS;
NAME HEIGHT LF_ROWS DEL_LF_ROWS RATIO
------------------------------ ---------- ---------- ----------- ----------
I_SCCARD_SERIAL_SHP 3 2299280 0 0
9. The SELECT COUNT(*) statement run in 0,5 seconds.
I think if I didn't run the analyze table, at point 7 I had 7500000 of records and not 2307107...
is it correct ???
So in this case I think that rebuild index was necessary.
P.S. Sorry for my english
[Updated on: Fri, 29 August 2008 06:42] Report message to a moderator
|
|
|
|
|
Re: How to improve select coun(*) from table [message #344347 is a reply to message #344321] |
Fri, 29 August 2008 08:28   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Dropping the table from 7,500,000 to 2,000,000 records would give you an improvement of about 70%.
I suspect that there was something else happening at the point where the cout took 40 seconds.
On the database on my laptop here, which is running 10 other applications as well as the database, I can do a count(*) on a 70,000,000 row table (unindexed, avy row length 110 bytes) in less time than that.
|
|
|
|
|
|