Re: Partial Index Usage Performance Question
Date: Tue, 7 Oct 2008 06:15:13 -0700 (PDT)
Message-ID: <c3ca280e-009e-4457-a9f1-5ae9e42a60ba@v30g2000hsa.googlegroups.com>
On Oct 7, 2:14 am, digory <dig..._at_gmx.net> wrote:
> I can't give you the whole definition because of business reasons. But > there are a lot of other columns in T, including a CLOB. Moreover, c > is a DATE column, and my condition for c is actually: > > SELECT * FROM T WHERE a = ? AND b = ? AND c > t0 > > for some constant date t0, which is known to be older than any value > of c in T.
So if t0 is older than any value in the table your query returns no rows, thus it could return MUCH faster than the query which eliminates c as a predicate. You could easily discover the information you ask for with tools already at your disposal:
SQL>
SQL> create table T(
2 a number, 3 b varchar2(20), 4 c date, 5 d varchar2(4), 6 e number
7 );
Table created.
SQL>
SQL> begin
2 for i in 1..1000 loop 3 insert into T 4 values (i, 'Test record '||i, sysdate-i, 'AABD',mod(i, 7)); 5 end loop; 6 7 commit;
8
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> insert into T
2 select * from T;
1000 rows created.
SQL>
SQL> insert into T
2 select * from T;
2000 rows created.
SQL>
SQL> insert into T
2 select * from T;
4000 rows created.
SQL>
SQL> insert into T
2 select * from T;
8000 rows created.
SQL>
SQL> insert into T
2 select * from T;
16000 rows created.
SQL>
SQL> insert into T
2 select * from T;
32000 rows created.
SQL>
SQL> insert into T
2 select * from T;
64000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> create index T_idx
2 on T(a,b,c);
Index created.
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'T',
estimate_percent=>100,cascade=>true);
PL/SQL procedure successfully completed.
SQL> SQL> set autotrace on timing on SQL> SQL> select *
2 from T
3 where a = 200
4 and b = 'Test record 200'
5 /
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
A B C D E
---------- -------------------- --------- ---- ----------
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
200 Test record 200 21-MAR-08 AABD 4
128 rows selected.
Elapsed: 00:00:00.03
Execution Plan
Plan hash value: 1020776977
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 35 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 35 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 3(0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("A"=200 AND "B"='Test record 200')
Statistics
1 recursive calls
0 db block gets
141 consistent gets
0 physical reads
0 redo size
1270 bytes sent via SQL*Net to client
302 bytes received via SQL*Net from client
10 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
128 rows processed
SQL>
SQL>
SQL> select *
2 from T
3 where a = 200
4 and b = 'Test record 200'
5 and c > sysdate+10 -- no record in T exists with this date 6 /
no rows selected
Elapsed: 00:00:00.00
Execution Plan
Plan hash value: 1020776977
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 35 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 35 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T_IDX | 1 | | 3(0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - access("A"=200 AND "B"='Test record 200' AND "C">SYSDATE@!+10
AND "C"
IS NOT NULL)
Statistics
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
258 bytes sent via SQL*Net to client
239 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> I'd suggest you try the same with your system and generate actual results based upon your data and database configuration, rather than relying upon simplisic examples against test databases which likely won't reflect how your system responds.
David Fitzjarrell Received on Tue Oct 07 2008 - 08:15:13 CDT