Index Check [message #360816] |
Mon, 24 November 2008 01:32 |
namishtiwari
Messages: 2 Registered: November 2008 Location: India
|
Junior Member |
|
|
Hi,
We have a need to generate a report using data in the ARRFSYSAUDITLOG table. The querys’ perfomance is drmatically increased when I add an index (using a test database). I am wondering if adding this index to the production server will have any adverse affects on the appliaction. Can you review the index creation statement below and let me know what you think?
CREATE NONCLUSTERED INDEX
[_dta_index_ARRFSYSAUDITLOG_10_1358627883__K13_K4_12] ON [ArcotAdmin].[ARRFSYSAUDITLOG]
(
[HTTPDEVICEID] ASC,
[DATELOGGED] ASC
)
INCLUDE ( [USERNAME])
WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [PRIMARY]
go
The columns inside the table are--
INSTANCEID NUMBER No 1
TXID NUMBER(18,0) No 2
SESSIONID VARCHAR2(32 Bytes) Yes 3
DATELOGGED DATE No SYSDATE 4
CALLERID VARCHAR2(256 Bytes) Yes 5
TXNTYPE NUMBER No 6
STATUS NUMBER No 7
GROUPID NUMBER No 8
SUBGROUPID NUMBER No 9
ACTIONID NUMBER Yes 10
CREDID NUMBER Yes 11
USERNAME VARCHAR2(256 Bytes) No 12
HTTPDEVICEID VARCHAR2(128 Bytes) Yes 13
FLASHDEVICEID VARCHAR2(128 Bytes) Yes 14
JARDEVICEID VARCHAR2(128 Bytes) Yes 15
DEVICESIGNATURE VARCHAR2(2048 Bytes) Yes 16
DEVICEIDOUT VARCHAR2(128 Bytes) Yes 17
CLIENTIPADDRESS VARCHAR2(32 Bytes) Yes 18
SDKIPADDRESS VARCHAR2(32 Bytes) Yes 19
RULESETSEQID NUMBER Yes 20
SCORE NUMBER Yes 21
ADVICEID NUMBER Yes 22
RESULT_USERKNOWN VARCHAR2(1 Bytes) Yes 23
RESULT_TRUSTEDIP VARCHAR2(1 Bytes) Yes 24
RESULT_UNTRUSTEDIP VARCHAR2(1 Bytes) Yes 25
RESULT_DEVICEIDCHECK VARCHAR2(2 Bytes) Yes 26
RESULT_SHAREDMACHINE VARCHAR2(1 Bytes) Yes 27
RESULT_TRUSTEDCOUNTRY VARCHAR2(1 Bytes) Yes 28
RESULT_UNTRUSTEDCOUNTRY VARCHAR2(1 Bytes) Yes 29
RESULT_SIGCHECK VARCHAR2(1 Bytes) Yes 30
RESULT_SIGCHECK_NUMINPUT NUMBER Yes 31
RESULT_SIGCHECK_NUMMATCH NUMBER Yes 32
RESULT_SIGCHECK_NUMTOTAL NUMBER Yes 33
RESULT_LOGICALUPGRADE NUMBER Yes 34
RESULT_EXCEPTION VARCHAR2(3 Bytes) Yes 35
RESULT_VELOCITY VARCHAR2(10 Bytes) Yes 36
RESULT_DETAIL VARCHAR2(512 Bytes) Yes 37
ASSOCIATIONNAME VARCHAR2(32 Bytes) Yes 38
SECAUTHRESULT NUMBER No 0 39
RELATEDINSTANCEID NUMBER Yes 40
RELATEDTXID NUMBER(18,0) Yes 41
|
|
|
|
|
Re: Index Check [message #360846 is a reply to message #360832] |
Mon, 24 November 2008 02:25 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
The general principles still applies though.
The index will speed up queries that use it,but will slightly slow down inserts, updates and deletes to the table.
|
|
|