OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » create index (10.2.0.4)

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

create index [message #534479]

Tue, 06 December 2011 09:21

varunvir
Messages: 389
Registered: November 2007

Senior Member

Hi Experts,

I have column containing three values:-N,E,Y.
I want to get results with only E and Y values.
Is it it possible to create index which would not
look for N values.

Thanks,
Varun

Report message to a moderator

Re: create index [message #534480 is a reply to message #534479]

Tue, 06 December 2011 09:24

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

how many rows contain "N"?
how many rows contain "E"?
how many rows contain "Y"?

Report message to a moderator

Re: create index [message #534483 is a reply to message #534480]

Tue, 06 December 2011 09:37

varunvir
Messages: 389
Registered: November 2007

Senior Member

Number of rows contain "N"=3
Number ofrows contain "E"=30
Number of rows contain "Y"=474470

Thanks,
Varun

Report message to a moderator

Re: create index [message #534484 is a reply to message #534483]

Tue, 06 December 2011 09:41

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

Senior Member

In that case an index is probably a waste of time. You need to retrieve 99.9999% of rows. A full table scan is the best way.

Report message to a moderator

Re: create index [message #534485 is a reply to message #534483]

Tue, 06 December 2011 09:43

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I want to get results with only E and Y values.
>Is it it possible to create index which would not look for N values.

Yes, it is possible to CREATE INDEX, but it would be a waste of CPU cycles & disk space.
To obtain rows "with only E and Y values" a Full Table Scan is best choice for posted data distribution.

Report message to a moderator

Re: create index [message #534488 is a reply to message #534485]

Tue, 06 December 2011 10:09

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks a lot Blackswan.Full table scan is definitely the
appropriate way for oracle in this case.
I am just curious though how would i create index which would not look for N values.

Thanks,
Varun

[Updated on: Tue, 06 December 2011 10:09]

Report message to a moderator

Re: create index [message #534489 is a reply to message #534488]

Tue, 06 December 2011 10:15

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I am just curious though how would i create index which would not look for N values.

I am curious as to how I can write a sentence that contains no letters.

Report message to a moderator

Re: create index [message #534491 is a reply to message #534489]

Tue, 06 December 2011 10:26

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

Senior Member

create index <index_name> on <table> (decode(<column>, 'N', null, <column>));

You would then have to use that decode statement in the where clause of your queries.

Report message to a moderator

Re: create index [message #534493 is a reply to message #534491]

Tue, 06 December 2011 10:31

varunvir
Messages: 389
Registered: November 2007

Senior Member

Thanks

Report message to a moderator

Re: create index [message #534495 is a reply to message #534493]

Tue, 06 December 2011 11:23

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>create index <index_name> on <table> (decode(<column>, 'N', null, <column>));
what results in future should INSERT new row and column contains "N"?

The whole premise to exclude value from index is absurd.

Report message to a moderator

Re: create index [message #534505 is a reply to message #534495]

Tue, 06 December 2011 16:51

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

It's very useful in the right circumstance imo, as it can make teeny indexes on large tables. I used it recently and had a 2mb index over a 100gb table.

So far, I'd say the uses of it have been 'last resorts' due to concrete artificial/business limitations. That said, I'd rather have the facility and not need it, than need it and not have it.

I think the real question is, is the solution appropriate?

Smile

Report message to a moderator

Re: create index [message #534575 is a reply to message #534479]

Wed, 07 December 2011 07:55

ma_appsdba
Messages: 4
Registered: March 2011

Junior Member

Try creating Bitmap index. That might help you to achieve what you are looking for.

Report message to a moderator

Re: create index [message #534576 is a reply to message #534575]

Wed, 07 December 2011 07:56

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

Senior Member

Doubtful in this case - full table scan is the way to go here.

Report message to a moderator

Re: create index [message #534578 is a reply to message #534575]

Wed, 07 December 2011 07:57

Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Unless this is an OLTP table, in this case this might help to kill your performances.

Regards
Michel

Report message to a moderator

Previous Topic:	performance problem
Next Topic:	Performance Across DB Links

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Apr 24 02:28:32 CDT 2024