Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...
Database Forum / Oracle / Oracle Server / October 2008

Tip: Looking for answers? Try searching our database.

Partial Index Usage Performance Question

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
digory - 06 Oct 2008 16:14 GMT
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
Reply to this Message
ddf - 06 Oct 2008 17:27 GMT
> Hi
>
[quoted text clipped - 16 lines]
> 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
Reply to this Message
digory - 07 Oct 2008 08:14 GMT
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.
Reply to this Message
ddf - 07 Oct 2008 14:15 GMT
> 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
[quoted text clipped - 4 lines]
> 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
Reply to this Message
digory - 14 Oct 2008 16:33 GMT
> > SELECT * FROM T WHERE a = ? AND b = ? AND c > t0
>
[quoted text clipped - 4 lines]
> 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.
Reply to this Message
Mark D Powell - 07 Oct 2008 14:16 GMT
> 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
[quoted text clipped - 4 lines]
> 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 --
Reply to this Message
digory - 14 Oct 2008 16:35 GMT
> 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.
Reply to this Message
joel garry - 07 Oct 2008 18:48 GMT
> Hi
>
[quoted text clipped - 16 lines]
> Thanks for a little insight,
> Peter

Think on this one:  http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID: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/10/06/40TC-power-myths_1.html
Reply to this Message
sybrandb@hccnet.nl - 14 Oct 2008 17:34 GMT
>Hi
>
[quoted text clipped - 16 lines]
>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.

Signature

Sybrand Bakker
Senior Oracle DBA

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.