Solved

SQL where and index

Posted on 2013-10-28
10
563 Views
Last Modified: 2013-11-02
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
0
Comment
Question by:bcarlis
  • 3
  • 2
  • 2
  • +2
10 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39606099
>>is there a way to index on substr(mynumber,16,1)

Yes.  It's called a Function-Based Index (FBI):
http://docs.oracle.com/cd/E11882_01/server.112/e41573/data_acc.htm#PFGRF94785

Is mydate a varchar2 or an actual date?
0
 
LVL 32

Expert Comment

by:awking00
ID: 39606237
And is mynumber a number or a string?
0
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 50 total points
ID: 39606501
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.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 50 total points
ID: 39606564
gerwinjansen,
>>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.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39608776
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 2

Author Comment

by:bcarlis
ID: 39615413
Actually I found out that all dates are actually numbers and the variable mynumber is actually varchar2
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39615420
Try the function based index that I mentioned above.

As long as the cardinality on the substr is decent it should use the index.
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 150 total points
ID: 39615751
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).
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39615779
>>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));

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

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

0
 
LVL 2

Author Closing Comment

by:bcarlis
ID: 39619399
Thank you for your feedback.. got the indexes in place.. much better
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now