Home » RDBMS Server » Performance Tuning » Which index should use ?? (Oracle 10g, windows 2003 Server)
Which index should use ?? [message #512967] Thu, 23 June 2011 02:18 Go to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi,

I have a partitioned table with attached structured. This table will be used for DSS( DML - twice/week , Select- 2-10 times/day)

So I want to know which type of index I should use for MSISDN_PREFIX Column...



Thanks & Regards

Pradeep
  • Attachment: out.txt
    (Size: 149.28KB, Downloaded 1487 times)
Re: Which index should use ?? [message #512979 is a reply to message #512967] Thu, 23 June 2011 03:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
There is no requirement for an index at all - unless it would help your queries. Which you didn't give.
About all one can say is that if you do want an index that includes MSISDN_PREFIX, it should probably be local prefixed.
Are you sure about your table partitioning strategy? Range partitioning on a varchar column is prone to error.
Re: Which index should use ?? [message #512984 is a reply to message #512979] Thu, 23 June 2011 03:13 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi John,

There are few reason for the paritition this column(MSISDN_PREFIX).

FYI, MSISDN_PREFIX is derived column from MSISDN. MSISDN Holds first four digit of MSISDN. and this tables having bulk insert.
5-6Cr entries in one stroke. and query which is being used on this table is
  select count(*) from TBL_NCHR1_NEW WHERE MSISDN_PREFIX = ':PREFIX' AND MSISDN=':MOB' AND OPSTYPE=':OPSTYPE'



So please tell me should I create any index on this column or not. If yes then which type of index.

Regards

Pradeep
Re: Which index should use ?? [message #512987 is a reply to message #512984] Thu, 23 June 2011 03:18 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi,

Current Execution plan is as follows
Execution Plan
----------------------------------------------------------
Plan hash value: 4183507859

---------------------------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |               |     1 |    18 |    69   (2)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE         |               |     1 |    18 |            |        |       |       |

|   2 |   PARTITION RANGE SINGLE|               |     1 |    18 |    69   (2)| 00:00:01 |  1325 |  1325 |

|*  3 |    TABLE ACCESS FULL    | TBL_NCHR1_NEW |     1 |    18 |    69   (2)| 00:00:01 |  1325 |  1325 |

---------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("MSISDN"='9317517818' AND "OPSTYPE"='A' AND "MSISDN_PREFIX"='9317')



Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        174  consistent gets
          0  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>


Reagrds

Pradeep
Re: Which index should use ?? [message #512988 is a reply to message #512984] Thu, 23 June 2011 03:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
5-6Cr

What?
Re: Which index should use ?? [message #512989 is a reply to message #512988] Thu, 23 June 2011 03:19 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi,

this is 5-6 Cr Records in this table...
Re: Which index should use ?? [message #512992 is a reply to message #512989] Thu, 23 June 2011 03:21 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Cr is not a universal notation for numbers. So you'll need to explain what it is.
Re: Which index should use ?? [message #512993 is a reply to message #512992] Thu, 23 June 2011 03:23 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi,

its is 50000000-60000000 Records in one stroke.


Re: Which index should use ?? [message #512996 is a reply to message #512993] Thu, 23 June 2011 03:35 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Also you should run explain plans against DB's with a representative amount of data.
There's no way there were 50-60 million rows in that table when you did that explain plan.
Re: Which index should use ?? [message #513008 is a reply to message #512996] Thu, 23 June 2011 04:10 Go to previous messageGo to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi

Currently there is 158415149 rows

and plan is as follow

SQL> select count(1) from tbl_nchr1_new;

  COUNT(1)
----------
 158415149

SQL> set autotrace traceonly
SQL>   select count(*) from TBL_NCHR1_NEW WHERE MSISDN_PREFIX = '9317' AND MSISDN='9317517818' AND OPSTYPE='A';


Execution Plan
----------------------------------------------------------
Plan hash value: 4183507859

---------------------------------------------------------------------------------------------------------

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

---------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |               |     1 |    18 |    69   (2)| 00:00:01 |       |       |

|   1 |  SORT AGGREGATE         |               |     1 |    18 |            |        |       |       |

|   2 |   PARTITION RANGE SINGLE|               |     1 |    18 |    69   (2)| 00:00:01 |  1325 |  1325 |

|*  3 |    TABLE ACCESS FULL    | TBL_NCHR1_NEW |     1 |    18 |    69   (2)| 00:00:01 |  1325 |  1325 |

---------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("MSISDN"='9317517818' AND "OPSTYPE"='A' AND "MSISDN_PREFIX"='9317'

)



Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        190  consistent gets
         15  physical reads
          0  redo size
        522  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed




Regards

Pradeep
Re: Which index should use ?? [message #513014 is a reply to message #513008] Thu, 23 June 2011 04:27 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Partitioning is a large and complex subject: get it right and there may be huge benefits, get it wrong and it may be disastrous. Are you sure you haven't made a few unwarranted assumptions already? For example, if you are doing intensive insert operations, perhaps your table should be hash partitioned, not range partitioned. Other things to think about: if your queries use an equality predicate, perhaps you should use a global hash partitioned index, not local range partitioned. But if you are going to do partition DDL (are you, for instance, using partition exchanger to bring the data in?) then global indexes may be awful. It isn't easy. You need to experiment with many partitioning strategies, and investigate the execution plans for each possibility.
Previous Topic: Big Table data Maintenance
Next Topic: Setting flash cache initialisation parameters
Goto Forum:
  


Current Time: Thu Mar 28 03:46:37 CDT 2024