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...@gmx.net> wrote:
> 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

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.

On Oct 7, 2:14*am, digory <dig...@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

On Oct 7, 3:14*am, digory <dig...@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.

>> I can't give you the whole definition because of business reasons <<

Nonsense. You can easily construct a table to support your specific
question.

If the where clause of a query supplies columns that are indexed then
it is up to the CBO to determine if the index is to be used. You can
use the explain plan feature of Oracle to see the likely plans that
will be used by the optimizer.

Generally speaking if leading columns of an index are supplied by the
query where clause then the CBO can use the index to solve the query
but if the index is used or not depends on the decisions made by the
optimizer based on the object statistics.

Explain plan is described in the Performance and Tuning manual.

HTH -- Mark D Powell --

On Oct 6, 8:14*am, digory <dig...@gmx.net> wrote:
> 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

Think on this one: http://asktom.oracle.com/pls/asktom/...54694913325497

Are you thinking of dynamic SQL? Or trying to come up with simple
rules to code in "performance?"

jg
--
@home.com is bogus.
http://www.infoworld.com/article/08/...r-myths_1.html

> > 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.

That's not right. If t0 is older than any values of c, the condition c
> t0 holds for all rows in T, so the condition is actually redundant.

> Nonsense. *You can easily construct a table to support your specific
> question.

Yes, I could if I renamed all the columns. I can't give any data,
though. I don't think it would be of any help since my question was a
general one. The answer seems to be, it cannot be determined in
general which one is faster, one has to examine execution plans.

On Mon, 6 Oct 2008 08:14:30 -0700 (PDT), digory <digory@gmx.net>
wrote:

>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

Answers to a question like this one typically depend on posting a
database version.
You were too lazy to do so.
No answer is possible.

--
Sybrand Bakker
Senior Oracle DBA