One of my Oracle tables contain more than 200 million records. Querying the table sometimes takes upto 30 minutes. I am trying to create a view on the table so as to limit the records for about 0.5 million. Will this decrease the querying time?
- Does the query have aggregate functions, joins etc?René Nyffenegger– René Nyffenegger2014-03-06 06:04:49 +00:00Commented Mar 6, 2014 at 6:04
- Dear Rene, the table does not have any functions or joins. It is the exact example as mentioned by Mark...SRCM– SRCM2014-03-06 06:08:20 +00:00Commented Mar 6, 2014 at 6:08
- So, I don't understand why you want to query only part of your table. What good is that data if you don't know how the rest looks like?René Nyffenegger– René Nyffenegger2014-03-06 06:42:01 +00:00Commented Mar 6, 2014 at 6:42
- Dear Rene, sorry for the delay response. The table contains complete histories of data. UI reports need to be generated for only recent data (i.e. last one week or one month). Therefore I need only the latest data...SRCM– SRCM2014-04-03 05:10:15 +00:00Commented Apr 3, 2014 at 5:10
Add a comment |
3 Answers
This is not the way to decrease the speed of querying, please follow the following:
- First of all make index on the fields which are normally searching by.
- Do not use where x in (select), but use inner join by PKs.
- Do not use functions to get value from another table, but use join, (this will open another sessions otherwise)
- Try if you can to normalize and separate the BLOB, CLOB fields away from the table to another table, and better if it is layed in another tablespace.
- Give more memory to the given schema.
- use also the following instractions:
Part 1 discusses the use of full-table scans part 2 discusses how and when to use indexes. Part 3 tells you how you can optimise joins. Part 4 discusses how to use views to get a high performance database. Part 5 tells you why your database should NOT be normalised. In Part 6 you can learn how to use stored procedures to sky-rocket Oracle performance. Finally, part 7 shows you how to use sub queries to boost Oracle performance
references to http://www.smart-soft.co.uk/Oracle/oracle-performance-tuning-part1.htm
2 Comments
haki
I agree with most of the things you said... so not to be petty but "Do not use where x in (select), but use inner join by PKs.". First, That may have been true in very old versions of oracle but now the optimizer rewrites
in when possible. Second, Who says that joining by index gives you the optimal execution plan ? an inner join between that result in most of the rows from the bigger table might benefit from FTS.
Adel
Thank you for you comment, What I said actully is not Join by index but (join by PKs) as you see up, the other point which is related to the old version, please send me that update to read since I have some queries tested myself comparing this on 11g and it is really big difference between join and sub quires till that version, anyway thanks again..
The view won't be slower (or faster) as the underllying query.
So, if your view is
create view v as select * from t where rownum < 500000; then
select * from v; takes +/- the same time as
select * from t where rownum < 500000; Maybe you want to take a look into materialized views. Depending on the query, they might significantelly speed up queries (at the cost of more disk usage).
Edit As per your comment (UI reports need to be generated for only recent data (i.e. last one week or one month). Therefore I need only the latest data...) I think want to partition your table. May I suggest this link for an introduction to partioned tables? Especially the part about partition pruning.
Comments
It depends. You need to understand your data, which subset of the data you're most interested in, and determine how to best implement the physical model. (heap table, IOT, bitmap index, B*Tree index, single table hash cluster, etc)
If you have a query, for example:
select * from some_big_table where col_a = 32457; and it returns, say, 500,000 rows out of 200,000,000, but it takes many minutes to run, because there's no index on col_a, and is forced to do a full table scan, then creating a view:
create view my_view as select * from some_big_table where col_a = 32457; and then select from the view, you're still going to have the same problem.
You need to look at the physical model, and what is the most efficient way to store and/or index the data, to optimize the access path(s) utilized to retrieve the data.
