Re: Composite Index Order VS Query Order ?? , Tuning Docs URL ?.

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 13 Jul 2004 17:12:23 +0100
Message-ID: <7765c897040713091231fe0fbe@mail.gmail.com>

Comments in-line
On Tue, 13 Jul 2004 20:23:33 +0530, Ranjeesh K R. <ranjeeshk_at_infics.com> wrote:
> Hi,
> I was working on optimising a piece of code which was taking 10hrs + to execute and all.So my queries are..
>
> #1) Saw a table where the "order of query" on the table and the "order of key"
> are different . For faster results shouldn't they be in the same order.
>
> for eg:
>
> Select ... where A.ID = ... and A.Name = ...
>
> The Key order on this table A NOW is Name & ID Order .
> Shouldn't they be in the order ID , Name ?..

shouldn't make any difference. What may make a difference is if some queries only use one of the columns (eg ID). If the *leading* column of the index is not used in the where clause then in many cases the index won't be used.[1].

for example consider the following test with a table with different data distributions (cos I adapted the test from one of Jonathans illustrating a different point)

NIALL 13-JUL-2004 16:50_at_nl1010>@ind_order_test
NIALL 13-JUL-2004 16:50_at_nl1010>set echo on
NIALL 13-JUL-2004 16:58_at_nl1010>
NIALL 13-JUL-2004 16:58_at_nl1010>execute dbms_random.seed(0);

PL/SQL procedure successfully completed.

NIALL 13-JUL-2004 16:58_at_nl1010>
NIALL 13-JUL-2004 16:58_at_nl1010>drop table t1 2 /

Table dropped.

NIALL 13-JUL-2004 16:58_at_nl1010>
NIALL 13-JUL-2004 16:58_at_nl1010>create table t1 as 2 select

  3          rownum as id,
  4          trunc(7100 * dbms_random.normal) as normal, -- normal
distribution of data
  5          trunc(dbms_random.value(0,50000)) as uniform -- uniform

distribution of data
  6 from
  7 all_objects a,all_objects b   8 where
  9 rownum <= 1000000
10 /

Table created.

NIALL 13-JUL-2004 16:59_at_nl1010>
NIALL 13-JUL-2004 16:59_at_nl1010>create index idx_1 on t1(normal,uniform) 2 /

Index created.

NIALL 13-JUL-2004 17:00_at_nl1010>
NIALL 13-JUL-2004 17:00_at_nl1010>execute dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

NIALL 13-JUL-2004 17:00_at_nl1010>
NIALL 13-JUL-2004 17:00_at_nl1010>explain plan   2 for
  3 select id
  4 from t1
  5 where normal = 42
  6 and uniform = 42
  7 /

Explained.

NIALL 13-JUL-2004 17:00_at_nl1010>
NIALL 13-JUL-2004 17:00_at_nl1010>@show_plan_9i
NIALL 13-JUL-2004 17:00_at_nl1010>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)

2 /

PLAN_TABLE_OUTPUT

Plan hash value: 2747017243

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00 :01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 14 | 4 (0)| 00:00 :01 |

|* 2 | INDEX RANGE SCAN | IDX_1 | 1 | | 3 (0)| 00:00 :01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

2 - access("NORMAL"=42 AND "UNIFORM"=42)

14 rows selected.

NIALL 13-JUL-2004 17:00_at_nl1010>
NIALL 13-JUL-2004 17:00_at_nl1010>drop index idx_1 2 /

Index dropped.

NIALL 13-JUL-2004 17:00_at_nl1010>
NIALL 13-JUL-2004 17:00_at_nl1010>create index idx_1 on t1(normal,uniform) 2 /

Index created.

NIALL 13-JUL-2004 17:01_at_nl1010>
NIALL 13-JUL-2004 17:01_at_nl1010>execute dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

NIALL 13-JUL-2004 17:01_at_nl1010>
NIALL 13-JUL-2004 17:01_at_nl1010>explain plan   2 for
  3 select id
  4 from t1
  5 where normal = 42
  6 and uniform = 42
  7 /

Explained.

NIALL 13-JUL-2004 17:01_at_nl1010>
NIALL 13-JUL-2004 17:01_at_nl1010>@show_plan_9i
NIALL 13-JUL-2004 17:01_at_nl1010>SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)

2 /

PLAN_TABLE_OUTPUT

Plan hash value: 2747017243

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time

PLAN_TABLE_OUTPUT

| 0 | SELECT STATEMENT | | 1 | 14 | 4 (0)| 00:00 :01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 14 | 4 (0)| 00:00 :01 |

|* 2 | INDEX RANGE SCAN | IDX_1 | 1 | | 3 (0)| 00:00 :01 |

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

2 - access("NORMAL"=42 AND "UNIFORM"=42)

14 rows selected.

NIALL 13-JUL-2004 17:01_at_nl1010>
NIALL 13-JUL-2004 17:01_at_nl1010>

The scripts are available on my site if you wish to play around with it.

> #2) Can anybody recommend me any site , where I will get reliable information on Tuning PL/SQL ?.

The Tuning Guide in the docs is actually pretty good these days. I'd start there.

-- 
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

[1] The advent in 9i of Skip Scans as an available access path changes
Oracle's behaviour in this regard - so if you are on 8i or lower and
you don't include the leading column of the index in your where clause
the index won't *ever* be used.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Tue Jul 13 2004 - 11:09:09 CDT