• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 577
  • Last Modified:

SQL where and index

Hi All,
What is the best way to query with
       where mydate >= '20130813'
         and substr(mynumber,16,1) in ('0','2','4','6','8')

table has 30million records

what about indexes?
is there a way to index on substr(mynumber,16,1)

Thank you,
BILL Carlisle
BILL Carlisle
  • 3
  • 2
  • 2
  • +2
5 Solutions
slightwv (䄆 Netminder) Commented:
>>is there a way to index on substr(mynumber,16,1)

Yes.  It's called a Function-Based Index (FBI):

Is mydate a varchar2 or an actual date?
And is mynumber a number or a string?
Gerwin Jansen, EE MVETopic Advisor Commented:
I'm guessing mynumber is a 16 character string, otherwise mod(mynumber,2) would be easier to find the even numbers instead of using substr :)

@bcarlis - Can you post a few rows of the table you're referring to? That would make it easier for us to suggest a solution for you. Change data if you need to.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

>>I'm guessing mynumber is a 16 character string, otherwise mod(mynumber,2) would be easier<<
I guessed that's the case as well. However, if not, the 16th digit would have to be the last in order for modulus to work in all cases. My real question should have been, "Is the 16th character always numeric?" In which case, the function-based indexed might be created as a bit-mapped index since the values would be limited to 0-9 and might perform better than a b-tree index. I also agree that posting sample data would be a big help in offering a solution.
Mark GeerlingsDatabase AdministratorCommented:
Please tell us the datatypes for the two columns you need to query on.  Also, please tell us which columns of the table already have indexes on them.
BILL CarlisleAPEX DeveloperAuthor Commented:
Actually I found out that all dates are actually numbers and the variable mynumber is actually varchar2
slightwv (䄆 Netminder) Commented:
Try the function based index that I mentioned above.

As long as the cardinality on the substr is decent it should use the index.
Mark GeerlingsDatabase AdministratorCommented:
If your "date" columns are actually datatype: number, and they contain values in the format YYYYMMDD, and they are already indexed, you should refer to them like this:
where mydate >= 20130813

It is important that you *DO NOT* use single quotes around these literal values, since Oracle expects "number" values to be provided that way.  If you do use single quotes, Oracle will consider that to be a character value, so it will be forced to do an implicit datatype conversion on the number column values in the table, and this will prevent the use of existing indexes on your "date" columns (that are actually "number" datatype).

And yes, a function-based index using: "substr(mynumber,16,1)" should work well for that column.

Create that index something like this:

create index last_digit_ix on [table_name] (substr(mynumber,16,1))
tablespace [tablespace_name] parallel 4 nologging;

Note: you can use any name you like for that index, I used "last_digit_ix" as an example.  Provide the actual table_name in place of [table_name].  And, you don't need the last line, but you can optionally provide a tablespace_name and/or a degree of parallelism, and/or the "nologging" option (which applies to the initial index build only).
slightwv (䄆 Netminder) Commented:
>>and this will prevent the use of existing indexes on your "date" columns (that are actually "number" datatype)

I'm not sure this is true but I've been wrong before.  Maybe in certain situations in certain versions.

Given the over-simplified test case below, both queries use the index.

drop table tab1 purge;
create table tab1(col1 number, col2 varchar2(20));

	for i in 1..10000 loop
		insert into tab1 values(dbms_random.value,dbms_random.string('a',20));
	end loop;

create index tab1_idx on tab1(col1);

set autotrace traceonly
select col1 from tab1 where col1 >= '1';

select col1 from tab1 where col1 >= 1;
set autortace off

Open in new window

BILL CarlisleAPEX DeveloperAuthor Commented:
Thank you for your feedback.. got the indexes in place.. much better
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now