| Which index should use ?? [message #512967] |
Thu, 23 June 2011 02:18  |
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 1717 times)
|
|
|
|
| Re: Which index should use ?? [message #512979 is a reply to message #512967] |
Thu, 23 June 2011 03:02  |
John Watson
Messages: 8965
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 |
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 |
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 #512996 is a reply to message #512993] |
Thu, 23 June 2011 03:35 |
cookiemonster
Messages: 13965
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 |
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 |
John Watson
Messages: 8965
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
