CODEWITHSUNDEEP
sqloracle-database
Prabhjot
Prabhjot

Reputation: 5014

Optimizing simple Select statement

I am having a simple query:

select * 
  from TABLE t 
 where t.something = 'code I am interested in';

The table has around 13 million records. And this simple query takes about 10 seconds. I then created an index on column t.something and the query takes 30 ms.

Unfortunately, I can't use the same approach on production server as I don't have write access to production database. Is there any other way apart from indexes to optimise this simple query?

EDIT - Unfortunately, I can't create new tables even though it won't affect existing tables. I had a chat with one of my colleague and he suggested to use date range so that I run the query multiple times but with different date range. Thanks for the comments guys.

Upvotes: 0

Views: 80

Answers (4)

Brian DeMilia
Brian DeMilia

Reputation: 13248

In many cases, although obviously I cannot comment on your particular company or agency, employees will at least be given access to create and maintain tables within their own schema, as it has no impact on other schemas that may be used by actual applications.

What you should check is whether or not have you have the "CREATE TABLE" privilege. If you do, you can create tables within your own schema. It is very different from the "CREATE ANY TABLE" privilege, which you are very unlikely to have, at least in production.

You can run the following to check if you have the CREATE TABLE privilege:

select * from user_sys_privs

If you do, then you can do the below:

create table table2
( col1      col1_definition,
  col2      col2_definition,
  something something_def    );

insert into table2
select * from table;

create index tab2_idx on table2 (something);

select * from table2 where something = 'code I am interested in';

The INSERT will take awhile to run, maybe do it overnight. However once it's done, and you've created the index on the 'something' field -- as shown -- your query will run very efficiently against it.

If the data in that table changes often, you may want to create a materialized view (which you can also index) instead.

Upvotes: 0

Aditya Yogi
Aditya Yogi

Reputation: 84

Assuming you have a Primary Key in Table you can use that column instead of using * in Select query.This will reduce considerable time.

Upvotes: 0

ErstwhileIII
ErstwhileIII

Reputation: 4843

You will not be able to improve the thirteen million row table scan to an index lookup on the production database server without adding an index.

You may want to take your information and performance analysis to the folks who maintain the production database and request that an index be added.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

Realistically, no.

If the problem is that you have a query against an un-indexed value (which is what it sounds like), the only way to solve that performance issue is to create an index (or some other structure) in the database that your query can use. Obviously, you personally don't need to be the one that does that. But you would need to work with the DBA or the developers or whoever decides what indexes to create in order to create an appropriate index for your query.

Potentially, you may be able to modify your query so that you get the same information but in a way that allows you to utilize existing indexes on the table. If there are other attributes that are indexed which are correlated with the particular code you're looking for, adding predicates on those values may improve performance. For example, if you know that t.some_indexed_column will always be 42 for the rows you're interested in, adding and t.some_indexed_column = 42 to your query may allow Oracle to use the existing index on some_indexed_column to get your results more efficiently.

Upvotes: 1

Related Questions