Creating a multi column Index, What are the rule to define the position of a column in an index? Options

[ich]

Hi

I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.

can some body give me some hints about the way to create multi_column indexes on the table?
What are the rule to define the best-position of a column in an index?
index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?


Kind Regrads
Ich

[burleson]

Hi Ich,

>> a table with about 6000000 record

Make sure to partition the table AND all indexes:

http://rampant-books.com/art_hernandez_par...g_in_oracle.htm

http://dba-oracle.com/googlesearchsite_pro...ealth_check.htm


******************************************
>> What are the rule to define the best-position of a column in an index?

It's driven by the SQL workload against the table . . .

Please read:

http://www.dba-oracle.com/t_composite_inde...mn_ordering.htm

[ich]

hi,


Thank you for the replay.

- 1- The table is in an OLTP-Database system and not in a datawarehouse.
- 2- The table is used in queries with aggregationen using Oracle Analytic Functions.

is it advisable to partition the table? ( I'm not such a great experience in DB-Tuning)
I cann only tune my SQL's and need to lern about Tuning the DB-Modell and choosing the best Values for DB-Params.


Kind Regards

Ich

[burleson]

Hi Ich,

>> The table is in an OLTP-Database system. The table is used in queries with aggregationen using Oracle Analytic Functions.

Whoa! This does not sound like OLTP to me! Aggregations and alalytics are full-scan data warehouse/DSS operations!

BEWARE! Oracle analytics are notoriously slow in performance, and replacing them with native SQL can be far faster!

Which means that the table will be accessed via full-table scans alot.

*******************************************
>> I can only tune my SQL's

No problemo . . . .

CODE

sql> show paramater cpu_count

Use a parallel hint, using cpu_cnt-1

CODE

select /*+ parallel 31 */ from mytab where . . .

Also, beware that parallel query is intelligent and it will only access the partitions that you need, improviong perfprmance

Parallel/parttiion-aware software breaks down the problem into smaller chunks, which are dispatched across a network of interconnected systems that concurrently process the chunks and then communicate with each other using message-passing libraries to coordinate and synchronize their results.

Please read. I wrote this just for you:

http://www.dba-oracle.com/t_parallel_partirion_aware_sql.htm

Good Luck!

[ich]

Hi,

Thank you for your posting.

The table is really used for a mix between OLTP and DSS.

CODE

create table t_x ( pk_col_1,pk_col_2, col_1 NUMBER(9) NOT NULL, col_2 VARCHAR2(20) NOT NULL, col_3 varchar2(20) NOT NULL, col_4 date, col_5 number,....) tablespace data_tbl;

no Triggers on the table, no promary key, an Unque index and 7 non unique Indexes

The most data about 65% of the data are with

CODE

col_1 IN (0,1,2)
AND col_2='L5'
AND col_3 in ('W1','W2','W3')
most of the queries a using col_1=n and col_2='x' and col_3='y'
or
col_1 in (0,1) and col_2='x' and col_3='y'

For an Insert or an Update in the tabe are selects used that takes about 5 Seconds per Records and this too slow.


I'm thinking about partitions as following:

CODE

case when col_1= 0 AND col_2='L5' and col_3='W1' then
    Partition_01
case when col_1= 0 AND col_2='L5' and col_3='W2' then
    Partition_02
case when col_1= 0 AND col_2='L5' and col_3='W3' then
    Partition_03
case when col_1= 0 AND col_2='L5' and col_3 IS NULL OR col_3 NOT IN ('W1','W2','W3') ) then
    Partition_04
case when col_1= 0 AND (col_2 IS NULL OR col_2 <> 'L5')  then
    Partition_05
--
case when col_1= 1 AND col_2='L5' and col_3='W1' then
    Partition_11
case when col_1= 1 AND col_2='L5' and col_3='W2' then
    Partition_12
case when col_1= 1 AND col_2='L5' and col_3='W3' then
    Partition_13
case when col_1= 1 AND col_2='L5' and (col_3 IS NULL OR col_3 NOT IN ('W1','W2','W3') ) then
    Partition_14
case when col_1= 1 AND (col_2 IS NULL OR col_2 <> 'L5')  then
    Partition_15
--
case when col_1= 2 AND col_2='L5' and col_3='W1' then
    Partition_21
case when col_1= 2 AND col_2='L5' and col_3='W2' then
    Partition_22
case when col_1= 2 AND col_2='L5' and col_3='W3' then
    Partition_23
case when col_1= 2 AND col_2='L5' and (col_3 IS NULL OR col_3 NOT IN ('W1','W2','W3') ) then
    Partition_24
case when col_1= 2 AND (col_2 IS NULL OR col_2 <> 'L5')  then
    Partition_25
--

Kind Regards
Ich