OraFAQ Forum: SQL & PL/SQL » Indexing advice

Home » SQL & PL/SQL » SQL & PL/SQL » Indexing advice (11g R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Indexing advice [message #648274]

Fri, 19 February 2016 10:09

geordibbk
Messages: 11
Registered: August 2015
Location: London

Junior Member

I was wondering what is the recommended advice for indexing on tables.

For example Table1 has 3 columns
1) Age integer
2) Name varchar2
3) DOB date

If I add a unique index for the 3 columns:
Unique Index: Age, Name, DOB

If searching by name is often done, is it recommended to an index to Name column also?
Index: Name

Or does the unique index already optimise this?

Report message to a moderator

Re: Indexing advice [message #648276 is a reply to message #648274]

Fri, 19 February 2016 10:34

Flyby
Messages: 188
Registered: March 2011
Location: Belgium

Senior Member

Depends on your query /workload.

Report message to a moderator

Re: Indexing advice [message #648280 is a reply to message #648274]

Fri, 19 February 2016 11:07

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

geordibbk wrote on Fri, 19 February 2016 08:09

I was wondering what is the recommended advice for indexing on tables.

For example Table1 has 3 columns
1) Age integer
2) Name varchar2
3) DOB date

If I add a unique index for the 3 columns:
Unique Index: Age, Name, DOB

If searching by name is often done, is it recommended to an index to Name column also?
Index: Name

Or does the unique index already optimise this?

It is BAD design to store any computed value, like AGE, in table column.
AGE can be computed at SELECT time to ensure it is always correct.

UNIQUE INDEX should be on NAME & DOB

Report message to a moderator

Re: Indexing advice [message #648281 is a reply to message #648274]

Fri, 19 February 2016 11:07

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

In this case, I would create the table as an IOT. To determine the optimal column ordering, you need to think about your predicates, and whether you want to make use of skip scan access.

Report message to a moderator

Re: Indexing advice [message #648355 is a reply to message #648280]

Mon, 22 February 2016 02:59

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

BlackSwan wrote on Fri, 19 February 2016 17:07

UNIQUE INDEX should be on NAME & DOB

While it's highly unlikely to get two people with the same name and DOB it's certainly possible. The table as it stands doesn't have anything you can apply a unique index to.

Report message to a moderator

Re: Indexing advice [message #648367 is a reply to message #648274]

Mon, 22 February 2016 05:56

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

To emphasize BlackSwan's comment ... why are you keeping AGE? It will be wrong the day after you enter it.

Report message to a moderator

Re: Indexing advice [message #648384 is a reply to message #648367]

Mon, 22 February 2016 13:10

Bill B
Messages: 1971
Registered: December 2004

Senior Member

You age can be calculated using the following code

floor(months_between(trunc(sysdate),date_of_birth)/12)

You can even make it a virtual column in Oracle 12.

[Updated on: Mon, 22 February 2016 13:10]

Report message to a moderator

Previous Topic:	How to use LAG function in oracle pl sql until non zero value is reached?
Next Topic:	Join 2 select statements

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 22:31:50 CDT 2024