Home » RDBMS Server » Performance Tuning » Advice on Index Creation (Windows 2000, Oracle 10g)
Advice on Index Creation [message #509806] Wed, 01 June 2011 03:17 Go to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
Hi

I have a table of 3 million records, I got a requirement of creating Index for a column. Column data details are as below.
Please advice, which Index type I should go for.
 Column_Name      Data_Count
   A              4000  i.e., distinct rows
   A              4500  i.e., actual rows
   A              2995500 i.e., NULL rows

Thanks in Advance,

Re: Advice on Index Creation [message #509807 is a reply to message #509806] Wed, 01 June 2011 03:19 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So to be clear - There are 3 million rows, of which 4500 have a non-null value, and there are 4000 distinct values in that 4500.
Re: Advice on Index Creation [message #509808 is a reply to message #509806] Wed, 01 June 2011 03:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The main point to create an index is to know what are the queries on the table.
So give some queries used to query the table.

Regards
Michel
Re: Advice on Index Creation [message #509809 is a reply to message #509807] Wed, 01 June 2011 03:22 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
your understanding is correct.
Re: Advice on Index Creation [message #509810 is a reply to message #509809] Wed, 01 June 2011 03:23 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Good, my next question would have been what Michel haas just asked.
Re: Advice on Index Creation [message #509811 is a reply to message #509810] Wed, 01 June 2011 03:27 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
And the environment, data warehouse/OLTP.
Re: Advice on Index Creation [message #509812 is a reply to message #509808] Wed, 01 June 2011 03:30 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
There are three scenarios and they are direct id search on single table.
1) SELECT <column_names>, ....
   FROM <table_name>                 -- single table
   WHERE <column_name> = <condition> -- indexed column
          OR
        A = <condition>             -- Index to be decided

2) SELECT <column_names>, ....
   FROM <table_name>                 -- single table
   WHERE A = <condition>             -- to be decided

3) SELECT <column_names>, ....
   FROM <table_name>                 -- single table
   WHERE <column_name> = <condition> -- indexed column
          AND
         A = <condition>             -- index to be decided  


--
Re: Advice on Index Creation [message #509813 is a reply to message #509811] Wed, 01 June 2011 03:32 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
It's an report database, there won't be any DML operations.
Table will be loaded once per day.
Re: Advice on Index Creation [message #509814 is a reply to message #509813] Wed, 01 June 2011 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Change the existing index on <column_name> to also include A.
Re: Advice on Index Creation [message #509815 is a reply to message #509814] Wed, 01 June 2011 03:37 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Though really you'd be better off just trying different index combos on your test DB and seeing what works best, rather than relying on us guessing based on limited information.
Re: Advice on Index Creation [message #510085 is a reply to message #509815] Thu, 02 June 2011 08:53 Go to previous messageGo to next message
oraranjangmailcom
Messages: 67
Registered: April 2010
Member
Thanks ...
Re: Advice on Index Creation [message #510086 is a reply to message #510085] Thu, 02 June 2011 08:56 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Table will be loaded once per day.
consider manually collecting statistics after the load completes
Previous Topic: Slow query with distinct
Next Topic: Tuning Merge Statetment
Goto Forum:
  


Current Time: Thu Mar 28 13:40:35 CDT 2024