Home » SQL & PL/SQL » SQL & PL/SQL » Index Check
Index Check [message #360816] Mon, 24 November 2008 01:32 Go to next message
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 #360832 is a reply to message #360816] Mon, 24 November 2008 01:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Note this is an Oracle forum.
Your question is irrelevant here.

Regards
Michel
Re: Index Check [message #360842 is a reply to message #360816] Mon, 24 November 2008 02:17 Go to previous messageGo to next message
namishtiwari
Messages: 2
Registered: November 2008
Location: India
Junior Member

please consider it as a generic sql example.
if you have any suggestions for tis question.

Thanks
NT
Re: Index Check [message #360846 is a reply to message #360832] Mon, 24 November 2008 02:25 Go to previous message
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.
Previous Topic: Sorting Query
Next Topic: View has erorrs
Goto Forum:
  


Current Time: Thu Nov 14 05:35:09 CST 2024