Hi

I have a table T with columns A, B, C and an index I over all three
columns. Further, I have a query which uses columns A and B, but not
C, i.e.

SELECT * FROM T WHERE a = ? AND b = ?

I gather Oracle is able to use index I even though the last column is
not used. My question is the following: If I write a query like

SELECT * FROM T WHERE a = ? AND b = ? AND c = ?

with a condition for C that, for the data in T, is known to be true
always, will that query be generally slower or faster than the one
above, or is there no difference in performance, or does it depend on
the complexity of the index?

Thanks for a little insight,
Peter

On Oct 6, 10:14*am, digory <dig... (AT) gmx (DOT) net> wrote:
That depends upon the structure of T. If there are more columns in T
than you have indexed then, most likely, the queries will run in
approximately the same time. If, however, there are only the three
columns in T then the second query could run faster as it will scan
the index for the results and not touch the table at all.

You haven't provided enough information to answer your question.
Provide the DDL for table T, and some sample data, and you'll get a
better response, and possibly even an actual answer.


David Fitzjarrell

On Oct 6, 10:14*am, digory <dig... (AT) gmx (DOT) net> wrote:
That depends upon the structure of T. If there are more columns in T
than you have indexed then, most likely, the queries will run in
approximately the same time. If, however, there are only the three
columns in T then the second query could run faster as it will scan
the index for the results and not touch the table at all.

You haven't provided enough information to answer your question.
Provide the DDL for table T, and some sample data, and you'll get a
better response, and possibly even an actual answer.


David Fitzjarrell

On Oct 6, 10:14*am, digory <dig... (AT) gmx (DOT) net> wrote:
That depends upon the structure of T. If there are more columns in T
than you have indexed then, most likely, the queries will run in
approximately the same time. If, however, there are only the three
columns in T then the second query could run faster as it will scan
the index for the results and not touch the table at all.

You haven't provided enough information to answer your question.
Provide the DDL for table T, and some sample data, and you'll get a
better response, and possibly even an actual answer.


David Fitzjarrell

On Oct 6, 10:14*am, digory <dig... (AT) gmx (DOT) net> wrote:
That depends upon the structure of T. If there are more columns in T
than you have indexed then, most likely, the queries will run in
approximately the same time. If, however, there are only the three
columns in T then the second query could run faster as it will scan
the index for the results and not touch the table at all.

You haven't provided enough information to answer your question.
Provide the DDL for table T, and some sample data, and you'll get a
better response, and possibly even an actual answer.


David Fitzjarrell

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.

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.

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.

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.

On Oct 7, 2:14*am, digory <dig... (AT) gmx (DOT) net> wrote:
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

-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------

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