Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Creating a multi column Index, What are the rule to define the position of a column in an index?
ich
post Jan 24 2012, 12:32 PM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 24-January 12
Member No.: 46,648



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

Go to the top of the page
 
+Quote Post
burleson
post Jan 24 2012, 03:16 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,430
Joined: 26-January 04
Member No.: 13



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


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
ich
post Jan 25 2012, 03:25 AM
Post #3


Newbie
*

Group: Members
Posts: 4
Joined: 24-January 12
Member No.: 46,648



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

Go to the top of the page
 
+Quote Post
burleson
post Jan 25 2012, 11:33 AM
Post #4


Advanced Member
***

Group: Members
Posts: 11,430
Joined: 26-January 04
Member No.: 13



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!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
ich
post Jan 30 2012, 03:33 AM
Post #5


Newbie
*

Group: Members
Posts: 4
Joined: 24-January 12
Member No.: 46,648



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


Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 31st August 2014 - 05:24 AM