| To verify if a row exists (index range scan) [message #527551] |
Tue, 18 October 2011 14:14  |
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
|
Senior Member |
|
|
In the link below
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1156159920245
Thomas kyte has said,
use the CBO and
select /*+ FIRST_ROWS */ primary_key from table where rownum = 1;
it'll read the index and stop at the first row. very fast on a big empty table (as the index is
small and empty).
very fast on a big full table as the index is just read to find the first leaf node and then "stop".
It gives faster result if the primary key is used. But what if we have a table with around 1000 million rows and for the predicates there is a index range scan on the table.
What if we have a table say big_table (10000000000 rows) and the sql is something like
select /*+ first_rows */ 1 /* id, attribute_id*/
from big_table
where attribute_name ='Gross Premium'
and value ='10000'
and version_date is null
and rownum=1; --it's taking around 3 min
We observed that in such case there will be a range index scan for the index on the predicates. For a particular id there may be different values for attribute 'Gross Premium' and may have multiple versions.
How I would tune such a query where the purpose is to check if at least 1 records exists in the table for the input?
Regards
Ved
[Updated on: Tue, 18 October 2011 14:18] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: To verify if a row exists (index range scan) [message #527703 is a reply to message #527551] |
Wed, 19 October 2011 17:41  |
 |
LNossov
Messages: 318
Registered: July 2011
Location: Germany
|
Senior Member |
|
|
First of all you should not use predicate rownum = 1 because of possible Oracle bugs (wrong results). It is better to use rownum <= 1.
Secondly, the empty leaf blocks of a primary key have to be also checked and it can be not so fast in some cases (s. the example below).
SQL> set echo on
SQL> set linesize 1000
SQL>
SQL> drop table ppp;
drop table ppp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>
SQL> create table ppp(a number not null, b number not null, c number not null,
d number not null, e number not null, f number);
Table created.
SQL>
SQL>
SQL> alter table ppp add constraint ppp_pk primary key (a,b,c,d,e) ;
Table altered.
SQL>
SQL>
SQL> begin
2 for i in 1..100000 loop
3 insert into ppp values(i,i+1,i+2,i+3,i+4,1);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.gather_table_stats (user, 'PPP');
PL/SQL procedure successfully completed.
SQL>
SQL> delete from ppp where a != 100000;
99999 rows deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select leaf_blocks from user_indexes where index_name='PPP_PK';
LEAF_BLOCKS
-----------
432
SQL>
SQL> alter session set statistics_level=all;
Session altered.
SQL>
SQL> select 1 from ppp where rownum <=1;
1
----------
1
SQL>
SQL> select * from table(sys.dbms_xplan.display_cursor('','','ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------
SQL_ID 4jgb9tfw12w6b, child number 0
-------------------------------------
select 1 from ppp where rownum <=1
Plan hash value: 2853731848
------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------
|* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:00.01 | 454 |
| 2 | INDEX FAST FULL SCAN| PPP_PK | 1 | 1 | 1 |00:00:00.01 | 454 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
18 rows selected.
SQL>
SQL> drop table ppp;
Table dropped.
Did you create index on (attribute_name,value,version_date)? Could you please send the output of sys.dbms_xplan.display_cursor like in my example.
[Updated on: Thu, 20 October 2011 01:18] by Moderator Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
